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.

table-formula-in-excel-total

How Does One Create a Table

insert-a-table-in-excel

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.
table-design-excel

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

total-optionSummarize 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’.

name-a-table

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.

using-subtotal-formula-in-an-excel-table

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]).

subtotal-function

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 ?


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

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=""> <strike> <strong>


Comments and Trackbacks

  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

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

  10. PC_BABE wrote:

    How do you revert the table back to just a spreadsheet

  11. AdamV wrote:

    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.

    Thank you in advance for your help with this.

  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

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel