How to alternate row colors in Excel: a step-by-step guide

As a marketer, I spend a significant amount of time working with spreadsheets. Excel is my go-to tool for organizing data and creating reports, but let's be honest: staring at rows and columns of plain data can be pretty boring.

That's why I love using formatting options to make my spreadsheets more visually appealing. One of my favorite tricks is to alternate row colors to create a simple but effective design.

If you're not sure how to do this, don't worry! In this step-by-step guide, I'll walk you through the process of alternating row colors in Excel so you can create beautiful, organized spreadsheets that will make all of your data analysis tasks feel a little bit more fun.

Step 1: Select the cells you want to format

Before you can start alternating row colors, you need to select the cells you want to format. This can be a single column of data, an entire sheet, or anything in between.

To select the cells you want to format, click on the first cell and drag your mouse to select the rest of the cells. If you're selecting an entire sheet, click on the tiny triangle in the top left corner of the sheet to select everything.

Step 2: Open the conditional formatting menu

Once you've selected your data, it's time to open the conditional formatting menu. This menu allows you to apply formatting rules to cells based on certain conditions.

To access the conditional formatting menu, go to the Home tab in the Excel ribbon and click on the Conditional Formatting button. This will open a dropdown menu with several formatting options.

Step 3: Choose the "New Rule" option

In the Conditional Formatting dropdown menu, choose the "New Rule" option. This will open a dialog box where you can set up your formatting rules.

Step 4: Select the "Use a formula to determine which cells to format" option

In the New Formatting Rule dialog box, you'll see several options for how to format your cells. The one we're interested in is "Use a formula to determine which cells to format."

Select this option and a new field will appear where you can enter your formatting formula.

Step 5: Enter the formula to alternate row colors

Now it's time to enter the formula that will alternate row colors in your selected cells.

The formula you'll need to use is:


This formula will check each row number and apply formatting to every other row, starting with the first row.

Step 6: Choose your formatting options

Once you've entered the formula, it's time to choose your formatting options. You can choose from a variety of options, including font color, background color, and fill pattern.

To alternate row colors, choose a background color for one row and a different color for the next row. You can experiment with different colors to find a design that works for your data.

Step 7: Apply your formatting

After you've chosen your formatting options, click "OK" to apply the formatting to your selected cells. You should see the alternating row colors appear immediately.

Step 8: Save your work

Once you've finished formatting your cells, don't forget to save your work! You can do this by clicking on the Save button in the Excel ribbon or pressing Ctrl+S on your keyboard.


Formatting your spreadsheets can seem like a tedious task, but it can make a big difference in how your data is understood. Alternating row colors is just one way to make your spreadsheets more visually appealing and easier to read.

By following this step-by-step guide, you can easily set up alternating row colors in Excel and create beautiful, organized spreadsheets that will make all your data analysis tasks a little bit more fun.

By clicking “Accept”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. View our Privacy Policy for more information.