Excel Tables with Excel MVP Zack Barresse

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.


You may also like

December 20, 2013

100+ Best Excel Resources And Websites

August 6, 2019

How to Create a Relative File Path in Power Query

April 9, 2011

How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell

September 17, 2012

The Excel Rollover Mini FAQ

February 17, 2014

It’s time to say “Goodbye,” to Hungarian Notation
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Free Power User Quick Guide

Discover The BEST Ways To Use Lookups And Conditional Calculations Quickly And Easily With This Reference Guide -- You Won’t Want This To Leave Your Side

With so many ways to use Excel, it can be difficult to memorize all of the key functions, calculations, and techniques you can employ to meet your goal: simply get the job done.