What is Excel Name Range & How to Use it within Excel Formulas

Contact Us

Bhandup (W): Dreams Mall, Opp BMC Market, Nr Bhandup Station, Mumabi 400078.
Thane (W): G-03, SAI Sagar App, Beside Sai Babab Mandir, Kalher Thane.

+91 9870419968

info@advancedexcelcourse.com






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:

  1. In Cell A1 type 5 and in Cell B2 type 10
  2. 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

  3. Name Bar


  4. From the name bar replace A1 as "one"
  5. Select the cell B2 and replace the name bar B2 as "two"
  6. Now on cell C1 type the formula as =one+two you will get the desired result 15
Note:
  • You can also define the name by clicking on Define Name option under Formula Tab.

  • Define_Name


  • 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

Example 2:

  1. Create the below Sales report in cell A1

  2. A B C D E
      1   Month Reliance TATA Birla Total
    2 Jan 10000 20000 30000 =sum(Jan)
    3 Feb 20000 30000 10000 =sum(Feb)
    4 Mar 50000 30000 20000 =sum(Mar)
    5 Total =sum(Reliance) =sum(TATA) =sum(Birla) =sum(Month)

  3. Select the range A1:D4 and hit on create from selection
  4. You will get 4 options (Top row, Left column, Bottom row, Right column) click on Top row and Left column
  5. Now if you click on name manager you will see that excel has created 7 name range (Month, Reliance, Tata, Birla, Jan, Feb, Mar)
  6. Select Cell A5 and type Total
  7. Select Cell B5 and type the formula as =SUM(Reliance) ; in cell C5 =SUM(TATA) and in cell D5 =sum(Birla)
  8. 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)





  • facebook
  • google+
  • Youtube
  • skype
  • whatsapp
  • mobile
  • mail