What are Excel 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.
In this updated article for Excel 365, 2019, 2016 and 2013, we’re going to look at using tables.
What’s the actual difference between data stored in an ordinary worksheet and a table?
There’s no difference at all. The main change when you’re using tables is 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:
After:
This animation shows you how to make a Table in Excel: highlight the cell in the top left hand corner, then CTRL=SHIFT with the down key to highlight, then CTRL+L to create 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 formul into a column and the entire column will populate with the caluculation automatically.
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 an example, let’s take a look at how formulas change the way they refer to cell ranges, other tables and columns.
This is an example of a “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
Though it takes a small period of time to adjust yourself to this way of thinking, tables are great for SEO research applications like keyword research and many more activities.
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 your favourite search volume tool and paste the raw CSV straight into your table.
All of the VLOOKUPs in our example would automatically update as the column adjusted itself to accommodate the additional lines of data.
Tables save us time and effort and they’ve been around for a while. 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.
