Excel QuickBytes# 6: Benefits of Excel Tables

QuickBytes is a series of articles for simple and easy to implement techniques with exponential benefits

Excel tables are different. Different than data stored in regular rows & columns. I have hardly seen people using Excel tables, so I decided to write a quick article.

Here are some quick benefits of an Excel table:

Benefit# 1: Easy to format

Excel tables come with some pre-defined easy-to-format options. We can pick and choose formatting options meeting our needs.

Select the table > Table Design > Table Styles

Benefit# 2: Aggregations

Excel tables have the capabilities of applying aggregations with the help of mouse selection:

Select the table > Table Design > Check the Total Row

We can select the calculation type from a drop-down menu:

Benefit# 3: Insert Slicers to filter a data table

We can add a slicer to the table for filtering. It enhances the user’s experience.

Select Table > Table Design > Insert Slicer

We can add more than one slicer on a table.

Benefit# 4: Auto-range update

It is easily one of the top benefits of an Excel table. Excel tables dynamically adjust the range; hence any reference to the formula or graphs dynamically adjusts accordingly.

Tip: Convert your regular data table to an Excel table before inserting a pivot. Pivot data range adjusts any inclusion or deletion from the data table.

Benefit# 4: Formula replication on all the rows

We enter the formula in the first cell, and it replicates in all the rows:

The formula will replicate to all the new rows added to the table.

Also, if you have noticed, tables are using structured names for formulas. For example, the formula for Population Density is [@Population]/[@Area]. In simple words, Excel is saying divide the current row of Population column with the current row of Area.

Benefit# 6: Use Structured Names for Formulas

Apart from the benefit of structured cell reference mentioned above, Excel tables come with a specific name range, which we can customize.

The Name ranges make our life easier when it comes to navigation, applying dynamic formulas, or creating a dynamic drop-down list.

Applying sum calculation on the Population column of the ftUser table

Benefit# 7: Data Modeling

Excel adds only Excel tables in the Power Pivot data model or Power Query. If in case the data table is not an Excel table, then it converts the existing range into an Excel table before adding it to the model.

Adding the table to Power Pivot data model