Overview of Excel tables

To make managing and analyzing a group of related data easier, you can turn a range of cells into a Microsoft Office Excel table (previously known as an Excel list). A table typically contains related data in a series of worksheet rows and columns that have been formatted as a table. By using the table features, you can then manage the data in the table rows and columns independently from the data in other rows and columns on the worksheet.

 NOTE   Excel tables should not be confused with the data tables that are part of a suite of what-if analysis commands. For more information about data tables, see Calculate multiple results with a data table.

In this article

·         Learn about the elements of an Excel table

·         Managing data in an Excel table

Learn about the elements of an Excel table

A table can include the following elements:

·         Header row    By default, a table has a header row. Every table column has filtering enabled in the header row so that you can filter or sort your table data quickly.

·         Banded rows    By default, alternate shading or banding has been applied to the rows in a table to better distinguish the data.

·         Calculated columns    By entering a formula in one cell in a table column, you can create a calculated column in which that formula is instantly applied to all other cells in that table column.

·         Total row    You can add a total row to your table that provides access to summary functions (such as theAVERAGE, COUNT, or SUM function). A drop-down list appears in each total row cell so that you can quickly calculate the totals that you want.

·         Sizing handle    A sizing handle in the lower-right corner of the table allows you to drag the table to the size that you want.

 TOP OF PAGE

Managing data in an Excel table

You can use one table to manage your data, but if you want to manage several groups of data, you can insert more than one table in the same worksheet.

If you have access to and authoring permission on a Microsoft Windows SharePoint Services site, you can use it to share a table with other users. By exporting table data to a SharePoint list, other people can view, edit, and update the table data in the SharePoint list. You can create a one-way connection to the SharePoint list so that you can refresh the table data on the worksheet to incorporate changes that are made to the data in the SharePoint list. You can no longer update a SharePoint list with changes that you make to the table data in Excel. After exporting the table data to a SharePoint list, you can open a SharePoint list in Excel as read-only  — any changes that you want to make can be made only to the data on the SharePoint site.

 NOTE   Because table functionality is not supported in shared workbooks, you cannot create a table in a shared workbook.

Table features that you can use to manage table data

·         Sorting and filtering    Filter drop-down lists are automatically added in the header row of a table. You can sort tables in ascending or descending order or by color, or you can create a custom sort order. You can filter tables to show only the data that meets the criteria that you specify, or you can filter by color. For more information on how to filter or sort data, see Filter data or Sort data.

·         Formatting table data    You can quickly format table data by applying a predefined or custom table style. You can also choose Table Styles options to display a table with or without a header or a totals row, to apply row or column banding to make a table easier to read, or to distinguish between the first or last columns and other columns in the table. For more information on how to format table data, see Format an Excel table.

·         Inserting and deleting table rows and columns    You can use one of several ways to add rows and columns to a table. You can quickly add a blank row at the end of the table, include adjacent worksheet rows or worksheet columns in the table, or insert table rows and table columns anywhere that you want. You can delete rows and columns as needed. You can also quickly remove rows that contain duplicate data from a table. For more information about adding and deleting table rows and columns, see Add or remove Excel table rows and columns.

·         Using a calculated column    To use a single formula that adjusts for each row in a table, you can create a calculated column. A calculated column automatically expands to include additional rows so that the formula is immediately extended to those rows. For more information on how to create a calculated column, see Create, edit, or remove a calculated column in an Excel table.

·         Displaying and calculating table data totals    You can quickly total the data in a table by displaying a totals row at the end of the table and then using the functions that are provided in drop-down lists for each totals row cell. For more information on how to display and calculate table data totals, see Total the data in an Excel table.

·         Using structured references    Instead of using cell references, such as A1 and R1C1, you can use structured references that reference table names in a formula.

·         Ensuring data integrity     For tables that are not linked to SharePoint lists, you can use the built-in data validation features in Excel. For example, you may choose to allow only numbers or dates in a column of a table. For more information on how to ensure data integrity, see Prevent invalid data entry on a worksheet.

·         Exporting to a SharePoint list    You can export a table to a SharePoint list so that other people can view, edit, and update the table data.