Tech Tip: Excel Conditional Formatting – Highlighting or Underlining Every Nth Row

If you’re a Microsoft Excel user, you might be familiar with Conditional Formatting where you can set certain rules to cells based on values and information in them. For the past few years, I’ve used a spreadsheet that I had to enter data off of and every 12th row I had to stop and page down in the program I was using. I thought to myself, it would be nice if I could have Excel underline every Nth row to prompt me when to page down, instead of visually doing going back and forth to catch it.

Here’s how to do it in Microsoft Excel 2010 and newer:
1) Highlight the rows you want to apply the formatting to
2) In the Toolbar, select Conditional Formatting > Manage Rules
3) Select New Rule
4) Select “Use a Formula to determine which cells to format”
5) In the “Format values where this formula is true” box, type =MOD(ROW($A1),12)=0
– You can replace 12 with whatever number you want to apply your formatting to, in my instance, this will format every 12th row
6) Click the Format box and set your rules (highlight, underline, etc)