- 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.
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.
- If you prefer to at any time then you can disable the header row in a table. For further details, read Turn Excel table headers on or off.
- Banded rows – Alternate shading or banding in rows aids with clearer distinctions amongst the data.
- 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.
- 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.
- Sizing handle – A sizing handle by the lower-right corner of the table enables you to drag the table to your ideal size.
- For alternative ways to enlarge or shrink a table, see Resize a table by adding rows and columns.
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:
- Choose the cell or the range within the data.
- Pick Home > Format as Table.
- Confirm a table style.
- 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.