How to Format an Excel table?

  • Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011.

This post will show you how to format an Excel table for both Windows and Excel for the Web to achieve your ideal presentation of your data.

Windows

Excel is jam-packed full of preformatted table styles that you can utilise to effortlessly format an Excel table. However, you can design and implement a custom table style if the pre-existing table styles are unsuitable for your requirements. Even though you can erase solely custom table styles, you can delete any preformatted table style to guarantee that it stops being associated with a table.

Example of data formatted as an Excel table
© Microsoft, 2020.

Additionally, you can amend the table further by picking Quick Styles options for table features, like Header and Total Rows, First and Last Columns, Banded Rows and Columns, alongside Auto Filtering.

Note: The screenshots in this post were taken in Excel 2016. If you have another version, your view may be slightly different, but unless otherwise stated, the functionality still operates in exactly the same way.

Pick a table style

Once you have a data range that is unformatted as a table, Excel will immediately update it to a table after you choose a table style. You can even alter the format for an established table by picking an alternative format.

  1. Choose any cell inside the table, or range of cells you prefer to format as a table.
  2. On the Home tab, press Format as Table.
Excel Style Gallery selections for Format as Table
  1. Select your desired table style to use.

Notes: 

  • Auto Preview – Excel will instantly format your data range or table with a preview of any style you pick, but will merely set that style if you press Enter or click with the mouse to verify it. You can scroll through the table formats with the mouse or your keyboard’s arrow keys.
  • Once you use Format as Table, Excel promptly transforms your data range to a table. Although, you have the choice to revert the table back to a regular range if you prefer not to work with your data among a table. This has the benefit of retaining the table style formatting that you defined. For more details, see Convert an Excel table to a range of data.

Create or delete a custom table style

Important: 

  • When created, custom table styles are accessible from the Table Styles gallery below the Custom section.
  • Custom table styles are exclusively stored in the present workbook, and are unavailable in other workbooks.

Create a custom table style

  1. Choose any cell in your preferred table to use to design a custom style.
  2. On the Home tab, select Format as Table, or expand the Table Styles gallery from the Table Tools > Design tab (the Table tab on a Mac).
  3. Press New Table Style, which will reveal the New Table Style dialogue.New Table Style dialog options for applying custom styles to a table
  4. In the Name box, enter a name for the new table style.
  5. In the Table Element box, do one of the following:
    • To format an element, select the element, then pick Format. Then, choose your desired formatting options from the FontBorder or Fill tabs.
    • To delete current formatting from an element, pick the element, and then select Clear.
  6. Beneath Preview, you can witness how the formatting modifies that you applied amend the table.
  7. To employ the new table style as the standard table style in the existing workbook, click the Set as default table style for this document checkbox.

Delete a custom table style

  1. Choose any cell in your sought table to erase the custom table style.
  2. On the Home tab, select Format as Table, or expand the Table Styles gallery from the Table Tools > Design tab (the Table tab on a Mac).
  3. Below Custom, right-click your target table style to erase, and then choose 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.

Remove a table style

  1. Click any cell in the table from which you want to remove the current table style.
  2. On the Home tab, select Format as Table, or expand the Table Styles gallery from the Table Tools > Design tab (the Table tab on a Mac).
  3. Choose Clear. The table will be presented in the basic table format.

Note: Deleting a table style does not erase the table. If you want to work with your data out of a table then you can change the table to a regular range. For further details, see Convert an Excel table to a range of data.

Choose table style options to format the table elements

There are multiple table style options that can be switched on and off. To set any of these options:

  1. Pick any cell in the table.
  2. Navigate to Table Tools > Design, or the Table tab on a Mac. Then, in the Table Style Options group, check or uncheck any of the following:Image of the Table Tools option on the Ribbon when a table cell is selected
    • Header Row – Add or delete formatting from the first row in the table.
    • Total Row – Easily add SUBTOTAL functions such as SUM, AVERAGE, COUNT, MIN/MAX to your table from a drop-down menu. SUBTOTAL functions enable you to encompass or omit hidden rows in calculations.
    • First Column – Set or extract formatting from the first column in the table.
    • Last Column – Transfer or erase formatting from the last column in the table.
    • Banded Rows – Show odd and even rows with alternating shading for ease of reading.
    • Banded Columns – Present odd and even columns with alternating shading for simple reading.
    • Filter Button – Turn AutoFilter on and off.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

Other Similar Topics of Tables

A Snapshot of Excel tables

Video: Create an Excel table

Total the data in an Excel table

Resize a table by adding or removing rows and columns

Excel table compatibility issues

Export an Excel table to SharePoint

Convert an Excel table to a range

Filter data in a range or table

Leave a Reply

%d bloggers like this: