A Snapshot of Excel Tables

An Excel table with revenue audit data for a small business.

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

This blog post will explore the aspects of Excel tables and why they are recommended for simplifying the formatting and analysis of a collection of linked data.

To begin with, changing a cell range into an Excel table (formerly known as an Excel list) makes it simpler to organising and examining an array of similar data.

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

Important Notes about Excel Tables: 

  • Excel tables must not be mistaken for the data tables that are affiliated with a group of what-if analysis commands. For further details about data tables, read Calculate multiple results with a data table.
  • Another vital note is that you are unable to design or add tables in a shared workbook.

Learn about the elements of Excel tables

A table can contain these specific elements:

  • Header row – Automatically, a table includes a header row. Each table column by default has filtering activated in the header row to ensure that you can filter or sort your table data swiftly. For further information, see Filter data or Sort data.
Sort or apply a filter to the table
© Microsoft, 2020.

  • Banded rows – Alternate shading or banding in rows aids with clearer distinctions amongst the data.
Excel table with header data, but not selected with the My table has headers option, so Excel added default header names like Column1, Column2.
© Microsoft, 2020.
  • Calculated columns – By typing a formula in one cell in a table column, you can generate a calculated column. This will cause that formula to be imminently relayed to each of the remaining cells in that table column. For further details, see Use calculated columns in an Excel table.
Add a single formula in a table cell that will autocomplete to create a calculated column
© Microsoft, 2020.
  • Total row – After you insert a total row to a table, Excel offers you an AutoSum drop-down list to pick from functions including SUM, AVERAGE, and so forth. Once you choose one of these options, the table will instantly transform them to a SUBTOTAL function. In effect, it will skip rows that have been concealed with an automatic filter. If you prefer to take account of hidden rows in your calculations, however, you can alter the SUBTOTAL function arguments. To learn more about how to do this, also see Total the data in an Excel table.
An Excel table with revenue audit data for a small business.
© Microsoft, 2020.
  • Sizing handle – A sizing handle by the lower-right corner of the table enables you to drag the table to your ideal size.
Drag the resize handle to resize your table
© Microsoft, 2020.

Produce an Excel table

You can make however many tables as you like in a spreadsheet.

To rapidly design an Excel table, follow this procedure:

  1. Choose the cell or the range within the data.
  2. Pick Home > Format as Table.
  3. Confirm a table style.
  4. In the Format as Table dialogue box, click the checkbox beside My table as headers. Only select this checkbox if you are seeking the initial row of the range to occupy the header row. After this, press OK.

You can even watch a video on creating a table in Excel.

Working productively with your Excel table data

Excel contains some built-in features that allow you to work efficiently with your table data:

  • Using structured references – Rather than using cell references, like A1 and R1C1, you can employ structured references that reference table names inside a formula. For further details, check Using structured references with Excel tables.
  • Ensuring data integrity – You can use the pre-existing data validation feature in Excel. For instance, you might decide to solely permit numbers or dates in a column of a table. To learn more about how to guarantee data integrity, see Apply data validation to cells.

Export an Excel table to a SharePoint site

If you have authoring access to a SharePoint site, you can apply it to export an Excel table to a SharePoint list. Effectively, other people can see, modify, and revise the table data within the SharePoint list. You can design a one-way connection to the SharePoint list to enable you to refresh the table data on the worksheet to consider amendments that are set to the data amongst the SharePoint list. For further information, see Export an Excel table to SharePoint.

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 (Excel Tables)

Format an Excel table

Excel table compatibility issues

Other Links

Back to Software Bytes – Official Website

Back to Excel Tutorial

Leave a Reply

%d bloggers like this: