Chapter 3: Excel Defined Tables
A range of worksheet cells can be converted into an Excel Defined Table. The Excel Defined Table features gives you more power to manage the data independently from the data in other worksheet ranges.
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.
You can insert more than one table into the same worksheet.
Defined Tables structure recommendations:
A Defined Table will not accept merged cells within it’s range. The data structure recommended for a Defined Table is similar to a database structure: a new record will be entered in a single row, all the parameters corresponding to this record will be stored in columns, like in the following image:
Avoid leaving empty cells within the data range, fill them with N/A or zero’s.
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.
- When you type anything under the last row with data, the Defined Table will automatically expand to include new data.
You can read all about Excel Defined Tables in the folowing posts:
3.1 Excel Table Components Read All Posts
3.2 Selecting Table Components Read All Posts
3.3 Formula References in Tables (Link – Coming soon…)
An important step in Your Free Excel Learning Journey is that you should subscribe for new posts, this way you will be notified when a new tutorial is published. Use the Subscription form below to “sign up.” Remember it’s FREE!
Start Your Free Excel Learning Journey!