Save Time With Excel 2007 Tables

An improved feature in Excel 2007 is Tables (previously known as Lists).  The purpose of an Excel Table is to aid with managing and analysing data.

By storing your information in a consistent format Tables allow for easier sorting, filtering and formatting and interpretation days, weeks and months after they were created.

Excel Tables 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.

Download the workbook and practice what you learn.

How to insert an Excel Table

Excel Tables 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.

Excel 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 a 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.

Excel Table 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 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.

Excel Table 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 Table 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

• To reference the whole table =Table1[#All]
• To reference the headers =Table1[#Headers]
• To reference the entire column = Table1[[#All],[Column1]]
• To reference all data (i.e. not including the headers) in the table =Table1[#Data]
• To reference the header value of one column =Table1[[#Headers],[Column1]]

These Structured References (Table1, #Headers, #Column1 etc) can also be combined e.g.

=Table1[[#Headers],[#Data],[Column1]]

……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 10% =Table1[[#This Row],[Salary]]*0.1%

Note: [#This Row] is indicating that the formula is applied to each row individually as in the example below.

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.

Excel Table Magical Feature 5

Excel Tables 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.

What do you like about Tables in Excel?  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.