The Power of Excel Tables

Overview

If you ask a hard-core Excel pro what their favorite Excel feature is, odds are that you’ll hear “tables” as much as anything else, and for good reason.  This seemingly basic feature is packed with valuable benefits, and yet it’s still way underused by the wider Excel community.

In this article, we’ll look at what tables are, how and why to use them, and a few tips and tricks as well.  Make sure to use the box below to download the example workbook showing all these approaches in action!  It also comes with a tables cheatsheet!

What are tables?

Think of a table as Excel’s way of turning a data list into something smarter and easier to work with. Instead of managing a bunch of individual cells, Excel recognizes the data as a complete set and gives you tools to analyze, organize, and update it more efficiently.

A normal range of cells is like a pile of papers on a desk. An Excel table is like putting those papers into a filing cabinet with labels, filters, and automatic organization. At the end of the day, tables are still just data, but they allow Excel to understand how the columns and rows work together to work more intelligently.

The term “Table” is nothing new – databases have been using it since the early 1970’s. A table is a collection of fields (sometimes known as attributes) and records. In Excel, the fields are columns and the records are rows.

Does that mean Excel is a database? That question will start loads of debate all over the internet! While Excel can act like a database at a basic level, it lacks many traditional database controls and scalability features needed for large, crucial data sets.

Think of Excel like a pickup truck and a true database like a semi-truck. You can haul some furniture around in a pickup. But if you’re consistently moving around an entire warehouse, you’ll want a semi-truck.

See the screenshot below that shows the a standard range and then converting the range into a table.

How do you use tables?

To convert a standard range to a table, simply select your range, then from the Insert ribbon menu, click Table (or with your range selected, click the Ctrl+t shortcut).  Verify the range and whether it includes headers (which should almost always be Yes!).  See below for these steps.

Once converted and you select a cell within the table, the Table Design contextual ribbon tab will display.  Make sure to give your table a logical name.  Experts recommend starting with “tbl” then camel-casing (capitalizing each word), such as “tblSalesData”.

You can also elect to display certain elements – header rows, total rows, etc.  Check the boxes for the items you want to display/use.

Lastly, you can select from several default table styles, or duplicate and modify custom table styles.  To select a different style, just click the Table Styles drop-down button on the right and pick a table style.  To make a custom table style, right-click on one similar to what you want and click Duplicate.  A pop-up will display where you can rename the custom table style and set specific formats for different table elements. 

Why use tables?

#2 – Formulas automatically fill to new rows

You’ve probably had a dataset that mixes hard-coded data with formulas.  It’s so easy to forget to fill those formulas when new rows are added, unless you’re using spilling formulas.  But with tables, those formulas automatically fill with new rows!

When you have a lookup, add columns together, calculate percentages like commissions, or other row-level formulas, tables work great for automatically filling them.  For formulas that reference other rows, it gets a little trickier (dynamic array functions shine here), but there are approaches that work well.  One of them is adding a row counter column to your table (one of my preferences).

See the screen capture below – notice that after pasting some data into the table, the last column’s formula automatically fills in for the new rows.

#3 – Dynamic drop-down lists

If you’re capturing any sort of manually-entered data in your Excel files, data validation is a must.  One of the best ways to keep data clean and consistent is with lists of valid values.  Often the values in those lists change over time, and that’s where the dynamic nature of tables can work great – even with a one-column table.  As you add valid options to the table, the drop-down list options update automatically!

I like to keep lists like that on a “Lookups” sheet.  Unfortunately, you can’t directly reference table columns, but named ranges are a great helper.  To set up a table-driven dynamic drop-down list, follow these steps:

1. Identify your list (with a header) and convert it to a table (Insert ribbon menu > Table or Ctrl+t).
2. Add a named range (Formulas ribbon menu> Name Manager > New). For the name, give it something similar to the column name (i.e. Region_List) and for the name definition, use table referencing (i.e. =tblRegionList[Region List]).
3. Select the column data that needs to be validated, then apply data validation (Data ribbon tab > Data Validation).  Select the List option and in the source box, reference the named range (i.e. =Region_List).

Any updates to that linked table column will be reflected in the drop-down list!  See the screenshot below to see the effects of linking the table to data validation.

#4 – Structured references

This is where tables will seem a little different the first time you use them.  You’re probably used to the column letter / row number referencing in Excel (i.e. range A1:D50).  That still works with table data, but you can also use structured references.

These make writing and understanding formula references much easier and contextual than the standard letters and numbers that make look like Greek if you’re not familiar with the data structure.  While they are easier to understand for humans, they are also easier for AI tools to understand as well.

Here are some key structured reference approaches:

1. Reference the entire table (including headers) by simply using the table’s name as if it was a range address or range name (i.e. =tblSalesData).
2. Reference a specific column in a formula by using the table’s name, then the column name within square brackets (i.e. =SUM(tblSalesData[Revenue]).
3. Reference columns within a row by using the @ symbol along with the column name(s) within a formula (i.e. =[@[Units Sold]]*[@[Unit Price]]).  If the column name includes a space, it must be surrounded by square brackets like that example.
4. Reference specific table parts using their names:
   
   1. tblSalesData[#Headers] = reference only headers
   2. tblSalesData[#Data] = reference only the data (excludes headers/totals)
   3. tblSalesData[#Totals] = reference only the data totals
   4. tblSalesData[#All] = everything (including headers and totals)

See the screenshot below where each of these is used and what it returns if you reference it directly.

#5 – Automatic styling

Tables also help automate the formats and styling within the table.  Add new data?  It’s automatically included with the existing style set.  By default, that means bold headers and banded rows (alternating colors).  Those make it much easier to read a sea of data.

Excel includes a few dozen default table themes with different color schemes – light, medium, and dark.  These are also impacted by any themes you might set (which are sometimes enforced by your organization for consistency).

You can build your own table styles by duplicating the existing table styles and modifying several components (see the left side of the screenshot below).  Available components include the entire table, the header row, totals row, and several others.

Note that you can override the table style formatting with fixed formats (i.e. fill colors, font colors, bold, etc.), as well as conditional formats.

See the screenshot below that shows several of the default table styles available, and some of the table components for which you can separately apply different formats to.

#6 – They play well with other tools

We already discussed drop-down lists (via data validation), but tables also play nicely with several other Excel tools.  Key among them are Power Query, PivotTables, charts, and slicers.

Power Query is an incredible ETL (extract-transform-load) tool that lets you source data from all over (websites, databases, Excel/CSV/TXT/PDF files, folders, etc.), transform it (filter, sort, add columns, perform advanced calculations, etc.), and load it – directly into tables.  This lets you refresh the data in just one click (or even schedule it!).  It’s one of the most powerful automation features in Excel!

PivotTables and charts are great examples of how the automatic range expansion helps reduce a lot of risk by keeping the referenced source data always up to date.

Slicers offer a great way to visualize the filters that you have in place on your data.  They are only available for data within tables or PivotTables and best used when you have a limited number of unique values to filter to (usually around 20 or less).

See the screenshot below where a slicer has been added to the table for easy filtering.

#7 – They make analysis easier

Tables come with some built-in analysis features: filters/sorting are automatically turned on.  The headers freeze automatically when you scroll down.  We already discussed the ability to add slicers.  And you can also add a totals row at the bottom.  Best of all for totals – they can be a mix of sum and other functions (i.e. average, count, etc.)!

See the screenshot below for the drop-down of common functions that pops up when you highlight a cell within the totals row.

#8 – Columns can be reordered

And lastly, here’s an underrated benefit I rarely hear people talk about – the ability to reorder columns and the column references still work.  Columns be added, removed, or reordered without affecting your formulas (as long as you don’t delete a referenced column). 

That might not seem like too big of a deal, but imagine this: you identify some source data, and build all kinds of calculations off of it.  Then the source data changes – a new column is inserted and everything shifts.  Without tables, all your references are all out of whack without some in-depth formulas.  But with tables – because of structured referencing – the order doesn’t matter!

I’ve been building some tools and advanced templates that use tables as source data primarily because of this benefit.  There are key fields (dates, account numbers, amounts, etc.) that are required, and anything else is optional.  That makes it super-easy for users to map their existing data structure to what is required by the formulas, scripts, etc. in the templates.

See the screenshot below where new columns are inserted, and the formulas referencing existing columns still work just as they did before.

Macros that help

To make tables even easier to use, I’ve built several tools to insert and use them quickly and easily.  Below are just a couple of the many tools available in the XLEV8 Excel Add-in.  Check these out in the support pages referenced below, or see them in action in the video below:

• Filter Picker – this custom picklist provides you with several filter and sort related shortcuts, like filtering to/out selected values, applying favorite filter/sort settings, inserting slicers, etc.
• Insert Picker – this custom picklist provides you with a variety of insert actions, including inserting and optionally naming a new table in one step – no mouse needed!

Video

Summary

If you’re not sold yet on tables, I suggest giving them a try to see for yourself.  Automatic expansion, styling, and formula filling are enough for me.  Add in the structured references and the ability to reorder the columns without ruining your formulas, and there’s just no going back.

To see some of these benefits in action if you’re new to tables or just curious, make sure to download the example workbook using the box above.  There are plenty of items you can learn from to take advantage of in your key workbooks.

Have you used tables in some creative way or found a bespoke benefit from them?  Let us know in the comments below!