Tuesday, April 24, 2012


How To Reference Data In a Table Using Excel

Referencing Data in a Table Using Excel: Beginning with Excel 2007, you can designate a range to be a table by using the Insert➜Tables➜Table command. Tables add a few new twists to formulas.

When you enter a formula into a cell in a table, Excel automatically copies the formula to all the other cells in the column — but only if the column was empty. This is known as a calculated column.

If you add a new row to the table, the calculated column formula is entered automatically for the new row. Most of the time, this is exactly what you want. If you don’t like the idea of Excel entering formulas for you, use the SmartTag to turn off this feature. The SmartTag appears after Excel enters the calculated column formula.

Excel also supports “structured referencing” for referring to cells within a table. The table in the accompanying figure is named Table1.

How To Reference Data In a Table Using Excel

You can create formulas that refer to cells within the table by using the column headers. In some cases, using column headers may make your formulas easier to understand. But the real advantage is that your formulas will continue to be valid if rows are added or removed from the table. For example, these are all valid formulas that use table references:

  • =Table1[[#Totals],[Income]]
  • =SUM(Table1[Income])
  • =Table1[[#Totals],[Income]]-Table1[[#Totals],[Expenses]]
  • =SUM(Table1[Income])-SUM(Table1[Expenses])
  • =SUMIF(Table1[State],”Oregon”,Table1[Income])
  • =Table1[@Expenses]

The last formula uses an each-at symbol (@), which means “this row.” This formula is valid only if it’s in a cell in one of the rows occupied by the table.


About bench3 -

Haja Peer Mohamed H, Software Engineer by profession, Author, Founder and CEO of "bench3" you can connect with me on Twitter , Facebook and also onGoogle+

Subscribe to this Blog via Email :