Using Formulas with Tables in Excel (2024)

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

Sections:

Create and Apply Formulas to Tables in Excel

Reference Data in a Table

Reference Table Data From Outside the Table

Reference Different Sections of the Table

Column Headers with Special Characters

Change the Table Name

Notes

Create and Apply Formulas to Tables in Excel

1. Type an equal's sign where you want the formula.
   
2. Now, we are using a table, so we reference the columns in a different way.
   Type [ and Excel will present a list of the columns in your table that you can use in your formula.
   (table references must ALWAYS begin and end with an open and a closing bracket [ ])
   
3. You can click one of the options from the drop down to select the entire column of data or you can type the columns name OR if you just want to reference the current row type an @ symbol and then the column name. Also make sure to put a closing bracket ] after the name of the column.
   
4. Now, I want to divide the Sales by the Goal to see how much of the goal was completed.
   I type a division sign / and then open bracket [ to reference another column in the table and then @ to make the reference for the same row and then type the name of the column Goal and then close the bracket ]
   
   
5. Hit Enter and the formula will automatically copy down the entire table.
   
6. Just add the necessary formatting and that's it.
   

This illustrates the basics of using formulas with tables, keep reading for more specifics on things that you can do with formulas and tables.

Reference Data in a Table

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.

Reference the Current Row

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.

Reference the Entire Column of Data

References all of the data for a particular column.

=[name of column]

Reference Table Data From Outside the Table

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

1. Type =SUM(
   
   
2. Start typing the name of the table and you should see it appear in a drop down menu.
   
3. Finish the name of the table and then type an opening bracket [
   
   
4. Select the column you want to sum or just type it.
   
   Don't forget to put a closing bracket after the name of the column.
5. Hit Enter and that's it.
   

Look below to figure out how to change the name of the table so it's easy to identify.

Reference Different Sections of the Table

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

Entire Table

References everything in the table including the data, headers, totals row, everything.

=[#All]

All Data Rows

References only data rows in the table.

=[#Data]

Headers

References only the table's headers.

=[#Headers]

Totals Row

References only the Totals Row in the table.

=[#Totals]

Column Headers with Special Characters

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.

Change the Table Name

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.

1. Select a cell within the desired table.
2. Go to the Design tab and look to the left side and change the value under Table Name:
   
   

The new name is the name that you will now use to reference the table within any formulas in the worksheet.

Notes

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.

Similar Content on TeachExcel

Create and Manage Tables in Excel
Tutorial: Here, I'll show you everything you need to know to get started using tables in Excel; how...