What is Excel Name Range & How to Use it within Excel Formulas.
Excel Name Range is nothing but, defining a name to a Cell Or Range of Cells. Lets look at the examples below to understand it in a better way.
- Example 1:
- In Cell A1 type 5 and in Cell B2 type 10
- Select the cell A1 and look at the formula bar, it will show number 5 and the name bar (on the lefthand side of a formula bar) will show A1
- From the name bar replace A1 as "one"
- Select the cell B2 and replace the name bar B2 as "two"
- Now on cell C1 type the formula as =one+two you will get the desired result 15
- You can also define the name by clicking on Define Name option under Formula Tab.
- Name Manger : Helps us to create, delete, edit, find all the name used in a workbook
- Define Name: Helps us to define name to a range or a cell
- Use In Formula: Helps us to use the defined name in a formula
- Create from Selection: Automatically define name to a set of range
- Create the below Sales report in cell A1
- Select the range A1:D4 and hit on create from selection
- You will get 4 options (Top row, Left column, Bottom row, Right column) click on Top row and Left column
- Now if you click on name manager you will see that excel has created 7 name range (Month, Reliance, Tata, Birla, Jan, Feb, Mar)
- Select Cell A5 and type Total
- Select Cell B5 and type the formula as =SUM(Reliance) ; in cell C5 =SUM(TATA) and in cell D5 =sum(Birla)
- In the same way select cell E2 and type the formula as =SUM(Jan) ; in cell E3 =SUM(Feb) ; in cell E4 =SUM(Mar) and in cell E5 =sum(month)