How to Use Tables in
Microsoft Excel

by on 27th October 2009

In my moz pro session last week I spent some time explaining the benefits of using Tables in Microsoft Excel. Gone are the days of broken formulas that once worked, and extending your cell range references every time you add new data in a spreadsheet.

Using this technique isn’t all that different to using cell references, and the outcome is a more agile and robust Excel, with an ability to manage your data faster making for a more time-efficient experience. What’s not to like?

What are tables?

From Microsoft Office Online [Overview of Excel tables]

A table typically contains related data in a series of worksheet rows and columns that have been formatted as a table. By using the table features, you can then manage the data in the table rows and columns independently from the data in other rows and columns on the worksheet.

That seemed really obvious! Let’s drill down a little deeper.

What’s the actual difference between data stored in an ordinary worksheet and a table?

There’s no difference at all. The benefit of using tables are how cell references change, and how they seem to suit keyword research methodologies and other SEO applications particularly well. Let’s look at an example to get the point across.

First, take a look at the table itself. By highlighting your data in a worksheet, and pressing CTRL-L, you can add a little polish to a quite ordinary data set.



After: [Highlight, CTRL-L, Select “My data has headers”]


Apart from the obvious differences in formatting, tables become quite powerful for two reasons. First of all, a formula applies to an entire column immediately, not just in the cells you apply the formula to. Enter a VLOOKUP into cell D2 and the entire column marked “September” will perform the calculation. Great for big worksheets.

The formulas change significantly

We don’t like cell references, especially when they apply to a large array of data. Why? Because if you add more data, you have to manually adjust each cell reference. This process introduces bugs and errors quickly. Using a VLOOKUP as the example, let’s take a look at how formulas change the way they refer to cell ranges, other tables and columns.

This is our “pre-table” VLOOKUP. You can see cell references “A2”, cell ranges “$A$2:$E$11” and worksheet references “‘KW rank’!”:

=VLOOKUP(A2,‘KW rank'!$A$2:$E$11,5,FALSE)

This is the same query written in a table:

=VLOOKUP(Table1[[#This Row],[Keywords]],Table5[#All],5,0)

Where “Table1[[#This Row]” is our new cell reference, “[Keywords]” is the Column name in which the data we’re looking for is stored, and “Table5[#All]” is the table in which we’re looking to find a match and pull through the value from column 5.

Become more agile in the time it takes to drop a penny

Though it takes a small period of time to adjust yourself to this way of thinking, tables are great for keyword research agility. Imagine the scenario – you’ve nearly finished your analysis and you remember there’s a keyword type missing from the original data set. If you’re using tables, you can extract your data from Google Keyword Tool and paste the raw CSV straight into Table5. All of the VLOOKUPs in our example would automatically update as Table5 adjusted itself to accommodate the additional lines of data.

Tables save us time and effort and they’ve been around for a while. As I said in my presentation, they’ve changed the way I work with Excel for the better. Next time you’re working with a spreadsheet, try selecting your data and pressing CTRL-L. It takes a while for the penny to drop, but once it does, there’s no turning back.

Like this? Sign up to read more