Skip to main content

How to Use a Named Range in Excel


In the previous part, you saw how to define and apply Named Ranges. In this part, you'll learn how to use the Named Ranges you have just set up.

Using Named Ranges

The spreadsheet you have created so far looks like this:
The spreadsheet so far
We have a Named Range in Cell B5 Called Monthly_Totals, and a Named Range in Cell C5 Called Monthly_Tax. We can use the Named Ranges to deduct the Tax from the Monthly Totals. But we need to set up two new Named Ranges. One for cell B5 and one for cell C5.
You might question the need to set up two more Ranges, on the grounds that we have just set up two Named Ranges in cell B5 and C5.
Strictly speaking, we don't need the new names. To deduct the Tax from the Monthly Total using Named Ranges we could just do this:
=Sum(Monthly_Totals) - SUM(Monthly_Tax)
And it would work. Excel would take one from the other. (The following, however, would not: = Monthly_Totals - Monthly_Tax.)
But if we set up two more Named Ranges, we can just use the single totals, instead of telling Excel to use a Function to add them up first.
So, to define two new Names, do the following:
  • Click inside cell B5
  • From the menu bar, click on Insert > Name > Define
  • When the dialogue box appears, click inside the top text box and type in Monthly_Result. (Don't forget the Underscore!)
  • Click OK
  • Click inside cell C5 and do the same. This time, though, type in Tax_Result as the Name.
  • Your Define Name dialogue box will then look like this one:
Four Named ranges have been defined
Click the OK button when you're done.
Now that we have the Names defined, we can enter a formula and Apply the names into the formula. First, amend your spreadsheet so that it looks like this one:
As you can see, a new label has been added, along with the formula in cell B7: = B5 - C5.
Now that we have a formula we can replace the cell references B5 and C5 with the names we set up. So click on B7 and do this:
  • Click on Insert > Name > Apply
  • The Apply Names dialogue box appears
  • Click Monthly_Result select it
  • Click on Tax_Result to select it
  • Click the OK button
  • Excel replaces your cell references with the Names
  • Your spreadsheet will then look like this one:

The cell references in cell B7 have been replaced with our Named Ranges, making the information easier to read.

And that's enough of Named Ranges. We'll move on to Pivot Tables.
Labels:

Comments

Post a Comment

Popular posts from this blog

PRATYUSHA HOME

Hi interesting readers, Prathysha NGO, Chennai. Bala gurukul orphange, chennai. Pratyusha support, Hyderbad. Krr oldage home, Vizag. Disa foundation.......................................................... so on..................... Join hands with us for a good cause. Thank you all. Contact  us on our  whatsapp no:  8939374254    for the more details. You can pay through Paytm scanner. for each donor we will give an online PDF book as a gift. Thank you Pratyusha Home
Post a Comment
Read more
Javascript Tutorials for the Beginner - Welcome to the Home and Learn course for Javascript. Although it is aimed at complete beginners to the subject we do assume that you have some knowledge of HTML and a little CSS. You don't have to be an expert, though. Just the basics will do. (If you're not sure about the HTML then see our Web Design course.) You don't need to buy any software for this Javascript course - you probably already have everything you need to get started.   > Section One: Getting Started 1. Software needed 2. Our Javascript Templates 3. A little word about debugging Home Page > Section Two: The Browser 1. A First Script 2. Javascript Tag Placement 3. The Browser Object Model 4. Browser Window Methods 5. The Document Object Model > Section Three: Javascript Programming Basics 1. Javascript Variables 2. Math Operators 3. Operator Precedence 4. IF Statements 5. IF ... Else 6. Comparison Operators 7...
Post a Comment
Read more
Sorting Images in Windows 7  -In the previous section, you learned how to add information to an image. In this section, you'll se what you can do with all this information. This comes under the topic of Sorting. First, though, change your View slider from  Tiles  to  Details : When you arrange the window to Details view, your screen should look like this one: We now have a list of images with the Name, Date, Tags, Size and Ratings showing. These headings can all be used to sort your images. Click on the Ratings, for example, and you'll see a dropdown box: You can choose to display only images with 1, 2, 3, 4 or 5 stars. All you need to do is to check each box. The ones not checked will then be hidden: Here, we're choosing to display only 1 and 2 star images, perhaps with a view to deleting them. (To delete an image, right-click. From the menu that appears, select  Delete . Or choose  Rename  to change the name of the image....
Post a Comment
Read more