How To Highlight Every Other Row In Excel?
Sometimes, we may have to highlight rows in Excel to make the data in the spreadsheet easier to interpret. The Excel data with every other row accentuated in a different shade is more presentable and professional in appearance.
And it is best to highlight rows in Excel when presenting the spreadsheet data in PowerPoint presentations and business documents to grab the audience’s attention.
The following techniques can enable us to highlight the required rows in a worksheet:
- Using the Excel Table option
- Using the Conditional Formatting feature
- Using custom formatting
Table of contents
Key Takeaways
- When we highlight rows in Excel alternatingly, the data becomes easy to grasp and eye-catchy.
- Users can highlight every other row in a worksheet data set when using the information in presentations and documents, as highlighting makes the data appear professional.
- We can utilize the Excel Table option from the Insert tab or the Conditional Formatting feature from the Home tab to highlight alternate rows in Excel.
- Using inbuilt functions such as MOD, ROW, COUNTIF, and COUNTIFS in the Conditional Formatting rule makes highlighting rows in Excel customizable and straightforward.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
#1 – Using The Excel Table Option
The steps to highlight alternate rows in a data range using the Excel Table option are as follows:
- Click on a cell in the given data range and click the Insert tab → choose the Table option or use the keyboard shortcut Ctrl + T to open the Create Table window.
- In the Create Table window, ensure the specified data range is correct and the check box confirming the table has headers remains selected.
- Next, click OK in the Create Table window to obtain the Excel table with alternate rows highlighted.
- Finally, use the Design tab options, such as Table Styles, to enhance the highlighted rows’ appearance according to the requirement.
#2 – Using The Conditional Formatting Feature
The steps to highlight the required rows in a data range using the Conditional Formatting feature are as follows:
- Select the given data range without the column headers. And click the Home tab → choose the Conditional Formatting feature à select the New Rule option to open the New Formatting Rule window. Otherwise, open the window with the keyboard shortcut Alt + O + D.
- Select the last Rule Type to enter the formula that evaluates the cells to highlight or format in the New Formatting Rule window.
- Next, enter the formula in the Edit the Rule Description section field in the New Formatting Rule window to highlight the required rows.
- Click Format in the New Formatting Rule window to open the Format Cells window.
- Click the Fill tab in the Format Cells window to choose the color to show the highlighted rows in the output.
- Click OK twice to close the Format Cells and New Formatting Rule windows and obtain the output with the required rows highlighted according to the specified format.
#3 – Using Custom Formatting
The steps to highlight alternate rows in a data range using the Conditional Formatting feature are as follows:
- Select the given data range without the column headers and click the Home tab → choose the Conditional Formatting feature à select the New Rule option.
- The New Formatting Rule window opens, where we must select the last Rule Type to enter the formula that evaluates the cells to highlight or format.
- Next, enter the MOD and ROW excel function -based formula to highlight every Nth row in the Edit the Rule Description section field.
=MOD(ROW(),N)=0
Or
=MOD(ROW(),N)=1
However, the two formulas work differently.
While the first formula highlights the rows being multiples of N, the second formula first highlights the Nth + 1 row, followed by every Nth row.
- Click Format in the New Formatting Rule window to open the Format Cells window.
- Click the Fill tab in the Format Cells window to choose the color to show the highlighted rows in the output.
- Click OK twice to close the Format Cells and New Formatting Rule windows and obtain the output with the required rows highlighted according to the specified format.
Examples
Check out the following illustrations explaining the three methods to highlight rows in Excel.
Example #1 – Highlight Rows Using Excel Table
We shall see an example of alternate highlight rows in Excel.
The table below shows employee names, their designations and dates of joining.
If the requirement is to automatically highlight rows in Excel data shown above. Then, here is how to use the Excel Table option to show the worksheet data with every other row highlighted.
- Step 1: First, click on a cell in the given data range. And select Insert → Table or press the keyboard shortcut Ctrl + T to open the Create Table window.
- Step 2: Next, in the Create Table window, update the data range we must view in an Excel table. And check the box indicating that the table has headers.
And clicking OK will create the Excel table, as shown below.
Next, we can enhance the Excel table’s appearance by using the options in the Design tab.
- Step 3: Then, click the Excel table to enable the Design tab in the ribbon. And click the More option in the Table Styles group.
Now, Excel shows the various table styles from which we can choose the required one by clicking on the specific style option, as shown below.
Next, as soon as we click, the required style will appear in the below Excel table.
[Alternatively, we can click on a cell in the data range and click Home > Format as Table to choose and apply the required table style.
The Format As Table window opens, where we must confirm the specified data range we require as an Excel table is correct. And ensure the check box indicating the table has headers remains checked.
And clicking OK will close the Format As Table window and give the required Excel table with alternate rows highlighted in the chosen style.]
Thus, the Excel Table option helps automatically highlight rows in Excel and customize the style, making the data tailor-made for our requirements.
Example #2 – Highlight Rows Using Conditional Formatting
Before we use the Conditional Formatting feature to apply custom formatting for highlighting every other row in a given data range, we must know how the feature works.
So, here is an example of how to highlight rows in Excel based on value using the Conditional Formatting feature.
The table below contains a list of students and their GPA details.
If the requirement is to highlight rows where the students have a GPA of 4. Then, as we must highlight rows in Excel based on value, the solution is to use the Conditional Formatting feature in the Home tab.
- Step 1: To begin with, choose the given data range without the column headers. And choose New Rule under the Conditional Formatting feature in the Home tab.
The New Formatting Rule window opens.
- Step 2: Next, click the last Rule Type to enter the formula that evaluates the cells to highlight or format.
Next, enter the formula in the Edit the Rule Description section field to highlight the required rows.
=$C2=4.
And then click Format to open the Format Cells window.
- Step 3: Then, choose the color required to show the highlighted rows in the output from the Fill tab in the Format Cells window.
And click OK to close the Format Cells window.
Finally, clicking OK will close the New Formatting Rule window. And we will achieve the data range with the rows where the students have a GPA of 4, as shown below.
The formula checks the column D cells in rows 3 to 17 for the value 4. And if the condition holds in a column D cell, the corresponding row gets highlighted.
[Alternatively, choose the given data range without the column headers. And then, use the keyboard shortcut Alt + O + D to open the Conditional Formatting Rules Manager window.
Next, click the New Rule option to open the New Formatting Rule window.
And after updating the rule, click twice to close the New Formatting Rule and Conditional Formatting Rules Manager windows and achieve the desired outcome.]
Example #3 – Highlight Every Other Row In Excel Using Custom Format
Using custom format with the Conditional Formatting feature, we shall see a scenario of alternate highlight rows in Excel.
The table below shows the monthly sales figures of the different branch offices of a firm.
If the requirement is to highlight every other row in the given data range. Then, applying a custom format using the Conditional Formatting feature is an excellent option.
- Step 1: First, choose the given data range without the column headers. And then, choose New Rule under the Conditional Formatting feature in the Home tab.
Next, the New Formatting Rule window opens. Here, we must pick the last Rule Type to enter the formula that evaluates the cells to highlight or format.
Next, enter the formula in the Edit the Rule Description section field to highlight the required rows.
=MOD(ROW(),2)=1
And click Format to open the Format Cells window.
- Step 2: Next, choose the color required to show the highlighted rows in the output from the Fill tab in the Format Cells window.
Clicking OK will close the Format Cells window.
Finally, clicking OK will close the New Formatting Rule window and give the output as shown below.
The MOD() calculates the modulo of the row number, the ROW() returns, and the value 2 for each row. And if the result is 1 for a row, the condition holds. Thus, the corresponding row gets highlighted in the chosen format.
For example, the ROW() returns the value 3 for row 3. And the MOD() determines 3 modulo 2, which is 1. So, the condition holds in row 3, and row 3 gets highlighted in the specified format color.
How To Break Down The Formula?
The last example in the previous section showed the formula to highlight rows in Excel alternatingly.
In this section, we shall break down the formula to understand how it works. However, we shall modify it slightly to verify its practicality using an example.
The table below lists products, their brands and availability status.
And the requirement is to shade every other group of three rows, starting from the second group so that every alternate brand gets highlighted.
Then, we can apply the MOD and ROW functions-based formula to highlight rows in Excel and achieve the desired outcome.
- Step 1: To start with, choose the given data range without the column headers. And choose New Rule under the Conditional Formatting feature in the Home tab.
The New Formatting Rule window opens, where we must choose the last Rule Type to enter the formula that evaluates the cells to highlight or format.
Next, enter the formula in the Edit the Rule Description section field to highlight the required rows.
=MOD(ROW()-2,6)>=3
And click Format to open the Format Cells window.
- Step 2: Now, choose the color required to show the highlighted rows in the output from the Fill tab in the Format Cells window.
And click OK.
Finally, clicking OK again will close the New Formatting Rule window and result in the below outcome.
While the ROW() returns the current row number, the formula deducts the value 2 from it. And then, the MOD() determines the resulting number modulo 6. Next, the formula checks if the MOD() return value equals or exceeds 2. And if the condition holds, the corresponding row gets highlighted in the chosen color.
For example, the ROW() in the row 2 formula returns the row number 2. Next, the formula deducts 2 from 2, resulting in 0. And then, the MOD() finds 0 modulo 6, which is 0. And as 0 is less than 2, the condition does not hold. And hence, row 2 does not get highlighted.
In contrast, the ROW() in the row 13 formula returns the row number 13. Next, the formula deducts 2 from 13, resulting in 11. And then, the MOD() finds 11 modulo 6, which is 5. And as 5 exceeds 2, the condition holds. And hence, row 14 gets highlighted.
Thus, we can apply the basic formula containing the MOD and ROW functions or modify the formula to apply a custom format to highlight rows in Excel in the required order.
Important Things To Note
- When using the Conditional Formatting feature to highlight rows in Excel, do not include the column headers in the data range selection. Otherwise, the Conditional Formatting rule will apply, considering the column headers as the topmost row.
- Once we highlight rows using the Conditional Formatting feature, we cannot undo the action and changing the color used for highlighting the rows is also not feasible.
- When using the MOD and ROW functions-based formula in the Conditional Formatting rule to highlight the required rows, ensure to apply the appropriate formula.
Frequently Asked Questions (FAQs)
We can highlight blank rows in Excel utilizing the Conditional Formatting feature in the Home tab.
For example, the table below contains a list of stationery items, their quantities and order dates.
And the requirement is to highlight the empty rows in the given data range. Then, we can utilize the Conditional Formatting feature to achieve the required outcome.
• Step 1: First, select the given data range without the column headers. And then, choose New Rule under the Conditional Formatting feature in the Home tab.
The New Formatting Rule window opens, where we must choose the last Rule Type to enter the formula that evaluates the cells to highlight or format.
Next, enter the formula in the Edit the Rule Description section field to highlight the required rows.
=COUNTIF($A2:$C2,””)=3
And click Format to open the Format Cells window.
• Step 2: Next, choose the color required to show the highlighted rows in the output from the Fill tab in the Format Cells window.
And click OK to close the Format Cells window.
Finally, clicking OK will close the New Formatting Rule window. And we will obtain the following output, with the empty rows in the data range highlighted in the format chosen in the New Formatting Rule window.
The COUNTIF() checks for blank cells in the specified row range. And if the total count of empty cells is 3, the condition holds, and the corresponding row range gets highlighted.
For example, the COUNTIF() checks for empty cells in row 5 cell range A4:C4. And as all the cells in the specified cell range are blank, the function returns the value 3. And as 3 equals 3, the condition holds, and the row 5 cells in the chosen data range get highlighted according to the specified format.
We can highlight duplicate rows in Excel utilizing the Conditional Formatting feature in the Home tab.
For example, the table below contains grocery items, their quantities, and prices per lb. unit.
If the requirement is to highlight duplicate rows in the given data range. Then, here is how to use the Conditional Formatting feature and achieve the required outcome.
• Step 1: First, select the given data range without the column headers. And choose New Rule under the Conditional Formatting feature in the Home tab.
Now, the New Formatting Rule window opens. Here, we must choose the last Rule Type to update the formula required to evaluate the cells to highlight.
Next, enter the formula in the Edit the Rule Description section field to highlight the required rows,
=COUNTIFS($A$2:$A$10,$A2,$B$2:$B$10,$B2,$C$2:$C$10,$C2)>1
Now, click Format to open the Format Cells window.
• Step 2: Then, select the shade to highlight the required rows from the Fill tab in the Format Cells window.
Next, click OK to close the window.
Then, click OK to close the New Formatting Rule window. Excel will show the following outcome.
The COUNTIFS() used in the Conditional Formatting rule highlights the duplicate rows in the given data range.
It starts by checking the cell A2 value occurrences in the column range A2:A10. Likewise, it evaluates the cells, B2 and C2, values’ occurrences in the column ranges B2:B10 and C3:C10, respectively.
Also, as the three specified values occur in the same four rows of the corresponding columns, the COUNTIFS() returns a value of 4. And as 4 is greater than 1, the condition holds. So, the Conditional Formatting feature highlights row 2.
Furthermore, the feature checks the specified criterion in each row. And as the criterion holds in rows 4, 7, and 8, the rows get highlighted.
On the other hand, as the data in the remaining rows are unique, they remain untouched.
We can’t highlight multiple rows in Excel, perhaps because of the following reasons:
• The expression used in the Conditional Formatting rule is incorrect.
• You modify the source data by adding, deleting, or updating new values in the data range, but the formula used in the Conditional Formatting rule remains the same.
Download Template
This article must be helpful to understand the Highlight Every Other Row In Excel, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to Highlight Every Other Row In Excel. Here we explain the top 3 methods for highlighting alternate rows with examples & downloadable excel template. You can learn more from the following articles –
Leave a Reply