Color Every Other Row Excel

Color Every Other Row Excel

Learn the color every other row Excel function. When you have a big spreadsheet with multiple rows, it is easier to read through it when every other entry has a different background color. You can customize your Excel worksheet to have every row, or every two or three rows, to have a different color so you can sort through information easily.

Download the example spreadsheet to begin.

When you open the document, you will see a list of 20 names and email addresses. If you are reading off this information or trying to sort the data, it can be difficult to follow each row exactly. An easy way to remedy this is to color every other row, or however many rows you want spaced. The MOD(ROW function will accomplish this for you.

In the example, highlight cells B5 through E24 (all cells with data in them). Next, click “Conditional Formatting” and in the dropdown from there, choose “create a new rule”. The dialog box that comes up will have many options, choose the last option labeled “Use a formula to determine…”

Under the formula description, enter this formula:


Don’t click OK yet – instead choose “Format” and then select the fill (highlight) color you want to use for every other row. Then press OK.

The dialog box will disappear and your spreadsheet will now have every other row highlighted so you can easily navigate among the data.

In the example, the “2” in the formula is a “3”, which would cause every third row to be highlighted. You can substitute the number with any number you like. For instance, if every five entries there was one you wanted to pull, then simply replace the 2 with a 5 and it will highlight every 5th entry.

To learn more about Excel’s capabilities, visit our guides section.

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.