Excel Class NYC Custom Table Styles Free Tutorial

One of the many terrific new features in Excel 2007 (and 2010) is the ability to create a table. Microsoft took a feature from 2003 called autoformat and improved it dramatically.

Microsoft Excel Tables allow you to:

• Apply alternate row shading where inserting or deleting a row adjusts the shading for you
• Freeze column headings effortlessly
• Applies data filters
• Offers a total row option
• Provides the ability to create new custom table styles (a style is a collection of formatting instructions)

Our introduction and advanced classes both cover the first four bullets and students generally immediately love this feature. This free Excel tutorial provides additional training on how to create a custom table style. If you search the Microsoft Excel help system this information is difficult to find, if it exists at all (the author couldn’t locate it effortlessly).

To follow along with this tutorial, you can download a workbook to practice custom table styles here.

In order to create an Excel custom table style you first need to format data as a table. To format your data as a table, click any single cell inside a contiguous range of data. Then, on the Home tab of the ribbon choose the format as table command, then pick any of the presets.

Excel prompts you to convert the highlighted data to a table format and you should respond by clicking the OK button.

Now you can create a custom table style with the formatting of your chosing. To create a custom table style, on the contextual Design tab of the ribbon, in the Table Styles section, click the second down triangle to display more table style options.

At the bottom of the dialog, locate the New Table Style command and click it.

Excel presents the new table style dialog shown below

In the name box, you should type a descriptive name for your table style, like Glenn’s Sales Report

In the Table Element listbox, there are many table elements to select from. A brief description of each element follows:

Whole Table: Formatting that affects the entire table, like the overall font, font size, font color, and perhaps an outer border or fill color

First Column Stripe: If you choose to vertically stripe the bands of your report, the first column stripe format

Second Column Stripe: If you choose to vertically alternate the striping of bands in your report, this is the format of the second column stripe

First Row Stripe: If you choose to horizontally stripe the bands of your report, the first row stripe format

Second Row Stripe: If you choose to horizontally alternate the striping of bands in your report, this is the format of the second row stripe

Last Column: If your report includes a totals column as the last column, you might choose to format it differently

First Column: If your report includes a column that shouldn’t be striped, this formats that column differently than the striped columns

Header Row: Your report will typically include a header row whose format is different from the rest of the report

Total Row If your report includes a total row at the bottom, it will typically have a different format to help make it stand out from the data

First Header Cell

Last Header Cell

First Total Cell

Last Total Cell

In this example, we want the heading row, total row and first/second stripe rows, but with 3 rows between stripes.

In the Table Element list box, click the Whole Table, then click the Format button.

In the Format Cells dialog, click the border tab, click the medium solid line, then click the outline option and click the OK button. Note how the Table Style dialog bolds the Whole Table text to indicate that formatting is set for that table element.

In the Table Style dialog, click the Header Row element, then click the format button. In the Format Cells dialog click the Fill tab at the top, then choose a light fill color.

Click the OK button to return to the table style dialog.

Click the Table Element First Row Stripe and change the stripe from 1 to 3, then click the format button

In the Format Cells dialog, on the border tab, apply a medium thick solid black border to the bottom edge of the 3rd row stripe and click the OK button.

Finally, for this table style, the last format is to the second row stripe. In the Table Elements list box, click the Second Row Stripe. Change the stripe size from 1 to 3. Then click the format button. In the Format Cells dialog, on the Fill tab, choose a light fill color different than the heading row fill color. On the border tab apply a medium thick solid black border to the bottom edgeof the alternating 3rd row stripe. Click the OK button to return to the Table Style dialog.

To finish the style, click the OK button to close the Table Style dialog.

Finally, to apply your custom table style, click any cell in the table. On the Design tab of the ribbon, click the more down triangle and at the top of the list, click on your custom table style (named Glenn’s Sales Report).

Evaluating your report, you notice the totals row wasn’t formatted differently and it doesn’t stand out. To modify your table style, on the Design tab of the ribbon, click the down triangle to show more styles, point to your custom style, right click it and choose Modify.

Select the Total Row in the Table Element List box, then click the format button. In the Format Cells dialog, apply a top and bottom border; In the Format Cells dialog, on the Fill tab, choose a light fill color. Click the OK button twice to close all dialogs and note the results in the Excel file.

Note: If you apply any ‘custom’ formatting from the Home tab of the ribbon, your formatting overrides the table style formatting.

If you have any questions about this tutorial, you’re encouraged to email us.

To learn more about Excel, investigate our Introduction to Excel Class or Advanced Excel Class.