You are probably aware of how formula use cell references in Excel. A1 refers to a single cell, A1:A5 refers to a column of cells and A1:G1 refers to a row of cells.
If you have a formula in a table, or a formula that refers to a table then the way that the formula references the cells is different.
Formula within a Table
=[@[Cost 1]]+[@[Cost 2]] Where Cost 1 and Cost 2 are the Column Headings from the table. As you can see this is more meaningful than “A2 + B2” The @ symbol is used to indicate the current row.
As mentioned before, that formula will copy down and fill the column.
Making use of the Total Row in a table will also use the column headings in the formula. =SUBTOTAL(109,[Cost 2])
Referencing Table Values from outside the Table.
If you reference a value in a table in the same row as that value, then the formula will use the @ symbol again. The Table name will be placed in the formula as well. As you can see this makes it clear what the formula is referring to. =Table1[@[Value 4]]
If the formula references a different row, then the formula will be in standard referencing format.
You can also use a formula to reference a whole column of the table.
=Table1[Value 4] this will return all the values in the column and the formula will SPILL into the cells below.
=Table1[[#Totals],[Cost 2]] Will reference the value in the total row for the specified column.
=Table1[[#Headers],[Value 3]] Will return the column header value.