Excel Tutorial: How To Make Excel Rows Alternate Color


When working with large sets of data in Excel, it can be challenging to keep track of information, especially when presented in long lists or tables. One way to make the data more readable and organized is by alternating row colors. This simple yet effective technique can help to visually differentiate between rows, making it easier to follow the data and identify patterns. In this tutorial, we will cover the step-by-step process to make excel rows alternate color, allowing you to enhance the readability of your Excel spreadsheets.

Key Takeaways

  • Alternating row colors in Excel can improve the readability of large sets of data
  • Using conditional formatting to create alternating row colors is a simple and effective technique
  • Applying the alternating color rule using a formula =MOD(ROW(),2)=0 helps to visually differentiate between rows
  • Reviewing and adjusting the formatting is essential to ensure the desired results
  • Utilizing this feature can enhance the organization and visual appeal of Excel spreadsheets

Step 1: Open your Excel spreadsheet

Before you can start alternating row colors in Excel, you need to have your spreadsheet open and ready for editing. Follow these simple steps to get started:

A. Launch Microsoft Excel

  • Locate the Microsoft Excel application on your computer and double-click to open it.

B. Open the spreadsheet you want to edit

  • Once Excel is open, navigate to the file menu and select "Open" to browse for the spreadsheet you want to work on.
  • Click on the file to open it in Excel.

C. Navigate to the Home tab

  • Once your spreadsheet is open, look for the "Home" tab at the top of the Excel window.
  • Click on the "Home" tab to access the formatting options you'll need to alternate row colors.

Step 2: Select the range of cells

Once you have your Excel sheet open and ready to go, the next step is to select the range of cells that you want to format with alternate row colors. Here’s how to do it:

A. Click and drag to select the entire range of cells you want to format

Start by clicking on the first cell in your range, then drag your mouse down and to the right to encompass all the cells you want to format. You should see them all become highlighted as you do this.

B. Ensure all relevant rows are included in the selection

Make sure that you have selected all the rows you want to apply the alternate row colors to. It’s important to double-check this, so you don’t miss any rows.

C. Be mindful of any headers or totals at the top or bottom of the data

If you have any headers or totals at the top or bottom of your data, be sure to include them in your selection. It’s important to make sure that your formatting doesn’t interfere with any important information in your spreadsheet.

Step 3: Open the conditional formatting menu

Once you have selected the range of cells that you want to format with alternating colors, it's time to open the conditional formatting menu.

A. Click on the 'Conditional Formatting' option in the 'Styles' group

First, go to the 'Home' tab on the Excel ribbon. Look for the 'Styles' group, where you will find the 'Conditional Formatting' option. Click on it to open the drop-down menu.

B. Choose 'New Rule' from the drop-down menu

After clicking on the 'Conditional Formatting' option, you will see a drop-down menu. From this menu, select the 'New Rule' option to open the 'New Formatting Rule' dialog box. This is where you will set up the rule for alternating row colors.

Step 4: Set up the alternating color rule

After selecting the range to format and accessing the conditional formatting options, follow these steps to set up the alternating color rule:

A. Select 'Use a formula to determine which cells to format'

First, click on the 'Format' dropdown menu within the conditional formatting dialog box and select 'Use a formula to determine which cells to format' from the list of options.

B. Enter the formula for alternating colors: =MOD(ROW(),2)=0

Once the 'Use a formula to determine which cells to format' option is chosen, enter the formula =MOD(ROW(),2)=0 in the formula field. This formula uses the MOD function to check if the row number is even, and if so, applies the formatting.

C. Choose the desired formatting options for the even rows

After entering the formula, click on the 'Format' button next to the formula field to choose the desired formatting options for the even rows. This can include setting the fill color, font color, borders, and other visual attributes to distinguish the even rows from the odd ones.

Step 5: Apply the rule and review the results

After setting up the alternating color rule, it's time to apply it and review the results. Here are the steps to follow:

A. Click 'OK' to apply the alternating color rule

Once you have finished setting up the rule in the Conditional Formatting menu, click the 'OK' button to apply the rule to your Excel worksheet.

B. Confirm that the rows are now formatted with alternating colors

Take a moment to review your worksheet and confirm that the rows are now formatted with the alternating colors that you specified. This visual cue can make it easier to read and interpret your data.

C. Make any necessary adjustments to the formatting or formula

If you notice any issues with the alternating color pattern, or if you need to make adjustments to the formatting or formula in your worksheet, you can do so at this time. Simply go back to the Conditional Formatting menu and make any necessary changes before reapplying the rule.


Creating alternating row colors in Excel is a simple yet effective way to enhance the visual appeal and readability of your spreadsheets. To recap, simply select the range of cells you want to format, navigate to the Home tab, click on the ‘Format as Table’ option, and choose a table style that includes alternating row colors. This formatting technique not only makes it easier to scan and understand data at a glance, but it can also help highlight patterns and trends within your spreadsheet.

By making use of this feature, you can significantly improve the overall look and feel of your Excel documents, making them more user-friendly and professional. So, why not give it a try and see the difference it can make in your own spreadsheets?

Excel Dashboard

ONLY $99

    Immediate Download

    MAC & PC Compatible

    Free Email Support

Related aticles