Contents
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:
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:
- Select the cells and press “Format as Table” in the “Home” tab of the ribbon
- 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.
- 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.
- 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.
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.
- 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…”.
- 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.
- 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.
- Press “OK”
- Tick “Set as default table style for this document” and press “OK”
- 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.
- Enjoy your custom zebra stipes Excel style
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:
- Select the “Table Design” tab in the ribbon and press “Convert to Range”
- Press “Yes” to the conversion prompt
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.
