Using Formulas in Excel using Named Ranges

Constructing the formulas sometimes very complicated especially when you use several functions in the same formula or multiple argument for a single function. For this, if you use named ranges or constants which refers to a frequently used value or constant. A ‘Named range’ is a name you assign to a group of related cells. Using named constants and named ranges can make creating formulas and functions easier by enabling the use of names that clearly identify a value or range of values. The named ranges or named values also helps you to understand the formula to other easier. Let us have a look into how to define a named ranges and values in excel and how to use them in Excel formulas.

Defining Named Ranges and Values in Excel

• Click on the Formula Tab and then  Click on ‘Define Name’.  The new name dialog box appears.
• Type the name you want to assign it to a range or constant. Ideally, it will be good practice to give a name which related to the value it refers to, though you can name it whatever you want.
• Select the scope of the defined name. You can choose for the full workbook or specified sheet.
• In the comment section, you can write something which describes your Named range or value. It is optional. It is better to write as it will be helpful when you refer this range after some time as you might forget what it refers to.
• ‘Refers to’ section you can enter a constant value or a range of cells. You can use browse icon to select the ranges or even you can manually enter it.
• Click Ok. You are done with defining the Names Range or Value.

Creating the Formulas using defined Named Ranges

We have already defined the range with a name. Now we will how to use that in the Excel functions or formulas. Use the functions or Formulas as usual. But instead of writing the range, type the name of that range you have defined. Below is the simple example which will give you better idea on how to use Named ranges in Excel Formulas.

Below is the Sample Excel data which has Smartphone names and its price.

Here I have defined the range from B2 to B5 as ‘PriceRange’. Now for using this range in SUM function look as mentioned below.

=SUM(PriceRange).

Note: When you are using the named range in Excel Formula, as soon as you type first letter of your named range, you will see a drop down list which shows the named ranges. You can use that for easy to type the names.