A very useful feature in Excel is defined tables, introduced in Excel 2007. Earlier versions had this feature as well but it was then known as Excel Lists.
What can an excel defined table do for you? It will simplify your work with data sets, adding or removing data, filtering, sorting, readability using cell formatting, cell references, formulas and more. I will go through all this in greater detail, keep reading.
You are probably used to cell references like this one:
Creating a cell reference to a table column returns this instead, see picture below.
First the table name (Inventory) and then the column name enclosed with brackets [Price].
The amazing thing with structured references is that if you add or remove values to a table the structured reference stays the same, no need to update cell references. In other words, they are dynamic. Back to top
Formulas in a table
The following example demonstrates what happens if I type a formula in an excel table. I want to multiply cell E3 with F3 in cell G3, see animated picture below.
Excel creates these structured cell references in cell G3 if I type = (equal sig) and then click on cell E3, type * (asterisk) and then click on cell F3:
@ (at) means cell value on same row as formula.
Excel also calculates the remaining cells in column G automatically, see animated picture above.
Creating a reference to the entire excel table and headers returns this: =Inventory[#All]
A reference to data in table looks like this: =Inventory
A reference to a table column returns: =Inventory[Warehouse]
A reference to a column header only looks like this: =Inventory[[#Headers],[Warehouse]] Back to top
Select a cell in excel table
Go to tab "Design" on the ribbon
Hover over a table style and see your table change
If you like it click on it to select it
Click the black triangle to se even more table styles