Happy New Year, readers! Today’s tip is an old favorite, and one that can save you a lot of tedious work. It exploits Excel’s conditional formatting feature to quickly highlight every other row of your privilege log, or any other spreadsheet. Feel free to download my dummy privilege log if you would like to follow along.
Here is the original, unadorned privilege log:
In order to highlight alternating rows, take the following steps:
First, select all the cells containing the privilege log entries, but not the Header Row. Also, don’t select the entire rows–just the cells to be highlighted. You can do this by clicking on cell A2 and dragging the cursor to the last cell on the bottom right of the log. (Pro tip: with cell A2 selected, hit Ctrl+Shift+Down Arrow, then Ctrl+Shift+Right Arrow (perhaps a couple times) to highlight all the cells.)
Next, from the Home ribbon, click the drop-down menu under Conditional Formatting and select New Rule:
In the dialog box, choose the option “Use a formula to determine which cells to format”; then, in the formula bar, type the following (don’t forget the “=”):
. . . then hit the Format… button to set up the highlighting.
Choose Fill, pick a nice color, and hit OK:
Now you can hit OK on the Conditional Formatting dialog box:
Voilà! Alternating rows of the privilege log will be highlighted in the color you chose:
The formula analyzes every cell in the selection and applies the formatting to every cell in an odd-numbered row. That’s why the entries in rows 3, 5, 7, etc. are highlighted. You can delete this highlighting, if necessary, by selecting all the cells and choosing Clear Rules from the conditional formatting drop-down menu.
Please share, forward, or like this post, and subscribe to the Excel Esquire blog!