Surprisingly, tables are the most misunderstood and, hence, underused feature of Excel. While some people confuse this feature with data tables developed by Scenario Manager, most people just plainly ignores it owing to its alien syntax. Microsoft Search Analytics revealed that less than 1% of the Excel users employ tables in their workbooks. This is a worrisome statistic given the multitude of difficult tasks tables simplify.
With Jordan Goldmeier facilitating the session, we have Oz du Soleil, Rick Grantham and our special guest Zack Barresse on the panel in this episode. And the topic is, you guessed it, Tables!
Let’s see what is so interesting about them?
1 – Why Use Tables?
Zack goes on at lengths about how tables let you overshoot in many things you might want to do to the data. They can make a data analyst’s life much easier. But not all is hunky-dory. Tables do have some restrictions one needs to be aware of.
2 – The Good
Zack list the following aspects of using tables as the source of his obsession with them:
- Tables force structure on to the data: unique headers, a single total row and so on.
- They act as dynamic named ranges. Yes! Forget about using a complicated mixture of INDIRECTs, MATCHs and ROW() and COLUMN() formulas. Tables offer a very elegant solution.
- With a small workaround, they can also be used to achieve dynamic Data Validation list! Watch the video above check out Zack explain the trick.
- If your pivot table takes data from a table as its input, updating that pivot table and the linked pivot charts literally becomes as easy as pressing a button. And that button is called “Refresh”, found under the ANALYZE tab.
3 – The Bad
Most of the time there is no good without some bad. And same is the case with Excel tables. Be sure to commit the following to memory:
- Absolute referencing in a calculated column can mess things up.
- Never use a subtotal function in a calculated column.
- Tables cannot be partially protected. For example, if you enable sheet protection but allow sorting of cells, the user will still be not able to sort the data in the tables.
4 – The Ugly
There is no ugly side to using tables. We won’t be encouraging more use of this feature otherwise.
5 – Zack’s Book
Rick brought up the topic of Zack’s new book “Excel Tables: A Complete Guide for Creating, Using and Automating Lists and Tables”. The book is about tables, of course, but within the context of importing external data, Power BI, Data Models and Power Query.
What’s next?
Get your hands dirty and start learning about the tables. The more you do, the more it will fascinate you.
Do not forget to share this discussion with your friends or colleagues. Maybe tables is the solution to what keeps them up at night. And write to us with your views in the comments section below.
- SSSVEDA DAY 7 – Every Team Needs Someone Who Understands Data - February 18, 2018
- SSSVEDA DAY 5 – When Data Analysis is Wrong - October 31, 2017
- SSSVEDA DAY 4 – Sharing the Excel Knowledge - July 18, 2017
