Overview of Excel Table – The Hidden Treasure

Excel Table is probably one of the most underrated features in Excel. It is super easy to understand and have a lot of merits over regular tabular data in Excel.

It makes it easy for a user to manage data if it is in a tabular format. Just by converting a data range into a table, you are able to use a variety of features that will make your work super easy (I cover these features in detail below in this article).

Creating an Excel Table

If you have the data in the tabular format in a worksheet, here are the steps to convert it into an Excel table

  • Select the entire data set.
  • Go to Home –> Format as Table.

Insert Excel Table

  • Select any one of the pre-defined formats.

Format as Excel Table

  • In the Format as Table dialogue box, ensure that the right range is selected. If your table has headers, keep the check box checked. In case your data does not have headers, excel automatically inserts headers with generic name Column 1, Column 2, and so on..

Excel Table - Format as Table Excel Dialogue Box

  • Click OK.

TIP: A much faster way to do this is to select any cell within the data set and use the keyboard shortcutControl + T. It would directly open the Format as Table dialogue box.

When your tabular data is converted into Excel Table, your data would look as shown below:
Excel Table Example

Note that some changes automatically happen with your data:

  • The data now has banded rows
  • The header is shaded in a different color
  • Data Filters are enabled (note the arrow pointing down at the right of the headers).

Note that when you convert tabular data into an Excel table, only the design changes. Nothing happens to the data.

Converting Excel Table into Regular Tabular Data

If you want to convert the Excel Table back to the normal tabular range, right-click anywhere on the table, go to Table and select Convert to Range.
Excel Table - Convert to Range Excel

Note that when you convert the Excel Table to a range, it no longer has the properties of an Excel Table (discussed below), however, it retains the formatting.

Useful Excel Table Features

Now that you have learned how to create a table, here are some features of Excel Table that make it useful:

  • An Excel Table automatically creates headers that have the option to sort or filter.

Excel Table Sort and Filter

  • If the Excel Table is long and stretches beyond the visible screen, the headers remain at the top when you scroll down. This would work only if you have selected the table and scrolling down.

Excel Table Headers Scroll

  • If you type anything in the cells adjacent to the Excel table, it automatically expands the selection to include this data. If not required, this can be switched off.

Excel Table Adds Record Automatically

  • If you are using the data from the table in any calculation, and if there are any additions to it (say you add one row of data), it automatically gets figured into the calculations

Excel Table Update Calculations

Excel Table Designing

Excel Table is cool and fashionable. You can dress it the way you want. When you click on any cell within the Excel Table, an additional tab TABLE TOOLS DESIGN appears in the Excel Ribbon area.
Excel Table Design Tab

When you click on the Design tab, there are several options that you can access:

  • Properties: Here you can change the existing name of the Table (yes! every table gets a name and it is very useful for referencing, as you will see later in this article), or you can re-size the table.

Excel Table Name

  • Tools: This has four options:
    • Summarize with Pivot Table: This simply creates a pivot table using the data in the Excel Table.
    • Remove Duplicates: This can be used to remove duplicates.
    • Convert to Range: To convert an Excel Table into regular range.
    • Insert Slicer (in Excel 2013 only): This works exactly like Pivot Table slicers, and could be helpful in filtering data using a single click.

Excel Table Tools

  • External Table Data: This section can be used to export data or refresh data.

Excel Table External Data

  • Table Style Options: This is the fashion section where you dress up your table:
    • Header Row: If unchecked, it removes the header from the table.
    • Total Row: If checked, it inserts a total row at the end.
    • Banded Rows: If unchecked, it removes the alternate band in rows.
    • Banded Columns: If checked, it applies alternate band in columns.
    • First Column: If checked, it makes the first column font bold.
    • Last Column: If checked, it makes the last column font bold.
    • Filter Button: If unchecked, it removes the filter drop down from headers.

Excel Table Style Options

  • Table Styles: Give you multiple options to change the styling of the tables. You can also create your own table style by selecting New Table Style option

Excel Table Style Colors

Structured Referencing in Excel Tables

Another benefit of using Excel Tables is the ease to reference data once a table has been created. There is a specific format that you can use to refer to data in an Excel Table:

Excel Table Structured Reference

If you have given a specific name to the Excel Table, then that name would be used instead of Table1.

This structured referencing has many benefits:

  • You can refer to data using names instead of cell references.
    • Imagine you have the Excel Table in one sheet and you are working in some other worksheet. You need not go back, again and again, to refer to the data in the table. All you need is to type the name of the table and excel will show you the option of using that table. Something as shown below:Excel Table Structured Referencing
    • Similarly, if you want to use the data from a specific column, simply have the entire name of the table and put a [ (square bracket). It will show you the column names and other options that you can use. Something as shown below:Excel Table Structured Referencing Columns
    • If you add more data to the table, it automatically gets figured in if you have used structured references.

NOTE: Structured references does not work within conditional formatting.

Related Tutorials: