Table formulas were something that I discovered recently. Actually our reader m-b commented that he prefers to convert a range to a table and then employ table formulas instead of named ranges. That got me curious enough to explore them further and here’s what I learnt.

A Table in Excel

A table is a feature in Excel that makes it easier to format and analyze a set of data points in a spreadsheet. Tables were introduced in Excel 2007 as an extension of the ‘Lists’ feature in the earlier versions. In Excel 2007 onwards, you can also use the table formulas to extract data from a table.

How Does One Create a Table

In Excel 2007 and later, all you have to do to convert a given range to a table is to simply select the range and then click the ‘Table’ button under the ‘Insert’ tab on the ribbon. Better still, as our reader Sam pointed out, use the shortcut key CTRL + T.

So What Good is the Table For

Lots actually.

Formatting: Completely change the look and feel of your data with a few mouse clicks

Summarize by adding row for Total: Just turn on the check box for ‘Total Row’ and you have a new row inserted just below the data set with the totals. Not only totals, you can select any cell in this row and choose from a number of aggregation options such as count, min, max etc.

Export and Share: Export and share the table with other users using SharePoint

Provide a Name to the Table: You can give the table a specific name (say ‘Sales_Data’) and use it later in your formulas. To give a new name to the table, open up the ‘Name Manager’ under the ‘Formulas’ tab and then edit the table name.

Table Formulas in Excel

“Flaming Bisons !!! You made me read all this just to show what an Excel table looks like … I already know what it is so why don’t you come straight to the point !”

Oh! That was rude. Did nobody ever tell you that patience is a kingly virtue?

Table Formulas let you access table in a easy and intuitive manner. Let’s begin by converting a range to a table. When you create a new table, Excel will provide with a default name, say something like ‘Table1′. This may not be most intuitive of names and you may wish to rename it to something else that is easier to remember and comprehend for others. Open the ‘Design’ tab and overwrite the text in the ‘Table Name’ box to something like ’sales’.

The opens up a whole new box of chocolates ! You can now refer to and use the entire table, individual columns, rows, data range, headers or totals in your formulas.

Using a Specific Column from the Table in a Formula

Say you wanted to know the average for all items in the ‘Revenue’ column. Enter something like =AVERAGE(sales[Revenue]) in a cell and smile. The formula is so intuitive that it hardly needs explaining. However being the a** I am, let me be me. The ’sales[Revenue]‘ string refers the data points in the ‘Revenue’ column of the sales table. ’sales[Target'] would have referred to the ‘Target’ column of the same table. We can now use this like any other range in any excel formula. So =MAX(sales[Revenue]), =LARGE(sales[Revenue],5) and =COUNT(sales[Revenue]) are all valid formulas.

Using a Specific Row from the Table in a Formula

What’s good for a column is good for the row. Format, however differ. To refer to a row in a table, we use the @ symbol. So if you want to refer to the the 10th row in the table, write =sales[@] in any cell in the 10th row. So something like =countif(sales[@],”<>”) would give you count of non-blank cells in the particular row of the table. If you copy the same formula to the cell immediately below, the corresponding values from the next row would be returned - even though the formula hasn’t changed.

Using the Entire Table in a Formula

What’s good for a column and row must be good for the table
To refer to the entire table, use : =sales[#All]
To refer to only the data portion of the table, use : =sales[#Data]
To refer to the headers, use : =sales[#Headers]

Using the SUBTOTAL Formula with the Table

Another interesting feature of the Table is the use of SUBTOTAL function. The SUBTOTAL formula has two parts - the first one indicates the formula to use for aggregation and the second one contains the range to use. So =SUBTOTAL(9,A1:A10) would give the sum of the range from A1:A10 while =SUBTOTAL(1,A1:A10) would provide the average for the same range.

Coming back the the Excel Table, you can aggregate over the entire table (or a portion of it) the values by using the SUBTOTAL formula and providing it with the reference to a particular row, column or the entire table. Just as in the example above, you can get the average of the ‘Revenue’ column by using =SUBTOTAL(1, sales[Revenue]).

If you noticed, when we turned on the ‘Total Row’ option, a new row with the total for columns got added to the table. We can now go ahead and modify the formulas using any of the formula options shown above. So the totals are not limited to just being summing but can very well be extended to averages, min, max, variance etc.

Using the Totals for a Particular Column in a Formula

To refer to the total for a column in the table, say Revenue, we can now write something similar to =sales[[#Totals],[Revenue]]. Please note that the ‘total’ value returned may not be the total (SUM) but is determined by the SUBTOTAL function parameter used to aggregate the column. Let’s illustrate this with an example. If the SUBTOTAL formula in the ‘Total Row’ for the ‘Revenue’ column contained an aggregation function parameter with a value of 4, the total would have returned maximum value from the column. Now when somebody wants to refer to this total using =sales[[#Totals],[Revenue]], the “total” returned would not be a sum of column values but the maximum.

I have a feeling that there’s more that can be done with table formulas. Have you tried using them? Care to share ?