The Power of Tables
One of the most powerful tools in excel is excel’s table feature…
and shockingly, there are few people who I’ve seen really understand & master this concept.
Excel tables have so many benefits, and once you learn about them, I can promise you you’ll start using them all the time.
What is an Excel Table, and How Do You Create One?
You may be wondering what exactly an excel table is…
Isn’t it just data structured in a table format?
Not quite.
A table is a special object in excel that once you convert a range to, you can unlock a ton of features.
To create a table, simply select your range and press CTRL + T…
or navigate to Insert > Table
Structured vs Unstructured Data
OK..before we do a deep dive on tables…
let’s understand something about data structures.
Which one of these 2 images is easier for you to comprehend?
I
’m assuming you voted as #2.
It’s much prettier…
and is presented in a way that makes it easier to understand what happened…
across which account… and what period.
Now if I asked you the same question in terms of which report is easier to run a calculation on…what would you say?
Without a doubt, report #1.
Why?
The information is uniform, and packed into a tight table that allows you to get the values across each period & account with a simple formula.
The general idea is that we as humans like to consume data often times in an unstructured pretty format…
while machines like to ingest data in a structured tabular format.
The Power of Tables
Tables come with many benefits…
Let’s review some of my favorite:
Auto Extend
My favorite benefit in working with a table is the fact that the table will automatically extend as you add in data to a new row, or column
This is especially useful whenever you have a reference to this table, as you won’t need to go back and update this reference
Formulas auto-populate
The whole idea with a table is you are trying to make your data uniform across every row in a column. With tables, your formula will automatically populate once you populate the first row
Refer to named ranges
When you convert your data to a table, any reference to that table will be shown in your formula bar in an easy to understand syntax
Select ranges easily
I LOVE keyboard shortcuts…
and with tables, your keyboard shortcuts for selecting a row, column, or entire range, will first default to the table that you are in
Transform more easily with Power Query
If you aren’t using Power Query…you are missing out.
Power Query is an ETL tool allowing you to Extract, Transform, and Load your data.
When your data is already in a table format, it makes it that much easier to then manipulate via Power Query, or a Pivot Table
Tables to me are a concept that separate the beginners from the more advanced…
as you continue on your journey in mastering Excel, Tables are a key piece in helping you move dynamically and flexibly.
Unleash the power of Tables!