Easy way to reference data tables and make formulas within a data table. This allows you to create formulas that apply to the entire table, copy easily, and are more robust than traditional formulas; these are called structured references.
First, make sure your data is formatted as a table; if it's not, read this tutorial on how to create and manage tables in Excel
Create and Apply Formulas to Tables in Excel
Reference Table Data From Outside the Table
Reference Different Sections of the Table
Column Headers with Special Characters
This illustrates the basics of using formulas with tables, keep reading for more specifics on things that you can do with formulas and tables.
All table column references MUST begin and end with an opening and closing bracket []. If they do not, this will not be interpreted as a table reference.
References only data from the current row, the same row in which you are entering the formula.
There are a couple ways Excel might write this, including: #This Row and @. However, you should use the most generic form when creating your formulas, which is this below:
=[@name of column]
"Name of column" should be replaced by the name of the column to use but everything else remains the same.
References all of the data for a particular column.
=[name of column]
To reference data from a table in a formula that is outside of the table, you need to specify the name of the table from which you want to get the data.
Here is the syntax:
=TableName[ColumnName]
If your table is names SalesData and you want to get the total of the sales from the Sales column, you would use a formula like this:
=Sum(SalesData[Sales])
Look below to figure out how to change the name of the table so it's easy to identify.
There are special ways to reference specific parts of a table. Each reference here must begin with the pound sign #.
Remember, all table references must also begin and end with an opening and closing bracket [ ].
References everything in the table including the data, headers, totals row, everything.
=[#All]
References only data rows in the table.
=[#Data]
References only the table's headers.
=[#Headers]
References only the Totals Row in the table.
=[#Totals]
Referencing columns is pretty easy once you understand the basic syntax of using brackets and so forth. However, there can be complications when you have certain characters in the headers of your columns.
To avoid remembering a long list and what to do, if you have any issues, just select the header names from the drop down list that appears when you enter them into a formula and Excel will do everything for you.
The default table name will be something like Table1 and that is not very descriptive.
When you reference tables within the worksheet, it is helpful to give them useful names like: Sales, Hours Worked, etc.
The new name is the name that you will now use to reference the table within any formulas in the worksheet.
Formulas that reference tables in Excel are called structured references and these can get very confusing very quick. This tutorial shows you the basics of structured references and how to use them. Look to some of our other tutorials on tables to learn more on this topic.
Download the attached Excel spreadsheet to see what we did in this tutorial.