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

- Type an equal's sign where you want the formula.
- 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**[ ]**) - 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. - 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**]** - Hit Enter and the formula will automatically copy down the entire table.
- 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.

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

- Type
**=SUM(** - Start typing the name of the table and you should see it appear in a drop down menu.
- Finish the name of the table and then type an opening bracket
**[** - 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. - 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.

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.

- Select a cell within the desired table.
- 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.

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

Complex Structured References (Table Formulas) in Excel

Tutorial: How to use complex structured references, table formulas, in Excel. If you don't already...

Tutorial: How to use complex structured references, table formulas, in Excel. If you don't already...

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

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

3 Slicer Tricks for Pivot Tables in Excel

Tutorial: 3 ways to better use Slicers for Pivot Tables in Excel. These tips will show you how to a...

Tutorial: 3 ways to better use Slicers for Pivot Tables in Excel. These tips will show you how to a...

Understanding Formulas and Functions in Excel

Tutorial: In this tutorial I will cover the basic concepts of Formulas and Functions in Excel. A for...

Tutorial: In this tutorial I will cover the basic concepts of Formulas and Functions in Excel. A for...

Display all Formulas at Once in Excel

Tutorial: How to view all of the formulas at once in Excel so that you can troubleshoot the spreadsh...

Tutorial: How to view all of the formulas at once in Excel so that you can troubleshoot the spreadsh...

Introducing Logic into Formulas and Functions in Excel

Tutorial: In this tutorial I am going to introduce the idea of Logic in Formulas. A Logic test is a ...

Tutorial: In this tutorial I am going to introduce the idea of Logic in Formulas. A Logic test is a ...