# Table Formula in Excel (Something I didn’t Know Till Yesterday)

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]

### 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 ? ## What Do You Think ?

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

1. sam wrote: 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

2. Ajay wrote: @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.

3. Juan wrote: Never used.

Thanks for the tutorial!

4. sam wrote: @Ajay,
a 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

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

Regards,
Ajay

6. sam wrote: 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

7. dhoff wrote: I 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?

8. Ajay wrote: @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,

9. sam wrote: Correction regarding my earlier comment on DSUM etc

10. PC_BABE wrote: How do you revert the table back to just a spreadsheet Useful 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

12. Alex Kerin wrote: I 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.

13. farshid wrote: how 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])
after copy or drag this formula horizontally it change to :
sum(tblSales[Stock])

14. Milind wrote: 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)

If 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.

15. Calvin wrote: 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
Excelville.com

16. Ramesh wrote: This is a unexplored area in Excel. Thanks for explaining.

17. tinu wrote: cool! never knew abt this.

1) I inserted the formula: =N(Table1[Revenue])
but 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?

18. Rita Cassidy wrote: Great explanation Ajay. I will keep visiting.

19. Bruce wrote: Does 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.

20. Peter wrote: How can you refer to the cell above the selected cell with these formula’s?

Thank you beforehand.

21. VIJAYKUMAR wrote: There 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.

22. Shalev wrote: When 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!

23. Maka wrote: I’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

24. Rudra wrote: Thanks for the insight.It is explained in clear terms.

25. Rajesh singh wrote: I 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.

26. Minimax wrote: is there a command character (like ‘\$’) that can be used to keep references to the table from outside the table from moving?

27. Minimax wrote: is there a command character like the dollar sign that can be used to keep references to the table from outside the table from moving?

28. Geoff H. wrote: Does 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
I’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.

29. Alex wrote: @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.

30. Rannilas wrote: This post is 7 years old today and it was extremely helpful as I just discovered this feature myself recently. Cheers.

31. Jay wrote: Thank you so much. Very useful for helping with our GIS CSV data. Data is typically 30-50 columns always have same header names however they do not always appear in the same order depending on which analyst or automated tool created the csv data.
Now we can refer all of our sum, count, etc, and graphing formulas to table headers without having to continually update formulas with column letters.

32. TL Norman wrote: Very informative. Only suggestion would be to include how to

1. Reference specific cell in table
2. Reference specific row in table
3. Determine the row number of the table (not of worksheet)