It can be hard to scan data when you have a large spreadsheet in Excel. Imagine having to locate the value of every Tuesday from the following data. You are left squinting through the rows and rows of drudgery. What a nightmare!

Luckily, there is an option to highlight every other row and make it more readable. This can help you find information at a glance. For this, you can use the following two approaches:

## Format Data as Table

This is the quickest and most simple way to highlight rows. Excel offers a wide variety of table designs with alternate shaded rows. You can select from these templates or opt for the default option.

If you are looking to highlight data just for aesthetic purposes and don’t care if it is in table format, this method is best for you.** As Excel has pre-built table styles, you do not need any formula for this approach. **

Tables are dynamic, so whenever you add extra rows, Excel will automatically highlight them too.

To change to table format,

- Launch Excel.
- Go to your
**Worksheet**. - Highlight the
**cells**and navigate to**Home Tab**. - On Styles menu, click on
**Format as Table**. Then, choose a**Table style**with alternate row highlights. - Check the
**box**for My table has headers. Pick**OK**to confirm. - To change the highlight colour, select a
**cell**and go to**Table Design**tab. - Pick a colour from
**Table Styles**.

`Quick Tip: In case you want to get rid of table format while keeping highlights, convert the table to a normal range. For this, navigate to `**Table Design **> **Convert to Range**. Note that when you do this, you will no longer get automatic highlights after adding rows.

## Use Conditional Formatting

Conditional Formatting is one of the best tools to shade rows or columns based on different conditions. For Instance, shade every row with the name “John” or value “less than 10” in your worksheet.

You can use this feature especially to draw attention to specific data. In this method, we will use formulas to highlight rows depending on each format rule.

### Every Alternate Row

To highlight every alternate row, we will use the MOD ROW formula in Excel. This formula will check whether the rows are **odd **or **even** and apply shades accordingly.

Let’s see how this formula works with an example first.

**Case 1 [Highlights every alternate even rows]**

**=MOD(ROW(),2)=0 **

In this condition, when you divide each row by 2, you get 0 as a remainder. So, it will identify even rows and shade them only.

For example: **=MOD(ROW(4),2)=0 (**4 is divisible by 2. So, we will get 0 as the remainder)

**Case 2 [Highlights every alternate odd rows]**

**=MOD(ROW(),2)=1**

In this condition, when you divide rows by 2, you will get 1 as the remainder. It means these are odd rows. So, Excel will find the odd rows and highlight them.

For Example: **=MOD(ROW(3),2)=1** (3 is not divisible by 2. So, we will have 1 as the remainder)

Now, you can use one of the given formulas to highlight every alternate even or odd row. Here’s how you do it.

- On your worksheet, highlight the cells to format. Then, go to
**Home**Tab. - Click on
**Conditional Formatting**>**New Rule**. - On New Formatting Rule box, pick
**Use a formula to determine which cells to format**. - Then, enter formula
`=MOD(ROW(),2)=0`

**=MOD(ROW(),2)=1** - Click on
**Format**. - Navigate to the
**Fill**tab and pick a**Colour**to highlight. Then, click on**OK**. - Again, click
**OK**.

### Every nth Row

It’s more easier to find the exact cell ranges when you highlight every nth row in your spreadsheet. We will again use the MOD ROW formula to apply this condition. However, this time, we will add cell values to the formula.

As a reference, we have provided an example to highlight every 3rd or 5th row below.

- On your worksheet, highlight the cells to format.
- Go to
**Home**Tab. - Click on
**Conditional Formatting**>**New Rule**. - On New Formatting Rule box, pick
**Use a formula to determine which cells to format**. - On Formula bar, enter
(For A2:C2, highlight the first rows and remove dollar signs. For $D$2, click on the empty cell.)`=MOD(ROW(A2:C2),$D$2)=1`

- Select
**Format**. - On Fill tab, click on
**Colour**and hit**OK**. - Again, pick
**OK**to confirm. - On the D2 cell, enter
**3**. It will highlight every 3rd row. - Similarly, if you enter
**5**, it will highlight every 5th row.

### Every Row Based on Text

Sometimes, you would want to highlight entire rows based on a specific text to retrieve data. For example, highlight every row with the name “**John**.” There is a different rule to apply this condition.

Here’re the steps for it.

- On your worksheet, select all ranges. Then, go to
**Home**Tab. - Click on
**Conditional Formatting**>**New Rule**. - Click on
**Use a formula to determine which cells to format**. - Enter
. (For $B2, Highlight the first cell of the column with text and remove the $ sign before 2. For $D$2, Select any empty cell.)`=$B2=$D$2`

- Choose
**Format**. - Pick a
**Colour**and choose**OK**. - Again, select
**OK**. - Hover over D2 cell. Type a
**Name**to highlight and press enter.

### Every Row Based on Number

You can also highlight rows based on the number. It can come in handy when you have to select the end results and analyze them.

Let’s assume, you have to find a value that is **less than 10 **in these steps.

- On your worksheet, highlight all ranges and click on
**Home**Tab. - Navigate to
**Conditional Formatting**>**New Rule**. - Select
**Use a****formula to determine which cells to format**. - Enter
. (For $C2, select the first cell with number value. You can replace number 10 with any value.)`=$C2>10`

- Click
**Format**and select**colour**to highlight. Choose**OK**. - Again, click on
**OK**button.

## How to Automatically Highlight Every Other Row When You Add Data in Excel?

Sometimes your data may be incomplete at the time of highlighting rows. Fortunately, there’s also a formula to automatically highlight every other row when you add new data. Let’s check them out below.

- Launch
**Excel**. - Open your workbook and highlight the entire
**Cell area**. - Click on
**Home**>**Conditional formatting**. - Choose
**New Rule**. - Under Select a Rule Type, click
**Use a formula to determine which cells to format**. - Enter
(For A1, select the top-left cell where your data begins.)`=AND(MOD(ROW(),2)=0, A1<>””)`

- Then, choose
**Format**. - Click on
**Fill Tab**. Pick a**colour**to highlight and choose**OK**. - Again, click
**OK**. Highlight will appear once you enter data.