My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

Excel Conditional Formatting Zebra Stripes

You are here: Home / Excel / Excel Conditional Formatting Zebra Stripes
Excel Conditional Formatting Zebra Stripes
by

It's ok, no zebras get hurt in this process, it's actually all built in to Excel's Conditional Formatting 🙂

Conditional Formatting - Shade Alternate Rows

Conditional Formatting Shaded Bands

To create this effect we need to create a new rule which uses a formula to determine which cells to format.

Conditional Formatting Using a Formula

  1. Select the cells you want to shade.
  2. On the Home tab of ribbon select Conditional Formatting > New Rule

    3.  
    How to create Excel Conditional Formatting using a formula

  3. Select 'Use a formula to determine which cells to format' > enter your formula in the 'Edit the Rule Description' field.

    4.  
    How to create Excel Conditional Formatting using a formula

    For alternating bands your formula is =MOD(ROW(),2)

  4. Click the Format button and from the Format Cells dialog box select the Fill tab > choose your weapon (colour, pattern, fill effect etc.):

    5.  

    How to create Excel Conditional Formatting using a formula

Conditional Formatting - Shade multiple rows

You can use a slight variation of the above formula to shade multiple rows like this:

Excel Conditional Formatting using a formula for multiple shaded bands

To achieve the above effect you use this formula:

Formula for multiple shaded bands

In this formula the 6 states how many rows in the set; 3 shaded 3 not shaded. And the 3 stipulates how many rows are shaded.

You can play around with these numbers to change the number of rows you want shaded and not shaded.

Conditional Formatting - Shade Alternate Rows in a Filtered Table

Excel Conditional Formatting shaded bands in a filtered table

When your data is filtered you need a slightly different formula for your shaded bands to change as your list is filtered. Otherwise you'll end up with sporadic groups of shaded and unshaded bands.

The formula you want to use is:

Formula for Excel Conditional Formatting shaded bands in a filtered table

Where cell M4 is the first cell in the header row of your table.

One alternative to the above examples (excluding the Filtered Table) is to simply use Excel's Table tool which automatically inserts shaded bands.

If you liked this trick please share it with your friends on Facebook.

Excel Conditional Formatting Zebra Stripes

Microsoft MVP logo

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

More Excel Posts

8 Excel in-built AI Tools
Clean, analyze and visualize data with these Official Excel AI tools by Microsoft. Including formula writing, PivotTables, Charts and more.
excel date and time
Everything you need to know about Excel date and time. Includes comprehensive workbook with every date and time function, plus PDF version.
10 mistakes to avoid when using excel formatting
These are the Excel formatting habits that drive me crazy and what you should do instead to ensure you use formatting effectively.
linear regression
Excel linear regression is easy with the built-in tools. Use charts to plot linear regression or use the Data Analysis Toolpak.
speed up slow excel files
How to improve Excel performance and the various causes of slow Excel files so you can speed up Excel and avoid problems in future.
Securely Share Excel Files
Securely share Excel files stored locally, on OneDrive or SharePoint. Prevent editing or downloading, specify who can open and edit the file.
excel check boxes
Excel check boxes are interactive elements you can link to formulas, charts, conditional formatting and more.
tips for working in multiple excel files
Awesome tips for navigating, arranging and working in multiple Excel files. Guaranteed to streamline your workflow and increase productivity.
chatgpt for excel
Using ChatGPT for Excel can be hit and miss. Learn the best uses for ChatGPT to make your Excel life easier and what to avoid using it for.
excel templates
Where to find free Excel templates and how to create your own Excel templates. Using templates saves time and effort.


Category:

Reader Interactions

Comments

  1. Lory Howell

    How do I alternate coloring for alternating numbers of rows based on the contents in Column A?
    I have a different amount of rows for each inspection report and I want to look at the next number and if it is the same then include it in the current formatting, if it’s different I want to highlight the row in yellow – alternating for every other inspection report number.
    inspection numbers date description
    86300501 07/14/14 yada, yada, yada
    86300501 07/14/14 yada, yada, yada
    599000102 08/02/13 more yada, yada, yada
    599000102 08/02/13 more yada, yada, yada
    599000102 08/02/13 more yada, yada, yada
    599000102 08/02/13 more yada, yada, yada
    CV4393015 05/21/14
    CV4393015 05/21/14
    Where the first two rows have yellow highlighting, but the next 4 don’t, then the last two have highlighting. Is that too complex for a single formula? Thanks!

    • Catalin Bombea

      Hi Lory,
      It’s not complicated, but you have to use a helper column with a formula to decide if that row should be colored.
      Here is a file uploaded on our OneDrive folder.
      Catalin

  2. Michael

    Hi all,

    if you do get the error “wrong formula” even when you have entered everything “correct”, please try to replace the “,” with a “;”.

    Somehow an US english Excel 2010 (at least in Switzerland) wants to have a “;” instead of a “,” within the MOD command.

    • Mynda Treacy

      Hi Michael,

      Thanks for sharing. I know there are many differences with international versions of Excel which most of us with English versions are unaware of.

      Cheers,

      Mynda.

  3. Michael

    Hello Mynda,

    this is Michael (not Michel) from Switzerland. Did you ever solve the Problem with Michel’s Excel sheet? I am asking as I get here the same error as he does: Wrong formula. I did enter exactly the recommended string. I am using a US Excel 2010.

    • Mynda Treacy

      Hi Michael,

      The problem with Michel’s formula was that he still had the double quotes around it. You just enter the formula into the Conditional Format manager like you would into a cell, i.e. without double quotes.

      Kind regards,

      Mynda.

  4. Fred

    Thanks for the info on zebra stripes. Does this technique differ in a pivot table? Once you set-up the formula in conditional formatting, do you copy to all the cells in the pivot table that you want to format? Thanks!

    • Mynda Treacy

      Hi Fred,

      I would use the Design tools for PivotTables to apply the zebra striping automatically. To locate them simply select any cell in the PivotTable, this should reveal the PivotTable Tools menu on the ribbon. From here you will find the PivotTable styles group where you can choose from a series of predefined stripes or create your own custom style.

      Kind regards,

      Mynda.