Format an Excel table

Microsoft Office Excel provides numerous predefined table styles (or quick styles) that you can use to quickly format a table. If the predefined table styles don't meet your needs, you can create and apply a custom table style. Although you can delete only a custom table style, you can remove any table style so that it is no longer applied to the data.

You can further adjust the table formatting by choosing Quick Styles options for table elements, such as header and total rows, first and last columns, and banded rows and columns.

What do you want to do?


Choose a table style when you create a table

  1. On the worksheet, select a range of cells that you want to quickly format as a table.
  2. On the Home tab, in the Styles group, click Format as Table.

Excel Ribbon Image

  1. Under Light, Medium, or Dark, click the table style that you want to use.

 Tip   Custom table styles are available under Custom after you create one or more of them. For information on how to create a custom table style, see Create or delete a custom table style.

 Note   When you use Format as Table, Office Excel automatically inserts a table. If you don't want to work with your data in a table, you can convert the table to a regular range while keeping the table style formatting that you applied. For more information, see Convert an Excel table to a range of data.

Top of Page Top of Page

Apply a table style to an existing table

  1. On the worksheet, select the table to which you want to apply a table style.

 Tip   This displays the Table Tools, adding a Design tab.

  1. On the Design tab, in the Table Styles group, do one of the following:
  • Click the table style that you want to use.

 Tip   Use the arrow buttons to scroll through the available table styles.

  • Click the More button Button image, and then under Light, Medium, or Dark, click the table style that you want to use.

Excel Ribbon Image

 Note   When the Excel window is reduced in size, table styles will be available in the Table Quick Styles gallery in the Table Styles group.

 Tip   Custom table styles are available under Custom after you create one or more of them. For information on how to create a custom table style, see Create or delete a custom table style.

Top of Page Top of Page

Create or delete a custom table style

 Important   Custom table styles that you create are stored only in the current workbook and therefore are not available in other workbooks.

Create a custom table style

  1. To create a custom table style, do one of the following:
  • On the Home tab, in the Styles group, click Format as Table.

Excel Ribbon Image

  • Select an existing table to display the Table Tools, and then on the Design tab, in the Table Styles group, click the More button Button image.
  1. Click New Table Style.
  2. In the Name box, type a name for the new table style.
  3. In the Table Element box, do one of the following:
    • To format an element, click the element, and then click Format.
    • To remove existing formatting from an element, click the element, and then click Clear.
  4. On the Font, Border, and Fill tabs, select the formatting options that you want, and then click OK.

 Tip   Under Preview, you can see how the formatting changes that you made affect the table.

  1. Repeat steps 4 and 5 for all table elements that you want to customize.
  2. To use the new table style as the default table style in the current workbook, select the Set as default table quick style for this document check box.

Delete a custom table style

  1. On the Home tab, in the Styles group, click Format as Table.

Excel Ribbon Image

  1. Under Custom, right-click the table style that you want to delete, and then click Delete on the shortcut menu.

 Note   All tables in the current workbook that are using that table style will be displayed in the default table format.

Top of Page Top of Page

Remove a table style

  1. On the worksheet, select the table from which you want to remove the current table style.

 Tip   This displays the Table Tools, adding a Design tab.

  1. On the Design tab, in the Table Styles group, click the More buttonButton image.

Excel Ribbon Image

  1. Click Clear.

 Tip   The table will be displayed in the default table format.

 Note   Removing a table style does not remove the table. If you don't want to work with your data in a table, you can convert the table to a regular range. For more information, see Convert an Excel table to a range of data.

Top of Page Top of Page

Choose table style options to format the table elements

  1. On the worksheet, select the table to which you want to apply table style options.

 Tip   This displays the Table Tools, adding a Design tab.

  1. On the Design tab, in the Table Style Options group, do one of the following:
    • To turn the header row on or off, select or clear the Header Row check box.
    • To turn the totals row on or off, select or clear the Totals Row check box.
    • To display special formatting for the first column of the table, select the First Column check box.
    • To display special formatting for the last column of the table, select the Last Column check box.
    • To display odd and even rows differently for ease of reading, select the Banded Rows check box.
    • To display odd and even columns differently for ease of reading, select the Banded Columns check box.

Excel Ribbon Image

Top of Page Top of Page

 
 
Applies to:
Excel 2007