Excel Table is probably one of the most underrated features in Excel. It is super easy to understand, and have its own merits.

Excel Table 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, a variety of features can be used on that data.

Creating an Excel Table

If you have the data in the worksheet, to create a table

  • Select the entire data set
  • Go to Home –> Format as Table
  • Select any one of the pre-defined table formats
  • 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…
  • Click OK

A faster way to do this is to select the data set and use the keyboard shortcut – Control + T. It opens the Format as Table dialogue box.

Converting Excel Table back to regular Data Range

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

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

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

  • An Excel Table automatically creates headers that have the option to sort or filter
  • If your table is long and stretch 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
  • If you type anything in the cells adjacent to the table, it automatically expands the selection to include this data. If not required, this can be switched off
  • If you are using the data from the table, and if there are any additions to it (say you add one addition row of data), it automatically gets figured into the calculations

Excel Table Designing

Excel Tables are cool and fashionable. You can dress it the way you want. When you click on any cell in the Excel Table, an additional tab Table Tools Design appear in the Excel Ribbon area

When you click on the Design tab, there are several options that you can access for Excel Tables

  • 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 later in this article), or you can re-size the table
  • 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 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
  • External Table Data: This section can be used to export data or refresh data
  • Table Style Options: This is the fashion section where you dress up your table
    • Header Row: If unchecked, it removes 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
  • Table Styles: Give you multiple options to change the styling of the table. You can also create your own table style by selecting New Table Style option

Structured Referencing – Referring to cells in Excel Tables

Another benefit for 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