How to Highlight Every 5 Rows in Excel (4 Methods)

Get FREE Advanced Excel Exercises with Solutions!

Highlighting rows in Excel datasheets is a common job in our day-to-day life. Because of better readability and visualization of a large data list, we often use it. In this article, we will show you how to highlight every 5 rows in the Excel workbook. If you are also interested in applying this feature to your datasheet follow us through this article.


How to Highlight Every 5 Rows in Excel: 4 Easy Ways

For highlighting every 5 rows, we are using a dataset of 10 employees of a company. In this dataset, the names of those employees are in the range of cells B5:B14 and their salary is in the range of cells C5:C14. We will show you how to highlight every 5 rows in 4 different methods.


1. ISEVEN, CEILING, and ROW Functions to Highlight Every 5 Rows

In this method, we are going to use the ISEVEN, CEILING, and ROW functions to highlight every 5 rows. The dataset is in the range of cells B4:C14. After completing this process, you will see that the last 5 rows are showing in different colors. The steps are given below:

📌 Steps:

  • First of all, select the entire range of cells B4:C14.

ISEVEN, CEILING, and ROW Functions to Highlight Every 5 Rows

  • Then, in the Home tab, go to the Conditional Formatting > New Rules.

ISEVEN, CEILING, and ROW Functions to Highlight Every 5 Rows

  • A new dialog box, called New Formatting Rule will appear. Now, select Use a formula to determine which cells to format.

  • Write down the following formula in the empty box below Format values where this formula is true.
=ISEVEN(CEILING(ROW()-4,5)/5)

ISEVEN, CEILING, and ROW Functions to Highlight Every 5 Rows

  • Now, click on the Format option.
  • Another dialog box, entitled Format Cells, will appear. Go to the Fill option.
  • Default the color of the cell will be white. Change the cell color. Here, we choose a light green color to identify the highlight cells. You will also see the color in the Sample section.
  • At last, click OK to close the Format Cells dialog box.

  • Again, click OK to close the New Formatting Rule box.

ISEVEN, CEILING, and ROW Functions to Highlight Every 5 Rows

  • Finally, you will see the last 5 rows of the list get our chosen highlight color.

ISEVEN, CEILING, and ROW Functions to Highlight Every 5 Rows

Thus, we can say that in the conditional formatting tab, our formula has worked perfectly.

🔍 Breakdown of Our Formula

We are showing how our formula worked inside the conditional formatting tab for Row 10.

👉 ROW(): This function returns 10.

👉 CEILING(ROW()-4,5)/5): This function returns 2.

👉 ISEVEN(CEILING(ROW()-4,5)/5): This function returns True and True means the value of the CEILING function is even and it will highlight the row.

Read More: How to Highlight Active Row in Excel


2. Using ISODD, CEILING, and ROW Functions

In this procedure, we have to use the ISODD, CEILING, and ROW functions to highlight every 5 rows. If you have already studied the breakdown of the previous method, you have found the technique of the ISODD function. Before, the ISEVEN function highlighted the text when the value of the CEILING function was even. Now, the ISODD function highlights the text if the value of the CEILING function is odd. Though the steps are almost similar, it is  described as follows:

📌 Steps:

  • First, select the entire range of cells B4:C14.

  • Now, in the Home tab, go to the Conditional Formatting > New Rules.

Using ISODD, CEILING, and ROW Functions

  • A new dialog box, titled New Formatting Rule will appear. Then, select Use a formula to determine which cells to format.

  • Write down the following formula in the empty box below Format values where this formula is true.
=ISODD(CEILING(ROW()-4,5)/5)
  • After that, click on the Format option.

Using ISODD, CEILING, and ROW Functions

  • Another dialog box, called Format Cells, will appear.
  • Now, go to the Fill option.
  • The color of the cell remains white by default. Change it. Here, we choose a light green color to identify the highlight cells. You will also see the color in the Sample section.
  • Click OK to close the Format Cells dialog box.

Using ISODD, CEILING, and ROW Functions

  • Again, click OK to close the New Formatting Rule box.

Using ISODD, CEILING, and ROW Functions

  • In the end, you will see the first 5 rows of the list get our chosen highlight color.

Using ISODD, CEILING, and ROW Functions

So, we can say that our formula worked perfectly.

🔍 Breakdown of Our Formula

We are showing how our formula worked inside the conditional formatting tab for Row5.

👉 ROW(): This function returns 5.

👉 CEILING(ROW()-4,5)/5): This function returns 1.

👉 ISODD(CEILING(ROW()-4,5)/5): This function returns True and True means the value of the CEILING function is odd and the ISODD function will highlight the row.

Read More: How to Highlight Row If Cell Is Not Blank


3. Manually Selecting Every 5 Rows to Highlight in Excel

This option is a manual method for highlighting a row. You will go through the row directly and highlight your desired row. We will show you how to highlight the last 5 rows of this data list. The steps are given below:

📌 Steps:

  • First of all, select the range of cells B10:C14.

  • From the Home ribbon, select the Font > Fill Color icon.

Manually Selecting Every 5 Rows to Highlight in Excel

  • We chose the Theme Color as light green.

Manually Selecting Every 5 Rows to Highlight in Excel

  • You will see the last 5 rows turned into a light green color.

Manually Selecting Every 5 Rows to Highlight in Excel

👉 If you have a limited amount of data like us you can follow this method. However, if you are handling a vast amount of data, we recommend you not follow the other approaches.

Read More: How to Highlight Row If Cell Contains Any Text in Excel


4. Use of MOD and ROW Functions to Highlight Every 5th Row in Excel

Through this method, we will use the MOD and ROW functions to highlight the row. However, this time it will highlight every 5th row of the dataset. The procedure of this method is explained below:

📌 Steps:

  • Select the entire range of cells B4:C14.

  • From the Home tab, go to Conditional Formatting > New Rules.

Use of MOD and ROW Functions to Highlight Every 5th Rows in Excel

  • A new dialog box, titled New Formatting Rule will appear. Now, select Use a formula to determine which cells to format.

  • In the empty box below Format values where this formula is true write down the following formula.
=MOD(ROW(),5)=4
  • After that, click on the Format option.

Use of MOD and ROW Functions to Highlight Every 5th Rows in Excel

  • Another dialog box, called Format Cells, will appear. Go to the Fill option.
  • We changed the white cell color into a light green color to identify the highlight cells. You will also see the color in the Sample section.
  • Click OK to close the Format Cells dialog box.

Using ISODD, CEILING, and ROW Functions

  • Again, click OK to close the New Formatting Rule box.

Use of MOD and ROW Functions to Highlight Every 5th Rows in Excel

  • You will see the 5th row with our chosen highlight color.

Use of MOD and ROW Functions to Highlight Every 5th Rows in Excel

At last, we can say that our formula worked perfectly to highlight the 5th row in Excel.

🔍 Breakdown of Our Formula

We are showing how our formula worked inside the conditional formatting tab for Row 9.

👉 ROW(): This function returns 9.

👉 MOD(ROW(),5)=4: This function returns True, and True means the remainder value of the ROW function, and 5 is equal to 4 and the format will highlight the row.


Download Practice Workbook

Download this practice workbook for practice while you are reading this article.


Conclusion

We are ending the article here today. I hope that this article will be helpful for you and you will be able to highlight every five rows in your Excel worksheet. If you have any further queries or recommendations, please share them with us in the comments section below. Keep learning new methods and keep growing!


Related Articles


<< Go Back to Highlight Row | Highlight in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Soumik Dutta
Soumik Dutta

Soumik Dutta, having earned a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a key role as an Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Soumik not only skillfully addresses complex challenges but also demonstrates enthusiasm and expertise in gracefully navigating tough situations, underscoring his unwavering commitment to consistently deliver exceptional, high-quality content that... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo