How to highlight every five rows in Excel

To improve the readability of the excel workbook, one can highlight the whole worksheet or Nth row. Highlighting the excel row means filling it with another unique color compared to other rows' colors. One can decide to highlight every row that contains a certain index. For example, in this article, we shall discuss how to highlight every five rows in your sheet. To do this, you can use these methods:

  • Using Conditional formatting feature.
  • Using Kutools for excel

Using Conditional formatting features

Conditional formatting is a feature in excel that can alter rows and columns of a given sheet by applying some rules. To highlight every five rows in excels using conditional formatting, these steps are involved:

1. Enter the data that you need to highlight every 5 rows.

2. Then, select the data range you want to format. That is, highlight all your data.

3. On your header tabs, click on the Home button. From the Home tab menu, select the conditional formatting, and click on its drop-down button.

4. On the drop-down button, select the New rules button. A dialogue box pops up.

5. On the dialogue box, choose the Use a formula to determine which cells to format option. Then enter the rule to format cells on the Format values where this formula is true. In our case, we will use this formula to highlight every five rows.

=MOD(ROW(),5)=0


6. Then, click on the Format button to set the fill/ highlight properties.

7. On the Format cells dialogue box, click on the fill tab. From the Background color section, select the color of your choice. Click Ok to save changes.

8. Finally, click the Ok on the New formatting rule dialogue box to close it.


Using the kutools feature

Kutools is an add-in feature in excel. Highlighting the nth row in excel is one of its many functions in Excel. However, this tool is not readily available in excel. You have to install it. Here are the steps to use kutools to format the cells of a worksheet.

1. After downloading and installing the kutools feature, enter your data on the worksheet.

2. Secondly, select the data by highlighting it. You'll do this to the range of data you want to highlight in every row.

3. Then, click the kutools tab on the header tabs and click the Format button.


4. On the Format drop-down menu, select the Alternate Row/ Column shading button. A dialogue box opens.

5. The range of your highlighted data will appear on the Range box. If some data are omitted, you can re-enter the range by clicking on the square box found next to the Range section.

6. On Apply shading to section, toggle on the row button because we are highlighting the rows only.

7. On the Shading method section, choose the conditional formatting button.

8. Select the color you like to appear on every 5th row from the shade color section.

9. Then, specify the interval of shading. In our case, let us use 5.

10. Finally, click on the Ok button to close the dialogue box.