Reports of all stripes with conditional formatting

Hi all,

Imagine you want alternating rows (or columns) in a report to have alternating colors. I don’t know, maybe you’re missing the days of tractor-feed paper.

One option is to convert your data in a table (Ctrl+T, or Insert \ Table). Other advantages come with the table like the ability to reference ranges in formulas with names instead of addresses, but the table doesn’t work in all cases (but I definitely should write something about tables soon).

An easy option, if you can’t use tables is to use conditional formatting.:

  • Select your data
  • Home \ Conditional Formatting \ New Rule
  • Use a formula to determine which cells to format
  • Use the following formula =ISODD(ROW())
  • Set your format, validate, you’re all set (you can also use ISEVEN, and swap COLUMN() for ROW() if you want vertical rather than horizontal stripes).


Try it, you might like it.


Right-click in drag

You obviously know that you copy / paste a cell by clicking on its lower right corner, and dragging it down or across while holding the left mouse button down.
For some cell values, e.g. dates or numbers, you can even switch between an incremented copy and a straight copy by holding or not the Ctrl key. By default, dates will increment and numbers won’t, and holding Ctrl will switch the behavior.
NOW, if you perform this dragging with the right-click instead of the left-click, you’re getting a lot more options (see screenshot below), like filling in weekdays, or months. This is quite the time saver.

Options stemming from a right-click drag

Try it, you might like it.


PS: Try selecting multiple cells before dragging and see how that affects the results you’re getting.