How to Highlight Every Other Row in Excel: A Step-by-Step Guide

To quickly highlight every other row in Microsoft Excel, select the range of cells you want to format, go to the ‘Home’ tab, click on ‘Conditional Formatting,’ choose ‘New Rule,’ then ‘Use a formula to determine which cells to format,’ enter the formula =MOD(ROW(),2)=0, set your desired format, and click ‘OK.’

After completing this action, every other row in the selected range will be highlighted with the format you specified. This enhances the readability and visual appeal of your data, making it easier to scan and analyze.


Microsoft Excel is a powerhouse for data analysis, and part of that power comes from its ability to organize and present data in a visually appealing way. One method for making large datasets more readable is to highlight every other row, also known as “zebra striping.” This technique creates a subtle visual rhythm that guides the eye and reduces the strain of tracking data across a screen or page. It’s particularly useful when dealing with long lists of numbers or entries where it’s easy to lose your place.

But why is this topic important, and who needs to know about it? Anyone who uses Excel to manage information can benefit from this trick. Whether you’re a financial analyst sifting through quarterly earnings, a marketer analyzing customer data, or a student organizing research, zebra striping can make your work easier to navigate and interpret. Plus, it’s a simple way to add a professional touch to your spreadsheets.

How to Highlight Every Other Row in Microsoft Excel

Before diving into the steps, it’s important to understand what we aim to achieve. Following this tutorial will result in every other row in your selected range being highlighted, making it easier to differentiate between rows when working with data.

Step 1: Select the Range of Cells

Select the cells you want to format by clicking and dragging your cursor across them.

Selecting the range is crucial because it tells Excel exactly where to apply the zebra striping. You can select a few rows, a whole column, or an entire worksheet if you wish.

Step 2: Access Conditional Formatting

Go to the ‘Home’ tab on the Excel ribbon and click on ‘Conditional Formatting.’

Conditional Formatting is a powerful feature in Excel that allows you to apply specific formatting to cells that meet certain criteria. This is where the magic happens.

Step 3: Create a New Rule

From the Conditional Formatting dropdown menu, select ‘New Rule.’

This step opens up a dialog box with various options for creating custom formatting rules.

Step 4: Use a Formula to Determine Formatting

Choose the option ‘Use a formula to determine which cells to format.’

Using a formula allows you to apply formatting to cells based on a logical condition, which in this case, will be whether a row is even or odd-numbered.

Step 5: Enter the Formula

In the formula field, enter =MOD(ROW(),2)=0.

This formula uses the MOD function to check if the row number is divisible by 2, which means it’s an even row. The ROW() function returns the current row number, and the MOD function returns the remainder after division by 2. If the result is 0, the row is even.

Step 6: Set the Format

Click on ‘Format,’ choose your desired formatting options, and then click ‘OK.’

Here, you can choose the background color, font style, and any other formatting you want to apply to the even rows.

Step 7: Apply the Rule

Click ‘OK’ again to apply the rule to the selected range.

Once you’ve set everything up, applying the rule will immediately highlight every other row in your selected range.


Enhanced ReadabilityBy highlighting every other row, it becomes easier to follow along the data without losing track, as the contrasting colors act as a guide for your eyes.
Professional AppearanceA well-organized spreadsheet with alternating row colors looks polished and can make a good impression on those who see your work.
CustomizableYou can use any color or format you prefer, allowing you to customize the look of your spreadsheet to fit your needs or preferences.


Limited to Conditional FormattingThis method relies on the Conditional Formatting feature, which may not be available in all versions of Excel or compatible with other spreadsheet software.
Potential to OvercomplicateOverusing this feature or choosing clashing colors can make your spreadsheet harder to read instead of easier.
Formula ComplexityFor Excel beginners, using formulas can be intimidating and may require a learning curve.

Additional Information

When it comes to organizing your data, the devil is in the details. Not only does highlighting every other row help with the visual aspect, but it also contributes to error reduction by minimizing misreads. Imagine trying to cross-check numbers across a sea of monochromatic cells—it’s a recipe for mistakes. Zebra striping helps mitigate that risk.

Additionally, there’s a level of versatility in using conditional formatting. It’s not just about colors; you could apply different text styles, add borders, or even use icon sets. Plus, you can combine this technique with other Excel features like sorting and filtering to really take control of your data presentation.

One thing to keep in mind is that if you have a table with alternating row colors and you sort the data, the colors will move with the rows. This is because conditional formatting is dynamic – it adapts to changes in the data.


  1. Select the range of cells you want to format.
  2. Go to the ‘Home’ tab and click on ‘Conditional Formatting.’
  3. Choose ‘New Rule’ from the dropdown menu.
  4. Select ‘Use a formula to determine which cells to format.’
  5. Enter the formula =MOD(ROW(),2)=0.
  6. Set your desired format and click ‘OK.’
  7. Apply the rule by clicking ‘OK’ again.

Frequently Asked Questions

What if I want to highlight odd rows instead?

Simply change the formula to =MOD(ROW(),2)=1 to target the odd rows.

Can I use this method to highlight columns instead of rows?

Yes, you can modify the formula to work with columns by using the COLUMN() function instead of ROW().

Will the formatting adjust if I add or remove rows?

Yes, conditional formatting is dynamic, so it will automatically update if the structure of your data changes.

Can I apply multiple formats to the same range?

Absolutely, you can layer conditional formatting rules to apply multiple formats based on different conditions.

Is it possible to clear the formatting later?

Yes, you can go back to ‘Conditional Formatting’ and choose ‘Clear Rules’ to remove the formatting.


Mastering the art of how to highlight every other row in Microsoft Excel can transform your spreadsheets from a mere jumble of numbers to a clearly defined data tapestry. Not only does it aid in data analysis, but it also adds a touch of professionalism to your work.

Remember, while the steps are relatively straightforward, the key to success lies in understanding the principles behind conditional formatting and the use of formulas. With a bit of practice, you’ll be zebra striping like a pro, bringing clarity and efficiency to your data management tasks.