Highlight every Nth row in Excel Tables – How To

4
4163

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:

highlight nth row excel table 1

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:

highlight n rows excel table 2

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

highlight nth row excel table 3

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

highlight nth row excel table 4

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.

highlight nth row excel table 5

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.

highlight nth row excel table 6

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.

highlight nth row excel table 7

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

highlight nth row excel table 8

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.

highlight nth row excel table 9

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.

highlight nth row excel table 10

Download Fully Worked File
You can download the fully worked file free of cost but if you like the effort and gained something valuable then you can set the price whatever you deem fit and pay. If you want to download it for free then simply set the price to “0”. Enjoy!
Download Now

Check out following tutorials as well for more Excel learning:

  1. 10 ways to present variance analysis reports in Excel
  2. How to LOOKUP using ONLY Conditional Formatting in Excel
  3. Comparing two columns to highlight Duplicates in the same row – Excel Conditional Formatting
  4. Create Edgy To-Do or Check Lists with Check boxes in Excel + Shortfall calculator

4 COMMENTS

  1. If you omit the requirement to change the banding with a spinner, you can create a custom table style where you set the banding to the number of rows you need. No need for calculation intensive conditional formatting.

    • Hey JKP,
      Thanks for commenting and bringing neat feature to highlight. But that functionality is limited and static unlike this dynamic approach as you can change it with spin button if you need it.
      – Hasaan

LEAVE A REPLY