top of page

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


The Power of Tables - create 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?


The Power of Tables - Structured VS unstructured Data

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


The Power of Tables - Auto Extend

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


The Power of Tables - Auto-populate

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. Learn how to name ranges here.


The Power of Tables - named ranges

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


The Power of Tables - select ranges easily

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



The Power of Tables - power query

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!


218 views

Related Posts

See All

Comments


bottom of page