An improved feature in Excel 2007 are Excel Tables (previously known as Lists). The purpose of an Excel Table is to aid with managing and analysing data.
Excel Tables allow for easier sorting, filtering and formatting and interpretation of your data.
They have some features that I think are MAGICAL, so keep reading to see why you should be using them to save time and make your workbooks easier to use days, weeks and months after you created them.
NOTE: Excel Tables are not to be confused with Data Tables that can be used for what-if analysis.
Enter your email address below to download the sample workbook.
How to insert Excel Tables
They typically only have column headings, with no blank columns within the table.
To insert an Excel Table select a cell anywhere in the data range you want converted to a table and press CTRL+T.
In the example below the Table begins with the headers on row 3. Rows 1 and 2 are not part of the Table.
Magical Feature 1
Once your table is inserted you will see that formatting is applied automatically and a new tab will appear on the ribbon called ‘Design’.
Here you can choose from 61 different built in styles to change the format as I have done above, or create your own in line with your branding.
If you don’t want to work with your data in an Excel Table but you like the instant formatting it provides, you can convert the table to a regular range while keeping the style formatting you applied.
You’ll find this option on the Design tab of the Ribbon, or simply right click anywhere in the table and select Table/Convert to Range.
Magical Feature 2
Insert a Total Row. Whoop-dee-doo I hear you say. Ah, but this isn’t any ordinary Total Row.
OK, I agree it looks pretty ordinary so far, but take a look at the picture below. Simply select a different function from the drop down list, or select More Functions to enter something else.
- The Total formulas don’t have to reference the Excel Table; however it’s unlikely you’ll need to do this.
- You can toggle the Total Row on and off and Excel will remember the functions that exist.
- As you add rows/columns your Total Row will automatically update to include the new data.
- The Total formulas use the SUBTOTAL function so that if you Filter your table the Totals will only SUM the visible cells.
Magical Feature 3
Insert a formula in a new column.
1) Type in your new heading immediately after the last column and the Table will automatically incorporate the new column in the Table range and apply the formatting.
2) Tip: Format the cell before you enter your formula.
3) Enter the formula and you will see that the table automatically copies the new formula and formatting down the whole column to the bottom of the table. This is known as AutoComplete.
4) Add rows and your formula will AutoComplete to fill the new rows.
5) Any changes you make to the formula will automatically propagate to all rows.
6) Notice the formula in the formula bar below does not refer to cell references. Instead it uses what Excel calls ‘Structured Referencing’ which is very similar to Named Ranges. Read Magical Feature 4 below for more on this.
Excel Tables 2007 Syntax:
Excel Tables 2010+ Syntax:
Magical Feature 4
Working with Formulas in Tables
When you insert a Table Excel automatically gives the Table and each column a Structured Reference, similar to a Named Range.
This Structured Reference automatically updates as you add or remove data.
The name for the table is usually something like ‘Table1’ and the columns will have names based on the column headings in the table. If you don’t have any column headings Excel will automatically insert headings like Column1, Column2...and so on.
As you add or remove columns the Structured References automatically update.
Renaming Structured References
You can rename the default ‘Table1’ name on the Design tab in the Properties section.
To rename the columns simply type a new name in the headers.
Table Formula Shortcuts
- The whole table =Table1[#All]
- The headers =Table1[#Headers]
- The entire column = Table1[[#All],[Column1]]
- All data (i.e. not including the headers) in the table =Table1[#Data]
- The header value of one column =Table1[[#Headers],[Column1]]
- The current row in Excel 2007 it's: =Table1[[#This Row],[Column1]] or in Excel 2010 onwards it's simply =@Column1
These Structured References (Table1, #Headers, #Column1 etc) can also be combined e.g.
......and so on.
Note: If you renamed your Table you will replace ‘Table1’ in the formula with your Table name. Likewise you will use your column names if they aren’t the default Column1 and so on.
Using Structured References in Formulas:
- Sum the Salary column =SUM(Table1[Salary])
- Average the Salary column =AVERAGE(Table1[Salary])
- Sum the whole Table =SUM(Table1)
Note: the above formulas exclude the header cells.
- Multiply Salary by 0.1%
- Excel 2007 formula: =Table1[[#This Row],[Salary]]*0.1%
- Excel 2010+ formula: =[@Salary]*0.1%
Note: [#This Row] or [@row] for Excel 2010 onwards is indicating that the formula is applied to each row individually as in the example below.
Excel 2007 Syntax:
Excel 2010+ Syntax:
Getting to grips with using Structured References in formulas takes a little practice, but just imagine how much time you will save once you can write a formula from anywhere in your workbook without to-ing and fro-ing back and forth between sheets.
Important things to know about Structured References:
- Table names are unique to the workbook. If you have 5 Tables in a workbook on many sheets, each table will have its own name which you can reference from any sheet.
- You can’t have a Named Range with the same name as a Table or Column.
- You will find your Table names in the Name Manager alongside your Named Ranges.
- If the table name or columns names are changed the formulas will automatically update with the new names.
- Structured References are handy when working with data validation drop down lists, PivotTables, and anything else dependent on the Table range like formulas etc.
Magical Feature 5
They automatically insert Filters.
I’m not going to go into how to use Filters here, but for those of you who know how to use them, you'll be pleased to know that all formulas used in the total row automatically exclude any rows hidden by the filter.
And any new rows added are automatically included in the total.
Absolute and Relative References
Unfortunately you can't use the F4 key to apply absolute references to Structured References in formulas. They have some quirky rules depending on whether you're copying and pasting a formula or left-clicking and dragging it. More on working with absolute references for Table Structured References.
What do you like about Excel Tables? Are there any features that you find annoying?
Did you like this tutorial or could it have been better? Let me know your thoughts in the comments below.
|Please share this with your friends and colleagues on Twitter, LinkedIn, Google+ and Facebook etc.|