What is an Excel table?
Imagine you’re celebrating someone’s 10 year anniversary and you have reservations at the hottest restaurant in town. You show up with all your friends and family and you’re seated immediately. Only one problem… everyone is sitting at separate tables!
Not a fun way to start the night, is it?
Using an Excel table is sort of like asking the maitre d to sit your family at the same table. It tells Excel that the data within a cell range is related and should be treated as a group.
Download the FREE Ebook
Short on time? Download the Ebook version of this post and get exclusive access to more content, Free webinars, and much more!
Why should I use an Excel Table instead of just having my data in a list form?
Using Excel tables has it’s advantages. Data mining, cleansing, analysis, and reporting become easier and more efficient when data is structured in a table rather in a random list. Here’s why:
Avoid reference errors with automatic structured references
When you convert lists into tables, Excel automatically assigns structured references which makes referencing data easier.
To make things easier, let’s go back to the restaurant analogy. Imagine that your family gets seated at separate tables, but you are all sharing bottles of champagne and appetizers.
Think about how frustrating this would be for your family who have to pass glasses, bottles, and plates back and forth across tables. Think about how confusing for the restaurant staff this is, who have to manage the additional work and coordination that comes with related people sitting separately.
If everyone were sitting together, it becomes much easier because the restaurant can dedicate less resources to your party and everything goes to the same table.
Referencing data that Excel recognizes as a group makes it easier for you to write formulas quickly as well as easier on the resources Excel uses to calculate your sheet.
Work MUCH faster!
Excel tables help you work faster, not only because of the advantages of using structured references, but also because filtering data, adding totals, and even building formulas inside the table can be done with just a few clicks (more on this later).
Add new data efficiently
A big reason why power users leverage Excel tables is because of how easy it is to add new data to it.
Imagine the restaurant again, except this time your crazy co-worker shows up 45 minutes late.
If everyone is sitting in separate tables where does he go? Does he sit with you? Does he sit alone at a table situated at the back of the restaurant next to the restrooms?
Maybe it’s best if he sits far away so he can’t interact with anyone…
However, If everyone is sitting together it’s easy to pull an extra chair from another table so he can sit and enjoy the night with everyone.
When your data is structured in a table it’s easy to add new data because Excel automatically includes it in your table and updates the formulas that reference the table to include this new data.
How do I create an Excel table?
Creating an Excel Table is really easy!
If your data is already in a list format, simply highlight what you want to include in your table and hit Ctrl + T on your keyboard.
Once you create your table, make sure you name it something descriptive:
Click inside the table, then click on design in the ribbon bar. From here, you can enter a new name for your table be renaming it in the left hand corner…
Excel Tables Tips and Tricks
Now that you are an Excel table creating jedi, it’s time to take your skills to the next level. I’m going to show you some of the best tips and tricks to take you from just creating Excel tables to running circles around everyone else in the office and enticing your boss to say:
How to Insert Totals Row At Bottom
With just a few clicks you can add a dynamic totals row to the bottom of your sheet. Not only does this save you time but since it automates the calculation there is virtually no way to make a mistake in the formula.
To add the totals row, click on any cell inside of the table to activate the design menu. Once inside the design menu, make sure you check “Total Row”. This will activate the total row at the bottom of the table.
You can then change the way the totals calculate the table’s values by clicking on the dropdown at the bottom of the table:
Dynamically expand to include new data
One of the best features of using an Excel table is that adding data to the table itself is super easy, even if you already created a totals row.
To add data to a table you can either:
- Drag the table down
- Paste your data to the bottom of the sheet
- Use the tab key
Structured References
When you reference an Excel table, Excel automatically applies structured references to your formula, which makes building formulas, nesting, and understanding formulas easier.
Structured Reference
Regular Cell Reference
Structured references are a lot like named ranges, except Excel automatically uses the column header as the name for the range you’re referencing.
Remember the co-worker who showed up 45 minutes late? Not only does he have a seat at the table but the kitchen already knows what he wants to eat!
What do I mean by that? Well, the best part of structured references is when you update your table with new data, your formulas are automatically updated to take the new data into account.
Spit out lightning fast calculations
Creating a formula and dragging it all the way down your table can be a drag (pun intended). With Excel table, whenever you enter a formula Excel automatically applies the same formula all the way to the end of your table, which can save you tons of time!
How to convert back to a range of data
Sometimes you no longer want an Excel table (BLASPHEMOUS!)
Whenever you want to turn a table back into a range:
- Click anywhere inside the table
- On the Design tab, in the Tools group, click Convert to Range
Use multiple filters in one sheet
When you create an Excel table, you’ll notice that filters come part of the package..
This allows you to have two separate sets of filters on the same sheet. In case you didn’t know this, having two sets of filters on one sheet is otherwise impossible in Excel.
Import Data into Power Pivot
Power Pivot is a relatively new feature Microsoft rolled out a few years ago and it’s a BIG DEAL! If you don’t know what it is or looking to learn more about it, check out Jon Michaloudis over at My Excel Online, he has some of the best tutorials I’ve seen on Power Pivot.
The best way to summarize it:
Pivot Tables = Eric Banner
Power Pivot = The Hulk
The best part is you can take an Excel table and directly import it into power pivot.
http://myexcelonline.com/blog/wp-content/uploads/2016/07/Using-Excel-Tables.gif
Source: My Excel Online
For more info, check out My Excel Online
Excel Table Limitation
Just like anything else, Excel tables have their drawbacks.
Because of the nature of structured references it is difficult to lock down a specific range inside of a formula. This means that when you reference a column table and you drag the formula over, Excel will drag the column reference as well.
This can cause you huge headaches and frustration if you’re not paying attention to that…
How do I maintain absolute references when copying my formula over?
I’ve got your back!
There is a small workaround to this drawback: use the Ctrl + R keyboard shortcut (fill right).
- Copy the formula you want to drag with Ctrl + C
- Highlight the cells you want to copy to
- Then hit Ctrl + R
This maintains the original references.
Excel tables can be so useful when dealing with data, especially if it’s a large amount of data. Next time you’re at a restaurant look around at how busy all the staff is and remember how much easier things would be if they all just used Excel tables (no, it’s not weird that you’re thinking about Excel at dinner).
Continue the discussion on FaceBook!
