Using Tables in Microsoft Excel 2007

In my SEOmoz 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.

[How to] use tables in MS Excel

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.

Before:

data

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

table

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. Good luck!

Photo: In Case Designs


Stay Updated: Sign Up for Webinar & New Blog Alerts

10 thoughts on “Using Tables in Microsoft Excel 2007

  1. Hey Richard,

    It’s always a good idea – semantically and for futureproofing – to rename the tables as soon as you make them. It gets a bit out of hand when you eventually reach ‘Table2315′, and you have no idea what the tables before that one contain…

    Thanks for highlighting this feature though!

    A

  2. Richard you and Will have become my Excel geek heros since the Pro Training, I only hope that one day I can equal your majestic skill with the green X :D Extremely informative sir!

  3. Sure thing Adrian – I left that tip out [apologies]!

    For everyone else, navigate to the design tab and on the left had side of the ribbon there’s a table name field. You can rename tables and the best bit is that all teh queries update on their own.

  4. Philippe says:

    Thanks Richard! I’ve been looking for the info after your presentation but could not find exactly what you explained. Thanks for sharing this =P

  5. Tim says:

    Just a quick note say that you don’t have to use specific cell reference in a normal vlookup formula, you can refer to the whole column thus allowing the addition of extra data.

    e.g. VLOOKUP(A2,‘KW rank’!$A$2:$E$11,5,FALSE) could be written VLOOKUP(A2,‘KW rank’!$A:$E,5,FALSE)

    I’m not meaning to take anything away from your article, there are of course other advantages to using the tables method above.

  6. Hi Tim – yes you’re right – thanks for adding to the conversation!

  7. Tom Szekeres says:

    Having to use Office/Excel 2003 at my (new) work, I was actually dreading going back to not being able to use about a million of the features introduced in 2007. Thankfully turns out that the “L” in Ctrl+L stands for “Lists” (almost the same thing as above), so the feature seems to have been around longer than I thought. Sadly you can’t use the same friendly column names as above, but it does make a huge difference to generating things like graphs and pivot tables – you can enter new rows without having to manually reset the pivot, etc., and publish a list to Sharepoint 2003 with a right click, left click combo. Now if I could just work out a way to feel better about IE6, I’d be happy…

  8. Good to know Tom, thanks! Though I doubt you’re ever going to feel good about IE6…

  9. Tom Nash says:

    Tables really come into their own when using large data sets – features like autofil column formulas and instant total rows can save you loads of time when crunching on Excel all day.

    No more cycling through F4 anchors either :)

  10. Agreed Tom! The other day I did have need for a totalling column that looked for the total row in 18+ different tables. Needless to say, I turned autofill off for that one.

    Was that my first ever Excel anecdote?

Comments are closed.