Defining and Using Named Ranges in Excel 2010

Sometimes you want to name ranges of cells to make them easier to refer to in Excel for formula intensive worksheets.

I don’t typically use “named ranges” because I find it easier to see what the formula is referencing without them. However I recently learned that many people use names in their worksheets. It’s also a good thing to know how to look for “named ranges” in the workbook as well.

Defining Named Ranges

Once you have data that you want to create a named range for, doing it is easy.

Select the formula tab and click “Define Name”.

Click the button to the right of the “refers to” box at the bottom of the pop-up.

Then select the cells you want to have in your range, click and drag across the range of cells.

For this example I selected the data under the name in column A.

I will make one more named range as an example in the “Sales” column so I can demonstrate potential usefulness of named ranges.

Now if I want to do a calculation of average sales I can simply type the formula =AVERAGE(Sales)

The result will be the average of the “Sales” range that I just created.

Now you can use the Sales named range in any similar manner.

Another important point to know is how to find a list of all the named ranges in the workbook. This is simple yet very useful.

Just click on the name manager item at the top of the screen when in the formula tab.

You should get the following pop-up:

If you’re taking over a workbook from someone else this feature can be very valuable because you will be able to tell if they’re using named ranges and where they are located in the workbook.