Excel Tutorial: How To Make Every Other Row Shaded In Excel


When working with large datasets in Excel, unshaded rows can make it difficult to read and analyze the information. Shading every other row not only improves the visual appeal of the spreadsheet but also makes it easier to track data and identify patterns. In this tutorial, we will walk through the steps to achieve shaded rows in Excel, ensuring your data is presented in a clear and organized manner.

Key Takeaways

  • Shading every other row in Excel improves data visualization and analysis
  • Conditional formatting can be used to achieve shaded rows
  • Using the formula "=MOD(ROW(),2)=0" will shade every other row
  • Removing blank rows is important for a clean and organized dataset
  • Exploring other formatting options in Excel can further enhance data presentation

Step 1: Select the data range

A. Open the Excel sheet with the data to be formatted

B. Highlight the range of cells where you want to shade every other row

Step 2: Open the Conditional Formatting menu

After opening your Excel spreadsheet, the next step is to open the Conditional Formatting menu. Here's how to do it:

A. Navigate to the "Home" tab in Excel

First, navigate to the "Home" tab at the top of the Excel window. This is where you'll find the options for formatting your spreadsheet.

B. Find and click on the "Conditional Formatting" option

Once you're on the "Home" tab, look for the "Conditional Formatting" option in the ribbon. It's usually located in the "Styles" group, but this may vary depending on your version of Excel.

C. Select "New Rule" from the dropdown menu

After clicking on the "Conditional Formatting" option, a dropdown menu will appear. From this menu, select "New Rule" to begin the process of adding a new formatting rule to your spreadsheet.

Step 3: Set up the formatting rule

After selecting the range of cells to format, you will need to set up the formatting rule to shade every other row in Excel.

  • Choose the option for "Use a formula to determine which cells to format"
  • Enter the formula "=MOD(ROW(),2)=0" to only format even rows
  • Click on the "Format" button to choose the shading color for the selected rows

Step 4: Apply the formatting

After setting the formatting options, it's time to apply the rule to shade every other row in Excel.

A. Once the formatting options are set, click "OK" to apply the rule

After selecting the desired formatting options, such as the fill color, click "OK" in the Conditional Formatting dialog box to apply the rule.

B. The selected range should now have every other row shaded in the specified color

After clicking "OK," you should now see that every other row within the specified range is shaded in the color you selected. This formatting will make your data easier to read and analyze.

Step 5: Remove blank rows

If there are blank rows within the data range, it's important to remove them to ensure a clean and organized dataset.

A. Highlight blank rows:

  • Select the entire dataset where you suspect blank rows may be present.
  • Go to the "Home" tab on the Excel ribbon.
  • Click on the "Find & Select" option in the editing group.
  • Choose "Go To Special" from the dropdown menu.
  • Select "Blanks" and click "OK."
  • This will highlight all the blank cells within the selected range.

B. Delete blank rows:

  • After highlighting the blank rows, right-click on any of the highlighted cells.
  • From the context menu, select "Delete."
  • Choose "Entire Row" and click "OK."
  • This will remove the blank rows from the data set, ensuring a continuous range without any interruptions.

By removing the blank rows, you can maintain the consistency and integrity of your dataset, making it easier to analyze and work with in Excel.


A. Shading every other row in Excel is a simple yet effective way to enhance the visual appeal of your data. Remember, the steps include selecting the range of cells, navigating to the Home tab, and applying the appropriate formatting with the Fill Color tool.

B. Organizing and visualizing data is crucial for better analysis and decision-making. By using shading and other formatting options, you can make your data easier to read and understand, ultimately leading to more informed insights.

C. I encourage you to practice shading every other row in Excel and to explore other formatting options to improve the presentation of your data. With a little creativity and experimentation, you can take your data visualization skills to the next level.

Excel Dashboard

ONLY $99

    Immediate Download

    MAC & PC Compatible

    Free Email Support

Related aticles