**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

**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 ?

The Subtotal Fuction also acceps 101,102…109..111 as parameters.

This works with both filtered/ as well as hidden rows

Table names are dynamic…but not truly. Keep a couple of rows blank and add some data. Delete the blanks rows and the table does not expand

January 28th, 2010 at 11:57 am@sam – Touché. Actually 101 corresponds to 1, 102 to 2 and so and so forth. While the former will ignore hidden values, the later will include them while calculating the subtotal.

January 28th, 2010 at 12:04 pmNever used.

Thanks for the tutorial!

January 28th, 2010 at 2:27 pm@Ajay,

January 30th, 2010 at 1:29 ama To convert a Range to a Table you can Say Ctrl+T

b To name a Table you dont need the name manager – on the Design Tab on the top left corner the Default name of the table appears. you can just overwrite on it

c. The @ syntax is only for 2010, for 2007 its #ThisRow

d. The only problem with 101, 102… etc is that its available form 2003 and above

@Sam – I’ve included both the shortcut for creating a table and naming it in the post above. Thanks a ton !

Regards,

January 30th, 2010 at 2:07 amAjay

Is is also to be noted that DSUM/DGET etc cant handle table names as the header row gets excluded in the range…. so we are back to dynamic names

January 30th, 2010 at 11:08 pmI was able to use the formulas you referenced for Excel 2007 tables, but I was unable to use them in Excel 2003 for List Objects that I had created. Are these table formulas only valid for Excel 2007 and higher and not compatible with the Excel 2003 List object?

January 31st, 2010 at 11:13 am@dhoff – I am pretty certain that the table formulas were introduced in Excel 2007 though a lot of this construct is an extension of the ‘List’ feature in Excel 2003.

Regards,

January 31st, 2010 at 2:16 pmCorrection regarding my earlier comment on DSUM etc

=DSUM(TableName[#All]…..etc) selects the header

February 1st, 2010 at 11:59 pmHow do you revert the table back to just a spreadsheet

May 6th, 2010 at 5:17 amUseful write up of an underused feature.

Note you can replace the totals formulas with anything you like, you are not stuck with just SUBTOTAL. Things like COUNTIFS and SUMIFS can be especially useful. You can still refer to them using the usual syntax =TableName[[#Totals],[ColumnName]], and your custom total formula survives the total row being turned off and back on.

@PC_Babe – on the Table|Design ribbon, use “convert to range”, or just right click > table > convert to range

June 4th, 2010 at 8:37 amI fell in love (an exaggeration) with the table formula referencing, then quickly had to get a divorce because of the inability to do absolute cell, row, or column referencing (i.e. the equivalent of $a1).

How this was never considered a use case I don’t know, but this rendered it just too annoying to use, so I had to turn this referencing style off.

June 7th, 2010 at 10:10 amhow can I absolute a table formula. when i drag a table formula in other column, the formula change.

table name: tblSales

sales-stock

2-30

2-33

the formula is : sum(tblsales[sales])

July 21st, 2010 at 7:06 amafter copy or drag this formula horizontally it change to :

sum(tblSales[Stock])

I tried using this table feature in conditional format. The problem is that sometimes it accepts, but produces no result. Other times it gives an error. The absence of proper documentation means we have no means to fall back on or refer to. The said formula was:

=IF(AND(Table1[[#This Row],[Qty Available]]<=Table1[[#This Row],[Reorder Level]],Table1[[#This Row],[Qty On Order]]=0),TRUE,FALSE)

When I referred this problem on microsoft forums, one of the MVPs asked me to remove the IF clause:

"

Assuming your table syntax is correct, (I never use that structured referencing!), get rid of the IF function:

=AND(Table1[[#This Row],[Qty Available]]<=Table1[[#This Row],[Reorder Level]],Table1[[#This Row],[Qty On Order]]=0)

If that doesn't work then I'd use normal A1 cell references.

I just can't understand why people would use the above compared to:

=AND(A1<=B1,C1=0)

”

February 17th, 2011 at 5:34 amIf a feature is introduced, why not elaborate?

Do you have any ideas? I tried using the “Evaluate formula feature” but even that shows error on every step.

I think everyone will benefit if we can take the discussion ahead. Anyway, thanks for the inputs.

Awesome blog post Ajay. I had no idea this could be done in Excel 2007. I suppose that now that I know this, its time to evolve past the ancient method of selecting cell ranges while creating formulas. This makes so much more sence and opens up new doors.

Thanks for providing

Calvin

March 26th, 2011 at 10:24 amExcelville.com

This is a unexplored area in Excel. Thanks for explaining.

May 17th, 2011 at 9:32 pmcool! never knew abt this.

1) I inserted the formula: =N(Table1[Revenue])

November 12th, 2011 at 4:51 pmbut always get an error- what am i missing here?

2) I just want to drag the formula which is a product of 2 cells in the table, for each month- but how?

Great explanation Ajay. I will keep visiting.

February 14th, 2012 at 7:48 amDoes anyone know how many levels of subtotal 9 (adding a column of numbers) are possible in Excel 2010?

I have 6 columns of numbers with subdivisions at 8-9 different progressively lower levels of detail, i.e. from a divisional level down to an individual cost center. When I put in subtotals for all 9 levels I lose the last two or three subtotals.

Thank you in advance for your help with this.

August 7th, 2012 at 9:17 amHow can you refer to the cell above the selected cell with these formula’s?

Thank you beforehand.

October 18th, 2012 at 9:27 amThere is another specialty is using table formulas.Even you have normal data you can convert that into table and then use formulas.The specialty is than you need not copy and pate the formula in the first rows to all other rows in the table in the same column.Excel table automatically does it for you.These are known as Calculated field.

December 18th, 2012 at 1:55 amWhen in table mode, when you scroll down, the headings become part of the upper bar and you don’t need to freeze the upper row- it does it for you and better!

December 18th, 2012 at 9:42 pmI’ve got a file to decode. One of the column contains formula table(B3) ex. How do I find which data is transformed to a table?

Thanks

August 14th, 2013 at 9:55 amThanks for the insight.It is explained in clear terms.

November 18th, 2014 at 5:26 amI have two table, one is Table #1 contain two cell eg D10 and E10. If I put a value in D10 eg : 30 , then I want it selects its value and put it in E10 from another table which also contain two column “M” and “N” . Please consider column “M” contains range eg : 0-1, 2-4, 5-10, 11-40. And second column “N” contains varriables corresponding to column “M”. Like as 2.6 for 0-1, 2.8 for 2-4, 4 for 5-10, 14 for 11-40. Please consider as per my logic I have to select 14.

December 11th, 2014 at 5:58 amis there a command character (like ‘$’) that can be used to keep references to the table from outside the table from moving?

July 23rd, 2015 at 4:52 pmis there a command character like the dollar sign that can be used to keep references to the table from outside the table from moving?

July 23rd, 2015 at 4:52 pmDoes anybody have an answer to the question that Peter asked back on October 18th, 2012:

How can you refer to the cell above the selected cell with these formula’s?Example:

Column A is “Sales Amount” Column B is “Sales Change”

When using a normal range I can enter in B3 formula “= A3 – A2”

When formatted as a table and I click in B3 then click in A3 then “-“ then click in A2: Excel generates =[@Sales]-A2

November 23rd, 2015 at 6:24 pmI’d instead expect to see something along this line: =[@Sales]- [@Sales – 1] where [@Sales – 1] is a reference to the row preceding the “active row”; likewise we could see [@Sales – 4], etc.

@Minimax and @farshid

The way I’ve found to do this is to wrap the table reference in an INDIRECT() function. So if TableName[ColumnName] is the equivalent of using e.g. A2:A100, INDIRECT(“TableName[ColumnName]”) is the equivalent of using e.g. $A2:$A100.

It’s not particularly elegant, but it works.

May 25th, 2016 at 3:48 am