User VertexVortex has been helping users out on r/Excel for quite a while.  After reaching out to him on Reddit, he agreed to help out by creating a lesson of his own specifically for this site.  Here’s his guest lesson on Structured Tables.  Hope you find it useful!!

The following is original content provided to Excel Exposure by VertexVortex:

This is a basic introduction to the benefits of Excel’s Structured Tables (added in version 2007). The basic premise of Structured Tables (referred to as Tables for the rest of this post) is that the most common format of organized data in Excel should be easier to use. Here are just a few of the benefits to these Tables.

Let’s say I have a collection of data, with similar pieces of information being placed in the same columns, and each row giving information about other pieces of data in that row. If you’re into database lingo, the column is a field, the rows are records, and the pieces of data are tuples. If you’ve worked much with Excel at all, you’ve probably run into data organized in this fashion.

I want a sum of the movement, but I don’t know how many rows I’m going to have. I could edit the formula every time, or I could insert rows (which would extend my formula), I could write the formula to extend beyond my current data and hope it doesn’t ever extend beyond…

But none of these are good options. This is where the Tables come in.*  It’s very quick to implement. Click on the Insert ribbon, and select Table.

*(There is another option, called dynamic named ranges. Dynamic named ranges take a bit more setup and are more trouble to work with, but are still a viable option, especially for earlier versions of Excel.)

The dialog will guess your data range, and ask if your data has headers. If your data doesn’t have headers, you should add them if you can.

The references in Tables can work a bit differently. *

To reference all the data rows of the whole table: TableName
To reference the whole table (including headers): TableName[#All]
To reference a column: TableName[ColumnName]
To reference a column header: TableName[[#Headers],[ColumnName]]
To reference a column with the header: TableName[[#All],[ColumnName]]
To reference an entire table row from outside the table: TableName[@]
To reference a data point from the same row (from outside the table): TableName[@ColumnName]
To reference a data point from the same row (from inside the table): [@ColumnName]

*(These reference syntax are true in 2010. In 2007, replace [@] with [#ThisRow]).

Here’s how we solve our expanding data problem:

Notice that the formula doesn’t change despite the number of items in the table increasing.

Fortunately, this is not the only benefit of using Tables. Tables are very dynamic, and that translates very well into other common Excel tasks. Let’s take a look at charting for a second. Normally if we have something charted, and we add more data, we have to go in and fiddle with the data ranges so that they match our data.

In the same way that formulas do not need to change when referencing certain sets of data in a Table, the data references of charts also do not need to be edited when new rows are added. If I take the data from earlier, and create a quick chart (deleting the annoying “Date” series, as I didn’t want to chart that), I can see my categories and their Movement. Adding another category into my Table automatically adds the points to my chart. You can see the effect here, before I’ve had a chance to key fake data in (which will also automatically update).

Another common tool in Excel, Pivot Tables, is made easier by using Tables. If you click anywhere inside your Table, then click “Insert->Pivot Table”, the reference will automatically fill in with the reference for your Table (defaults to Table1).

Question: Since it’s not a cell reference, does that mean the data will update dynamically if the size of the Table changes?

Answer: Of course it does!

All we need to do is refresh the Pivot Table when our source data changes, this will update both the content and size of our data.

Now, in my opinion, one of the neatest things about Tables is the calculated column. Calculated columns kick in when you enter a formula into a blank column cell in a Table, or in a column that’s already a calculated column. When the formula is entered, Excel automatically copies the formula to every cell in the extent of the Table. As rows are added or deleted, formulae are created and removed. The references react just as if you had copied and pasted the formula by hand, so it’s easy to plan for your absolute/relative reference concerns.

And while we’re on the subject, don’t forget the special Table referencing discussed earlier. In this example, I’m calculating the month of the date in the Date field:

This is the result immediately after hitting Tab:

Here’s more special Table referencing in action:

Isn’t that easier to read?  And the results immediately after hitting Enter:

So remember when I mentioned that Pivot Tables would update with both the content and size of our Table? If you go back to the Pivot Table that I’m going to pretend that you’ve made, and click Refresh, the new columns will pop up.

Before:

After:

This doesn’t make the Pivot Table better, but it sure does make it easier to use.

So that’s just a few of the many benefits of Excel’s Tables.

Some notes for advanced users:

Excel creates a Table by default when certain data is imported through the Data ribbon. This removes some of the pains of data that changed size as it imported. Of course, this only works in versions later than 2003.

For VBA programmers interested in using Tables, the collection of Tables in a worksheet are accessed by

And if the Table is the result of a data import, the query table can be accessed by

Thanks to VertexVortex for spending time creating this lesson for Excel Exposure!