## 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.

### #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 N^{th}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 N^{th} + 1 row, followed by every N^{th} 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)

**1. How to highlight blank rows in Excel?**

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.

**2. Can you highlight duplicate rows in Excel?**

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.

**3. Why can’t I highlight multiple rows in Excel?**

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