How to Alternate Row Colors in Excel?

Adding alternate row colors in your data is a great way to enhance its readability. It makes it easier for your intended audience to find a record and see its related information. Plus, it’s so nice to look at!

When you look at the image below, I’m pretty sure your eyes are immediately drawn to the table with alternate row colors.

Adding alternate colors (also known as color banding) can immediately amp up your data and make it more professional-looking.

Before you proceed, you might want to freeze the top rows first so you get to see the header names as you scroll down the page.

1. Alternate Row Colors in Excel by Formatting the Cells as a Table

This method is probably my favorite as it offers preset formats that you can readily choose from and only involves a few steps!

1. Select your entire dataset.

2. From the Home menu, go to the Styles section and click on the Format as Table button.

A list of preformatted table designs will appear. Select the design of your liking – one with alternate row colors.

3. Once selected, the Create Table prompt will appear. Click OK to continue.

NOTE: If you don’t want to convert your cells into a table, don’t worry. We can easily convert them back to regular cells later on.

4. And that’s it! You already have your data in alternate row colors. Easy-peasy, right?

What’s great about having your data in table format is that when you add new rows at the bottom or delete rows in between, Excel automatically adds or adjusts the alternate colors.

QUICK TIP:

If you want to add the alternate colors to the columns, instead of rows, you only need to:

1. Select a cell within the table.
2. Go to the Table Design menu.
3. From the Table Style Options, uncheck the Banded Rows checkbox and tick the Banded Columns checkbox.

You’ll then have alternate colors for your columns (as shown below).

To convert your table back to the normal range:

1. Right-click on any cell within the table.

2. From the dropdown list, select Table >> Convert to Range.

3. A prompt will appear to confirm the process. Click Yes to continue.

4. That’s it! Your cells are now converted back to a normal range with the table design still intact.

Note that if you add new rows to the range, the alternate row colors are no longer automatically added. Use the Format Painter to copy the cell format from the existing rows to the new ones.

2. Alternate Row Colors in Excel Without a Table by Adding Conditional Formatting

This next method may involve more steps, but it allows you to pick the alternate colors to apply upfront.

Also, this gives you the option to specify how many rows before an alternate color is added. (More about this in the latter part of the article).

1. Select the cells you want to apply the alternate row colors to.

If you like, you can select the entire column where your data are so that alternate colors are still added to the bottom rows even if they have no data yet.

Or you can opt to hide the rows containing blank cells for a cleaner look.

2. From the Home menu, go to the Styles section and click on the Conditional Formatting button.

From the list of options, select New Rule…

3. The New Formatting Rule form will appear.

From the list of Rule Types, select the “Use a formula to determine which cells to format” option.

Once selected, type either of these formulas in the formula textbox:

• =MOD(ROW(),2)=0
• =ISEVEN(ROW())

These two formulas are only checking if the current row is even.

If this condition is met, Excel will apply the cell format that you will specify next.

Click on the Format… button.

The Format Cells menu will appear.

Go to the Fill tab and select the color you would like to apply to the rows in even numbers.

The selected color will appear in the Sample section at the bottom. If you’re happy with it, click OK.

Click OK again to the New Formatting Rule menu.

That’s it! There’s now a cell color to your rows in even numbers.

Pretty cool, right? You can stop here.

But if you would like to add color to the other set of rows, that is, the odd number rows, redo the same steps as above, except that in the formula textbox, type either of the following formulas:

• =MOD(ROW(),2)>0
• =ISODD(ROW())

These formulas will check if the current row is an odd number.

2.1. Add alternate color to every nth number of rows

There are certain types of datasets where two or more data rows are referring to just one record.

For these kinds of datasets, it would be ideal to highlight multiple rows at a time so that it would be easier to identify rows that are related to each other.

In my example below, there are 3 members for each group – which is equivalent to 3 rows per record.

Note that the number of rows per record is fixed.

Apply the same steps in adding the conditional formatting and use this formula:

=MOD(ROW()-[starting row],[row count]*2)+1<=[row count]

• [starting row]
  • Refers to the first data row (or the row that comes after the header).
  •  If your header is in row 1, your [starting row] would be 2.

• [row count]
  • Refers to the number of rows to be grouped.
  • In my example above, [row count] would be 3.

With my example, my formula would be:

=MOD(ROW()-2, 3*2)+1<=3

And that’s it! You will have alternate colors for every nth number of rows.

You can manually add the row color for the header to have it stand out, like what I did in the example above.

But for tables with calculations, you might consider deleting every other row if you find that the data in them isn’t needed.

2.2. Add alternate row colors based on a specific field

If the number of rows per record varies, don’t worry. There is still a way for you to have alternate row colors.

Before adding the conditional formatting, we need to identify first the column that serves as the basis for saying that a group of rows is related to each other.

We will refer to this column as our “Parent Field”.

In my example below, my “Parent Field” is column A (Group Name).

Sort the data by this column so that related rows are next to each other.

Next, go to the last column in your dataset, and add a new column.

In this new column, we will add a formula to determine if the current row belongs to the same group as the previous row.

I suggest adding it as the last column so you can easily hide it later.

In my case, I added it in column F (as shown below).

The formula to use is:

=MOD(IF(ROW()=2,0,IF($A2=$A1,F1,F1+1)),2)

Add this formula to the same row as your first data row (e.g., row 2).

We are going to modify this formula to match your dataset.

1. Change the highlighted letters with the column letter where your “Parent Field” is located.

2. Change the highlighted letters with the column letter where you have placed this formula.

3. Change the yellow highlighted numbers with the row number containing your first data row.

Change the green highlighted numbers with the row number minus 1.

Copy this formula to the rest of the cells in that column.

This formula marks rows with 1’s and 0’s to identify rows within the same group.

Now, it’s time to add conditional formatting.

Follow the same steps in adding the conditional formatting but use this formula: =$F1=1

Change the letter “F” with the column where you have added the formula. 

And that’s it! You should now have alternate row colors added.

Hide the column containing the formulas by right-clicking on the entire column and selecting Hide.

2.3. Add alternate colors to columns

If you would instead like to apply alternate colors to your columns, use either of the following formulas:

Edit the conditional formatting

If you want to choose a different color for the rows (or columns):

1. From the Home menu, click on the Conditional Formatting button and select Manage Rules…

2. The Conditional Formatting Rules Manager will appear.

In the Show formatting rules dropdown list, select “This Worksheet”.

This will ensure that all the conditional formatting within the worksheet appears in the list of rules.

Next, select the formatting rule that you would like to edit. Look for the formula that you have previously added. Once selected, click the Edit Rule button.

You will then see the same menu when you added the conditional formatting. Click on the Format button to select a different cell color.

Remove the Conditional Formatting

If you changed your mind and would like to remove the alternate colors altogether:

1. Select the cells with alternate rows or columns.
2. Go to the Homemenu and click on theConditional Formatting button.
3. Select Clear Rules >> Clear Rules from Selected Cells.

Conclusion

Adding alternate row colors is a simple yet effective way to give your data a more professional appeal. I hope the suggestions above have helped you achieve the format that you are looking for.