How to Highlight Every Other Row in Excel (2 Easy Ways)
The fastest way to highlight every other row in Excel is to turn your data into a table: click a cell in the data, press Ctrl + T and click OK. Excel shades every other row automatically (banded rows).
Don’t want a table? Use conditional formatting with this formula instead:
=MOD(ROW(),2)=0
Here’s the quick version:
- Select the rows you want to shade.
- Go to Home > Conditional Formatting > New Rule.
- Choose Use a formula to determine which cells to format and type =MOD(ROW(),2)=0.
- Click Format, pick a fill color on the Fill tab and click OK twice.
Both ways are automatic, so you never have to color rows by hand 🙈 Below, I’ll walk you through both step by step, plus odd rows, every 3rd row, every other column and how to keep the stripes when you filter.
Download the sample workbook for this guide here and follow along.
Table of Contents
How to highlight every other row with conditional formatting
Here’s the first method: conditional formatting with a small formula 🎨
The image below shows a dataset with multiple rows (don’t mind the details).

We are on a mission to highlight alternate rows from this dataset using conditional formatting.
So here we go 🚴♀️
- Select the whole data range where you want the conditional formatting applied, starting at A2 (for example A2:F100).
To apply it to the whole worksheet, click an empty cell and press Ctrl + A to select the entire sheet. (If you press Ctrl + A inside your data, Excel selects just the data first.)
Pro Tip!
Leave out the headers (if your dataset has any) if you don’t want them to be highlighted too.

- Go to Home > Conditional Formatting > New Rule.

This takes you to the “New Formatting Rule” window as follows 🚪

- Under Select a Rule Type, select Use a formula to determine which cells to format.
- In the Format values where this formula is true box, type the following formula.
=MOD(ROW(),2)=0

Pro Tip!
What does this formula mean?
- The ROW function returns the row number of the cell. For example, =ROW() in cell A2 returns 2.
- The MOD function returns the remainder after a division. For example, =MOD(5,2) returns 1, because 2 goes into 5 two times (4) and 1 is left over.
So =MOD(ROW(),2)=0 divides each row number by 2 and checks if the remainder is 0. That’s TRUE for all even rows, so all even rows get highlighted 🔔
For example:
- Row 2: MOD(2,2) returns 0. TRUE, so row 2 is highlighted.
- Row 3: MOD(3,2) returns 1. FALSE, so row 3 is not highlighted.
- Row 4: MOD(4,2) returns 0. TRUE, so row 4 is highlighted.
- Row 5: MOD(5,2) returns 1. FALSE, so row 5 is not highlighted.
- Next, click Format to select the highlighting color 😍

- Go to the Fill tab.
- Choose the color to highlight the rows. We are selecting a green color for now.

- Click OK, and OK again. And tada 🌍

Every other row is now highlighted. Pretty straightforward, right?
Pro Tip!
Because the rule is based on the row number, the stripes stay perfect when you sort the data or insert and delete rows inside the range 👇
Two things to know:
- New rows below the selected range aren’t covered. Select a bigger range from the start, or use a table (next method).
- When you filter, hidden rows break the pattern. See how to keep the stripes in filtered data.
How to highlight every other row with a table style
The second method is built right into Excel: tables come with banded rows 📫
So here’s our dataset.

Right now, this is simply some data populated in Excel. Let’s convert it into a table.
- Select the data (or just click a cell in it).
- Go to Insert > Table, or press Ctrl + T.

This opens the Create Table dialog box.

- Check that the range is right, and select My table has headers if the first row holds your headers 🎓
- Click OK.

And your data is converted into a table. Yay!
Excel shades every other row by default. That’s the Banded Rows option on the Table Design tab. Want a different color? Pick another table style:
- Click any cell in the table.
- Go to Table Design and click the More arrow in the Table Styles gallery.
Here’s a huge list of table designs 📃

- Select any of these (as desired). How about this?

Play around with different styles to see if any of them fits your taste!
The big plus of a table: the banding continues automatically when you add or delete rows in the table. You can also get it straight from Home > Format as Table.
Don’t want a table, but want to keep the stripes? Click in the table and choose Table Design > Convert to Range. Just know that new rows won’t get banded automatically after that.
Odd rows, every nth row, every other column and filtered data
Change the formula in the conditional formatting rule to get other patterns:
- Odd rows instead of even rows: =MOD(ROW(),2)=1
- Every 3rd row: =MOD(ROW(),3)=0 (change 3 to any number for every nth row)
- Every other column: =MOD(COLUMN(),2)=0. In a table, clear Banded Rows and select Banded Columns on the Table Design tab instead.
Keep the stripes when you filter
=MOD(ROW(),2)=0 looks at the real row numbers. When a filter hides some rows, two shaded (or two white) rows can end up next to each other.
To shade every other visible row, use SUBTOTAL, which ignores rows hidden by a filter. If your data starts in row 2 and column A is never empty, select the whole data range starting at A2 (for example A2:F100) and use:
=MOD(SUBTOTAL(3,$A$2:$A2),2)=0
SUBTOTAL(3,…) counts the visible, non-empty cells from A2 down to the current row, so the count only goes up for rows you can see.
That’s it! Now what?
Now you know two ways to highlight every other row in Excel: a table style (fastest) and conditional formatting with MOD and ROW (most flexible). Both save you from coloring rows by hand ⏳
Excel functions like MOD and ROW are what make tricks like this possible, and there are lots of them. And to become an Excel expert, you must know them all.
To start with, we suggest you go with the VLOOKUP, SUMIF, and IF functions of Excel. Want to learn them already?
Enroll in my 30-minute free email course that will help you learn them all (and much more).
Other resources
Want to know more about conditional formatting in Excel? Our guide will take you through it.
And why only conditional formatting? Hop on here to learn about the ROW and MOD functions of Excel too.
Frequently asked questions
What is the formula to highlight every other row in Excel?
=MOD(ROW(),2)=0 in a conditional formatting rule (Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format). It highlights the even rows. Use =MOD(ROW(),2)=1 for the odd rows.
Is there a shortcut to shade every other row?
Yes. Click a cell in your data and press Ctrl + T, then Enter. The data becomes a table with banded rows.
How do I remove the alternating row colors?
In a table, click a cell in it and clear Banded Rows on the Table Design tab. For conditional formatting, go to Home > Conditional Formatting > Manage Rules and delete the rule (or use Clear Rules).
Can I highlight every other row in Excel for the web?
Yes. The table method is the quickest in Excel for the web: tables shade every other row by default there too. Microsoft’s help page on alternate shading says custom formula rules for this aren’t available in Excel for the web, so if you don’t see the formula option, use the table.
How do I select every other row in Excel?
Add a helper column with =MOD(ROW(),2), filter that column to show only 0 (or 1), and select the visible rows. Now you can format, copy or delete them in one go. Clear the filter and delete the helper column when you’re done.
