Named Ranges

A named range is an alias for a cell or a group of cells.  They make it easier to understand and maintain formulas.  

 

For example, I can refer to the Amount column as $G:$G.  Or, if I have named this range "Amount", the word "Amount" in my formula will refer to the same group of cells. 

 

On the SUMIFS page named ranges allow me to simplify a formula

from SUMIFS(data!$G:$G,data!$F:$F,D$13,data!$E:$E,$B16)

to SUMIFS(Amount,Year,D$13,Month,$B16)

Pivot table sample data.png

To create a named range: 

1) Select the cells with the data you want to total.  You can select a single cell, a group of cells, a column(s) or a row(s)

2) Select the Formulas menu

3) Select Define Name

4) Enter the name of your data range.  I used Amount in my example.  The Refers to box will be filled in with the column that you selected

5) Press OK

Repeat for each column of data that you will need.  In my example I created named range for Month and Year

Sumifs2_named_range.png

To view a named range, simply click on the arrow, as shown in the image to view the list of all named ranges. 

 

Click on one of the ranges and you will be shown the data that is linked to that name. 

 

Or, you can click on the Formula menu and select Name Manager.

named range1.png