What is a Defined Table?

An Excel Defined Table is a special object in Excel which adds a lot of new options and functionalities when working with data ranges. With Defined Tables, you can manipulate data more easily than before, adding power and flexibility to your applications.

To create a Defined Table, select the data range, or simply select any cell within the data range, and click the Table button from the Insert tab from Ribbon. The keyboard shortcut key is Ctrl+T to create a Defined Table.

Can you tell which table from the image below is a Defined Table?

Data range vs. Defined Table

The formatted data ranges looks identically, but only the right range is a Defined Table. The easiest way to see if there is a Defined Table object inserted into a worksheet, is to check the Name Manager to see if there are any Table Names listed (the Table icon is different from a regular Name icon, see image above), or you can click to select any cell within the data range, if that cell is part of a Defined Table, Excel will open the Table Tools tab in Ribbon.

There are 4 Excel Table Components:

1. Headers Row
2. Data Body Range
3. Totals Row
4. Sizing Handle

1. The Headers Row:

Header Row

The Headers Row contains the Column Names. If your data does not have headers, Excel will automatically create a Header Row with default Column Names like: Column 1, Column 2, and so on. There can be only one Header Row, merged cells are not allowed in Defined Tables.

2. The Data Body Range:

The Data Body Range contains all your data, excluding the Headers Row and the Totals Row:

Data Body Range

3. The Totals Row:

The Totals Row contains drop downs for each column, each dropdown will allow you to select from various formulas to display the desired result.

4. The Sizing Handle:

The Sizing Handle is a small triangle on the bottom right side of the Defined Table:

Sizing Handle

The Sizing Handle will allow you to manually resize the Defined Table, you can increase or reduce the number of rows OR columns.

Note that you cannot resize a Defined Table to add more rows and columns in the same time, you need to perform 2 different resizing operations for that.

Advantages of working with Excel Defined Tables:

There are many advantages, including (but not limited to) :

• You can easily select Table components, with a single click
• Easy Formatting: In Table Tools tab from Ribbon, you will find many Design Styles for Tables, you can configure your own personal style.
• Easy sorting and filtering, each column Header has a drop down menu which allows you to sort and filter data to see only what you want.
• Formulas are easier to understand, Excel will automatically include named references in your formulas (also known as structured references)
• Excel will auto – fill formulas for the entire column whenever you insert new rows, no need to manually update formulas for new rows.
• An Excel Defined Table is a dynamic source for your charts, pivot tables and formulas, there is no need to adjust the data source if you add new rows or columns to the Table.

In the next lesson, I will show you how to work with Excel Defined Tables, to select Table Components.

Enjoy,

Catalin