• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

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

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Conditional Formatting Zebra Stripes

You are here: Home / Excel / Excel Conditional Formatting Zebra Stripes
Excel Conditional Formatting Zebra Stripes
November 2, 2011 by Mynda Treacy

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

  4. Select 'Use a formula to determine which cells to format' > enter your formula in the 'Edit the Rule Description' field.
  5.  
    How to create Excel Conditional Formatting using a formula

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

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

    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

More Excel Posts

excel templates

Where to Find Free Excel Templates

Where to find free Excel templates and how to create your own Excel templates. Using templates saves time and effort.
Easily Remove Password Protection from Excel Files

Easily Remove Excel Password Protection

How to remove Excel password protection when you’ve forgotten the password. Works for sheets, workbooks and read only files.
Import data from a picture to Excel

Import Data from a Picture to Excel

Import data from a picture to Excel. Works with pictures from a file or the clipboard and loads it to the spreadsheet.
excel online

5 Excel Online Features Better than Desktop

5 Excel Online Features Better than Desktop including searchable data validation, track changes, single line ribbon and more.

10 Common Excel Mistakes to Avoid

10 common Excel mistakes to avoid, including merge cells, external links, formatting entire rows/columns and more.
new Excel features

Cool New Features in Excel for Microsoft 365

Cool New Features in Excel for Microsoft 365 including the navigation pane, smooth scroling, unhide multiple sheets and more.
dynamic dependent data validation

Dynamic Dependent Data Validation

Dynamic Dependent Data Validation with dynamic array formulas like FILTER make it quick and easy to set up.
QAT

Excel Quick Access Toolbar

The Excel Quick Access Toolbar is not only a handy for your mouse, but it also enables some super easy keyboard shortcuts.

Share and Collaborate in Excel

Share and Collaborate in Excel just like Google Sheets! Show changes, custom views, threaded comments with @ mentions and more.
Workbook Protection

Excel Workbook Protection

Excel Workbook protection can prevent your users from breaking your reports while still allowing interaction with Slicers and refreshing.




Category: Excel
Previous Post:top 5 mistakes with excel reportsTop 5 Mistakes With Reports in Excel
Next Post:Excel Advanced Filter Unique RecordsExcel Advanced Filter Unique Records

Reader Interactions

Comments

  1. Lory Howell

    August 2, 2014 at 1:00 am

    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

      August 2, 2014 at 2:29 am

      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

    January 9, 2014 at 5:24 am

    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

      January 9, 2014 at 9:59 pm

      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

    January 9, 2014 at 5:15 am

    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

      January 9, 2014 at 9:58 pm

      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

    October 28, 2013 at 4:43 am

    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

      October 28, 2013 at 8:45 pm

      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.

  5. Noitidart

    August 27, 2013 at 8:49 am

    A special thank you for the multiple zebra striping!!

    • Mynda Treacy

      August 27, 2013 at 4:36 pm

      You’re welcome, Noitidart 🙂

  6. Michel

    January 8, 2013 at 9:03 pm

    Hello
    It’s not working..!
    We receive always a error (wrong formula)
    What can we do?

    • Mynda Treacy

      January 8, 2013 at 9:53 pm

      Hi Michel,

      I’m not sure why it’s not working for you. You can send me your file via the help desk and I can take a look at it for you.

      Kind regards,

      Mynda.

      • Michel

        January 9, 2013 at 5:17 pm

        Hi Mynda

        Thank you for your support…!

        Kind regards from Switzerland

        Michel

        • Mynda Treacy

          January 9, 2013 at 8:37 pm

          You’re welcome, Michel 🙂

  7. Trudy Rutland

    January 1, 2013 at 2:33 am

    Thanks for the easy instructions to create Zebra lines in Excel.

    • Mynda Treacy

      January 1, 2013 at 9:00 am

      You’re welcome, Trudy 🙂

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.