Excel Logo

Applying color to alternate rows makes your spreadsheet data easier to read. In Excel, this is quick and simple to apply by formatting your data as a Table. The row shading is then automatically applied to new rows added to the table.

How to Format Your Data As a Table

Select the range you want to format as a table. Next, click the “Format as Table” button within the “Home” tab and then select the table style you want to use.

Format your data range as a table

If your data range already contains some formatting, it can be a good idea to right-click on the table style and select the “Apply and Clear Formatting” link. This will remove any current formatting before applying the table style.

Apply and Clear Formatting when applying tables

You will then be prompted for the range to format and whether it has headers. Check that this range is correct, and change it if necessary. Click the “OK” button to proceed.

Select the range to format as a table

The table is applied along with shading on alternate rows. This formatting will automatically be applied when new rows are added to the table.

Alternate shading applied to a range

How to Edit the Row Shading Color

If you aren’t happy with the color used for the row shading, you can change it. You cannot directly edit the existing Excel table styles, but you can create your own or duplicate an existing style and edit that.

Let’s take a look at duplicating and changing the table style we’ve applied to this data range. Begin by clicking on a cell within the table.

From the “Table Design” tab (last tab on the ribbon), right-click the style in the “Table Styles” gallery and then click “Duplicate.”

Duplicate an existing table style

Enter a name for your new table style.

To edit the row shading, select “First Row Stripe” and then click the “Format” button.

Editing the table style

Click the “Fill” tab. Select the color you want to use. The “More Colors” button will provide more specific color options.

Click the “OK” button at the bottom of the window to proceed.

Change the fill colour of a table row

Back in the “Modify Table Style” dialog window, you can make your style the default for the spreadsheet by checking the “Set As Default Style for This Document” box. Click the “OK” button to save your changes.

Apply the edited style

The duplicated style is created, but it is not applied immediately. Click the “More” button to open the table style gallery.

Open the table styles gallery

Select your custom style from the list provided.

Select your table style from the gallery

Convert Your Table Back to a Range

There are many additional benefits to formatting your Excel data as a Table, so using one is encouraged. However, if you do not want this functionality, you can convert your table back to a range and still keep the alternate row shading.

Click a cell in your table, select the “Table Design” tab (last tab on the Ribbon), and then click “Convert to Range.”

Convert table to range

You will be asked whether you’re sure you want to do this. Click the “Yes” button.

Are you sure to convert to range

Without being a table, it will not automatically apply the shading to rows when they are added. But you can copy the formatting quickly using the Format Painter button, or a similar technique.

Profile Photo for Alan Murray Alan Murray
Alan Murray has worked as an Excel trainer and consultant for twenty years. On most days, he can be found teaching Excel in a classroom or seminar. Alan gets a buzz from helping people improve their productivity and working lives with Excel.
Read Full Bio »