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.
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
Excel converts the existing range into a table before importing it in the Power Query
How to convert a regular table into an Excel table?
Select anywhere in the table > CTRL + T