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:
Step 1: Click on the Formula Tab.
Step 2: Click on ‘Define Name’. The new name dialog box appears.

Excel Tips and Tricks

Step3: 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 what ever you want.
Step 4: Select the scope of the defined name. You can choose for the full workbook or specified sheet.
Step 5:  In the comment section, you can write some thing 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.
Step 6: ‘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.
Step 7: Click Ok. You are done with defining the Names Range or Value.

Creating the Formulas using defined Named Ranges: We 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.

Excel Tips on Formulas

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 the 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.

(Visited 29 times, 1 visits today)

Meet the Author

Hi, I am Aniruddha, an avid blogger since 2008 who likes writing about new gadgets, smartphones and excel. You can find them at www.tech2touch.com. You can also follow me on Twitter @tech2touch or my Facebook page tech2touch. You can subscribe to my YouTube channel tech2touch. Enjoy reading and watching!!!

0 comments… add one

Leave a Comment

Shares