Highlight every Nth row in Excel Tables – How To

By default, every second row in Excel tables is highlighted and the other option is to turn highlighting off completely. Though it does help most often but often times we may find a need to highlight every third, fourth, fifth or any other row.

In today’s tutorial we will learn how to make the process of highlighting completely dynamic and you can highlight rows at the push of a button giving you the option to increase or decrease the number of rows. Have a look:

Main challenges to implement this idea were:

1. Make Excel table’s structured reference system to work within conditional formatting. I don’t know why they don’t go together.
2. Make it fail safe so that even if table is moved from one place to the other, it doesn’t break
3. Make the highlighting option dynamic i.e. giving you the option to change the row number. This was completely optional and not necessary but to show that it is possible and to learn something extra involving form controls, I felt I should add this bit to tutorial as well.

Highlighting every N’th row of Excel table – Step by step

Step 1: Have the arranged and convert it into Excel table by selecting it and hitting CTRL+T shortcut. By default, the headers will be given a distinct color and every second row will be banded. Just like as following:

Step 2: Give table your desired formatting and turn off the banded option so that it doesn’t interfere with our custom banding solution.

Step 3: Give table some name. Just any so that it is easy for you to remember while writing formulas. I gave it sales.

Step 4: Now we need to implement a spin button so that it can increase or decrease the numbers in a particular cell. To do this we need developer’s tab to access form controls.

Once enabled, go to developer tab > controls group > click insert button > select spin button under form controls category. Click anywhere you want to place it and adjust the width and height as you wish.

Step 5: Once we have the spin button available now we need to connect it to particular cell so that value changes as we press spin buttons. To do this right click on spin buttons > click form controls > have a cursor within range filed and click the desired cell where you want values to change > click OK.

Step 6: I did little formatting so that spin buttons and value field looks a little better. Its a simple merge and border.

Once done select the cell with value and name it anything. I gave it “rval” using name box.

Step 7: To make structured reference work within conditional formatting, we will wrap structured reference within named range. And later using that name we can get conditional formatting to work.

What we need is to identify the row number of table header. From there we can do the math which and how many rows to highlight based on the value given by spin buttons.

Go to Formula tab > click defined names button > click names manager > from the dialogue box click New button. A new pop up will open. Delete anything that is mentioned in “refer to” field and while having the cursor within range field, click any header of the table. And later give any name to it. I gave “range”. Click OK

Step 8: Now comes the finishing touch where all the effort will come to fruition. Select the table (excluding headers) and go to Home tab > styles group > click conditional formatting button > click new rule > from the dialogue box select “use a formula to determine which cells to format” and put this formula:

=INT((ROW()-ROW(range))/rval)=(ROW()-ROW(range))/rval

Make the format changes that you wish. I simply selected the gray color. Press OK.

Time to see it in action! Just click the up and down arrows to increase/decrease the value and see the rows being highlighted accordingly.