Conditional Formatting for Maintenance Free Formatting & Presentation
Shading alternate rows is a very common and easy way to make a table with multiple columns more readable. This is usually achieved in Excel by applying Format, Cells, Patterns, Color on alternate rows. So what's the problem? Well, if the table is then sorted, or rows are deleted or added the shading will be scrambled in the process.
Conditional formatting is great way to keep the desired shading without any effort. This is achieved by using a =ROW()=EVEN(ROW()) formula as a conditional format across the entire table as follows.
How does this work?
- The formula =EVEN() rounds any number to the nearest even integer. For example =EVEN(9) rounds to an even 10.
- =ROW() returns the row number of the current cell.
Therefore when these two formulae are combined in a “Formula Is” condition such as "=ROW()=EVEN(ROW())", if =ROW() is an even number, the formula returns True and applies the conditional format. if =ROW() is an odd number the formula returns False and the conditional format is not applied.
We hope this tip makes things easier for you.