Latest posts by techwriter (see all)
- How to Count the Number of Days with an Incident and Chart with Running Averages in MS Excel - October 19, 2016
- FREE Online Video Course – MS Word Power Shortcuts - October 14, 2016
- INFOGRAPHICS – Which Business Entity is Right for You? - September 28, 2016
By Yoav Ezer
Special to TCC
An especially nice way to add a splash of color to your Microsoft Excel spreadsheets, and make the data much more readable, is to add an alternating background color to rows.
Of course you can do this manually, but if you have lots of rows, who would want to spend lots of (tedious), time to do that?
There are various automated options, but they don’t intelligently color only the rows that actually contain data. Is it possible to have an automated option with that extra dash of intelligence?
As you can see in the screen grab above, yes we can! If you would like this effect in your spreadsheets, just read on …
Intelligent Conditional Formatting
Our approach is to use conditional formatting and to check the content of cells before formatting them!
You need to create a Conditional Formatting rule on the cells you wish to format. The trick here is rather than create your formatting rule the way you might ordinarily, use a formula to determine which cells to format.
First we need to include a built in check for if it is an odd or even row.
That challenge is solved by this formula:
Which returns true if the row is odd.
We can check the cell is empty using <>””, therefore our formula needs to be …
Which will take into account if the first cell contains data, so we get true only if it is an odd row AND contains data.
The conditional formatting feature of Excel is extremely flexible when you combine it with the ability to use formula logic. Using it for alternating row highlighting is only the start, what might you use it for?
About the author
Yoav Ezer, co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to Excel conversion software.