Featured - How to Apply Shading to Alternate Rows in Excel

It’s an old trick at this point, but applying shading (zebra stripes) to alternative rows in Excel makes your sheet easier to read. The effect, also known as banded row, allows your eyes to keep their place more easily when you’re scanning a spreadsheet.

Even better, it’s quick and simple to achieve this effect. Once you have a table format, you can get Excel to auto color cells with handy table color schemes. 

The difficulty, then comes in knowing where to look and how to format cells as a table in the first place. Here’s how you can apply and customize table formatting to form alternating rows in Excel:

Advertisement

How to Format a Range of Cells As a Table

To get started shading your worksheet, you’ll want to highlight the rows to format as a table and follow the steps below:

  1. Select the cells and press “Format as Table” in the “Home” tab of the ribbon
     

     

    Windows 10 - Excel - Select the Range - Home - Format as Table

  2. Select a color scheme from the dropdown menu
     

    In the resulting pop-up menu, you want to right-click on a theme option and press “Apply and Clear Formatting”. This will remove any current formatting before applying the table style. If you want Excel to keep your formatting before it auto colors cells, you can select “Apply (and Maintain Formatting)” instead.

    Windows 10 - Excel - Select the Range - Home - Format as Table - Apply and Clear Formatting

  3. Press “OK” to the “Create Table” prompt
     

    If needed, you can modify the table range to include more or fewer cells. You should also tick “My table has headers” if it has them.

    Windows 10 - Excel - Select the Range - Home - Format as Table - Apply and Clear Formatting - Check the Range - Ok

  4. View your zebra striped Excel document
     

    Excel should auto color cells in the zebra stripes pattern, with your header rows highlighted in the color of your choice. The formatting will also apply to any new rows you add to the table.

    Windows 10 - Excel - Select the Range - Home - Format as Table - Apply and Clear Formatting - Check the Range - Result

How to Edit Your Zebra Stripes’ Excel Style

If you don’t like the color you chose initially, it is possible to change it. As well as selecting a different color from the Quick Styles list, you can add your own custom colors.

  1. Duplicate an existing style
     

    You can’t directly edit the default banded row styles, so it’s necessary to duplicate one first. Switch to the “Table Design” tab in your ribbon, then click “Quick Styles”. With the style window in focus, right-click your current style and choose “Duplicate…”.

    Windows 10 - Excel - Table design - Quick Styles - Right Click Selected table - Duplicate

  2. Name your zebra stripes Excel style and format the first row stripe
     

    To format the First Row Stripe, just select it from the list and press the “Format” button below it.

    Windows 10 - Excel - Table Design - Duplicate - Modify Table Style - Name - First Row Stripe - Format

  3. Switch to the “Fill” tab and select a color
     

    Click “Fill” in your Format Cells window and either choose from the list of preset colors or click “More Colors…” to define your shading for the worksheet.

    Windows 10 - Excel - Table Design - Duplicate - Modify Table Style - Name - First Row Stripe - Format - Fill - Select Color

  4. Press “OK”
     

    Windows 10 - Excel - Table Design - Duplicate - Modify Table Style - Name - First Row Stripe - Format - Fill - Select Color - Ok

  5. Tick “Set as default table style for this document” and press “OK”
     

    Windows 10 - Excel - Table Design - Duplicate - Modify Table Style - Set as Default

  6. Select your custom style from the Quick Styles menu
     

    Go back into the “Table Design” tab of the Excel ribbon and click “Quick Styles”, then select your style from the list with your cells highlighted.

    Windows 10 - Excel - Table Design - Quick Styles - Select Custom

  7. Enjoy your custom zebra stipes Excel style
     

    Windows 10 - Excel - Table Design - Quick Styles - Select Custom - Result

How to Convert a Table Back into a Range

Generally, it’s recommended to keep your Excel data as a table as it allows your to sort, format, and build charts from it more easily. If you don’t want this functionality, however, it’s possible to convert it back into a range but keep the banded row formatting. Here’s how:

  1. Select the “Table Design” tab in the ribbon and press “Convert to Range”
     

    Windows 10 - Excel - Table Design - Convert to Range

  2. Press “Yes” to the conversion prompt
     

    Windows 10 - Excel - Table Design - Convert to Range - Confirm

There you have it. You now know how to create zebra stripes in Excel. While you’re here, though, you may be interested to learn how you can insert a PDF into your spreadsheet or remove duplicate rows from your table.

Advertisement