Highlight Every Other Row in Excel

Highlight Rows in Excel (Table of Contents)

• Highlight Every Other Row in Excel
• How to Highlight Every Other Row in Excel?

Highlight Every Other Row in Excel

Highlighting every other row in excel is done by the use of Conditional Formatting. This improves the readability for a user and also helps in distinguishing the different rows from other data values. Select a new rule from the Conditional Formatting list, which is under the Home menu tab, and there we can use the MOD function along with ROW to reach every second row. And then choose the color by which we want to highlight every other row in which we get the applied rule.

How to Highlight Every Other Row in Excel?

There are various methods of highlighting or shading every other row of your data in excel.

Method 1 – Using Excel Table

This is the easiest and quickest way to highlight every other row in excel. In this method, the default ‘excel table’ option is used.

Consider the below example, where any random data is entered in the excel sheet.

The steps to highlight every other row in excel by using an excel table are as follows:

Step 1: Select the entire data entered in the excel sheet.

Step 2: From the ‘Insert’ tab, select the option ‘Table’, or else you can also press ‘Ctrl +T’, which is a shortcut to create a table.

Step 3: After selecting the table option or creating the table, you will get the ‘Create Table’ dialog. In that dialog box, clicks on ‘OK’.

This will create a table like this.

The above figure shows how every other row is highlighted.

If you want your data to look or appear in a different style or format, select the ‘Table Styles’ option from the ‘Design’ tab, as shown in the below figure.

The below figure shows how modification could be done to your table by clicking the design tab, selecting the ‘Table Styles’ option, and selecting your preferred style. (Design->Table Styles)

Method 2 – Using Conditional Formatting

This method is useful when we want to highlight every Nth row in the spreadsheet. Consider the below example as shown in the figure.

In this example, if a teacher needs to highlight every 3^rd student and group them in one group. The steps to highlight every other row in excel using conditional formatting are as follows:

Step 1: Select the data which needs to be highlighted.

Step 2: Click on ‘Home Tab’, and then click on the ‘Conditional Formatting’ icon. After clicking on conditional formatting, select the ‘New Rule’ option from the drop-down.

Step 3: After selecting the new rule, a dialog box will appear, from which you need to select ‘Use a formula to determine which cells to format’.

Step 4: Under the ‘Edit the Rule Description’ section, enter the formula in the empty box. Formula is ‘=MOD(ROW(),3)=1’.

Understanding the Formula

The MOD formula returns the remainder when the ROW number is divided by 3. The formula evaluates each cell and checks if it meets the criteria. So, for example, it will check cell A3. Where the ROW number is 3, the ROW function will return 3, so the MOD function would calculate =MOD(3,3) which returns 0. But, however, this hasn’t met our designed or specified criteria. Hence, it moves to the next ROW.

Now, the cell is A4, where the ROW number is 4, and the ROW function will return 4. SO, the MOD function will calculate =MOD(4,3), and the function returns 1. This meets our specified criteria, and therefore, it highlights the entire ROW.

This formula can be customized according to your requirements. For example, consider the below modifications:

1. For highlighting every 2^nd ROW starting from the 1^st ROW, the formula would be ‘=MOD(ROW(),2)=0’.
2. To highlight every 2^nd or 3^rd COLUMN, the formula would be ‘MOD(COLUMN(),2)=0’ and ‘MOD(COLUMN(),3)=0’ respectively.

Step 5: After that, click on the ‘Format’ button.

Step 6: After that, another dialog box will appear. From that, click on the ‘Fill’ tab and select any color you like.

Step 7: Click OK. This will highlight every 3^rd row of our data in Excel.

Advantages

In the case of using conditional formatting, if you add new rows within range, the highlighting or shading of the alternate ROW would be done automatically.

Disadvantages

For a new user, it becomes difficult to understand the conditional formatting by using the formula for it.

Things to Remember about Highlight Every other Row in Excel

• In case you are using the excel table method or standard formatting option, and if you need to enter a new ROW within range, the shading for alternate ROW would not be done automatically. You need to do it manually.
• The MOD function can be used for ROW’s and COLUMN’s as well. You need to modify the formula accordingly.
• The color of the ROW’s cannot be changed if conditional formatting is done. You need to do follow the steps from first again.
• If a user needs to highlight both even and odd groups of ROW’s, then the user needs to create 2 conditional formattings.
• Use light colors for highlighting if the data needs to be printed.
• For Excel 2003 and below, the conditional formatting is done by first clicking on the ‘Format Menu’ and then ‘Conditional Formatting.

Recommended Articles

This has been a guide to Highlight Every Other Rows in Excel. Here we discuss how to highlight every other rows in excel using an excel table and conditional formatting along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

1. Add Rows in Excel Shortcut
2. Autofit Row Height in Excel
3. Shade Alternate Rows in Excel
4. ROWS Function in Excel