Create an Excel Table With a Specific Style

When you create a named Excel table with the Table command on the Ribbon’s Insert tab, the table retains any formatting that it currently has, and the default Table Style is applied.

formatastable03

You can select a different Table Style from the Format tab, and clear out the old formatting, but that adds more steps to the process, and who has time for that?

Use a Specific Table Style

If you want to apply a specific table style when creating an Excel Table:

  1. Select a cell in the list of data that you prepared.
  2. On the Ribbon, click the Home tab.
  3. In the Styles group, click Format as Table
  4. Click on the Style that you want to use
    formatastable01
  5. In the Create Table dialog box, the range for your data should automatically appear, and the My table has headers option is checked. If necessary, you can adjust the range, and check box.
  6. Click OK to accept these settings.

A formatted Excel Table is created, with the selected Table Style. Any existing cell formatting (font, fill, and border) is retained.

formatastable03

Use a Specific Table Style and Clear the Formatting

If you want to apply a specific table style, and clear any existing cell formatting (font, fill, and border):

  1. Select a cell in the list of data that you prepared.
  2. On the Ribbon, click the Home tab.
  3. In the Styles group, click Format as Table
  4. Right-click on the Style that you want to use
  5. Click Apply and Clear Formatting

    formatastable02

  6. In the Create Table dialog box, the range for your data should automatically appear, and the My table has headers option is checked. If necessary, you can adjust the range, and check box.
  7. Click OK to accept these settings.

A formatted Excel Table is created, with the selected Table Style, and the existing font, fill, and border formatting removed. The number formatting is not affected.

formatastable04

Download the Sample File

To download the sample file, please visit my Contextures website: Creating an Excel Table

Watch the Video

To see the steps for creating an Excel table with a specific Table Style, please watch this short video tutorial.

___________

Create an Excel Table from a List

When you create a list in Excel, do you automatically convert that list to a formatted table?

exceltable01

If not, you’re missing out on one of Excel’s best features.

  • Formatted tables are easy to set up, and make it easy to sort, filter, format, and expand your list.
  • You can refer to the table’s fields in your formulas, and the reference will adjust automatically, if you add or remove data.

exceltable05

Set Up Your Data

There are a few things to check, before you turn your data list into a table. For example:

  • Add a heading in each data column
  • Leave blank rows and columns around the list
  • Don’t leave blank rows or columns within the list

exceltable02

Create the Table

Once the data is set up, select any cell in the list, and click the Table command on the Ribbon’s Insert tab. When the Create Table window opens, check that the range is correct, and check the box for “My table has headers”.

exceltable04

Name Your Table

After you create the table, change its name from the default “Table1” to something meaningful, such as “Orders”. This is especially important if you’re going to have more than one table in the workbook.

Just type over the old name in the Table Name box on the Ribbon’s Design tab.

exceltable07

More Table Tips

For more tips on working with tables, please visit the Excel Tables page on my Contextures website.

Watch the Video

If you haven’t made a formatted table before, you can watch this video, to see the steps.

__

The video is also on the Excel Tables page on my Contextures website.

__________________