How to define named ranges in Excel 2010

If you have a range of cells that hold a specific type of data e.g. Sales, No. of students etc. then you can give that range a meaningful name. Later if you want to refer to the range you can do that by specifying its name instead of using its cell reference.

In the example below we have two columns, Month (column A) and Sales Value (column B).

If we want to sum the total sales for the 12 months then we will use this formula:


Alternatively we can give the range B2:B13 a name like this “SalesValue” and then use the sum formula as follows:


The names will make it easier for you to refer to a range and to make your formulas more meaningful.

How do you define a named range?

1. Select the range that you want to name.

2. In the Formulas tab, Defined Names group click Define Name. The New Name window will open.

3. In the Name field enter the name you want to give to your range.

4. In the Scope combo box, if you want this name to be known to other sheets in the workbook, select Workbook. If the name will only be used in the current worksheet, then select the name of the worksheet.

5. In the Refers To field make sure that the required range is specified.

6. Click OK when you are done. Your defined name is now ready to be used in any formula.






  1. […] In a previous post I explained how to define named ranges in Excel 2010. […]