Highlight Every Other Row in Excel

Highlight Row in Excel (Table of Contents)

• Highlight Row Using Excel Table
• Highlight Row Using Conditional Formatting

How to 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 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.

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

1. Using Excel Table.
2. Using Conditional Formatting

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 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 ‘Create Table’ dialog In that dialog box click on ‘OK’.

This will create the 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 below figure

In the below figure, it shows how modification could be done to your table by clicking the design tab and then selecting the ‘Table Styles’ option and then 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 ‘Conditional Formatting’ icon. After clicking on conditional formatting, select ‘New Rule’ option from the drop-down

Step 3: After selecting 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 ‘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 case of using conditional formatting, if you add new rows within range, the highlighting or shading 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 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 formatting.
• 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 Row in Excel. Here we discuss how to highlight every other row in excel using excel table and conditional formatting along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. How to Create Gantt Chart in Excel?
2. Create Waterfall Chart in Excel
3. Creation of Scatter Chart
4. Guide to Excel Bar Chart