Microsoft Office Tutorials and References

In Depth Information

**Creating a formula that references values in an Excel table**

Creating a formula that references values in an Excel table

In previous versions of Excel, it was a challenge to create named

ranges that included an entire column in a data list. Suppose

that you created a named range that encompassed the

existing cells in a data column, such as the cell range A3:A44; if you

added data to cell A45, you would need to change the cells in

the named range’s definition. Yes, there is a complicated way to

create a dynamic named range in Excel 2003 and earlier

versions, but you don’t have to worry about it in Excel 2013. All

you need to do is create an Excel table (as shown in “Creating

an Excel table” on page 52) and select the headers of the

columns that contain the data that you want to summarize in your

formulas.

Create a formula with an Excel table reference

1
Click the cell in which you want to create the formula.

2
Type
=
, followed by the function to include in the formula and a left

parenthesis; for example,
=SUM(
would be a valid way to start.

3
Type the name of the Excel table.

4
Type a left square bracket.

5
Click the name of the table column.

6

2

4

Type a right square bracket, a right parenthesis, and press Enter.

5

1

3

TIP To include more than one table column in a formula,

either hold down the Shift key, select the column header of the

first column to use in the formula, and then click another column to

select everything in the span between the two columns, or hold down

the Ctrl key and click the other column headers that you want to use.

Excel includes the references in the formula.