Let's talk about how you can use range names for improved clarity in your formulas.
You might also come across range names when you work with your colleagues' files and you see some
names inside their formulas. And you're wondering, where they're coming from?
After this lecture, you're going to know. The default in Excel is that the name of the cell is its address.
If you take a look at the Name Box here the name of the cell is E3.
Here's the thing.
You can change that.
We can just click there delete what's in there and call this "Discount" and press enter.
Now this cell is also called discount.
There are some naming conventions here.
So you have to start with a letter or an underscore.
You can't start with a number.
You also can't have special characters in the name and you also can't have a space there.
So you can use an underscore instead of the space.
Now the moment this is called discount.
I don't need to reference the cell
by saying "=E3".
I can also reference it by saying "=discount". Type in "=discount" I actually see my name cell
in the dropdown here and I press enter.
It's my 10 percent.
You might find it easier to read formulas if you are using a name that's describing what is in the cell.
Instead of using a cell reference.
So for example here, if we want to calculate our final price. I can do price times one minus discount (=B5*(1-Discount))
close bracket press enter and send this down.
Now here's the other thing, I don't have to worry about absolute cell referencing. That name is fixed
to this cell.
The other great thing is that I can use it from any other worksheet.
So I don't have to come and reference back to this sheet and this cell.
For example, if I go to the referencing tab here type in "=discount" and press enter, I see my discount
value.
I can also jump quickly to that cell.
So let's say I'm in this sheet I'm going to go and type in discount in here and press enter, I jump directly
to that discount cell. In addition to naming individual cells
you can also name an entire range.
For example, this entire price list I could just call this "pricelist" and press enter.
So the entire range is named "pricelist".
Now if I want to get the average price, I can do SUM and let's do "pricelist" - you can see it right here -
divided by 4. Or, I can directly use the average function which is a function we're going to cover
in detail in the next section when we take a look at most useful Excel functions.
So here I would just have to type in my pricelist, close bracket, press enter and it's there.
And this is kept in memory so I can do this from any worksheet. If my numbers change here they're automatically
going to pull through the list.
So let's change 80 to 10.
Keep your eye on these values here.
Press enter, they changed. Ok, Control+z to go back.
What if you wanted to delete one of these existing names or update the name?
You can do that from Name Manager.
Name Manager is in the formulas tab, you see it right here.
You can define names also directly through the name manager if you don't want to do it from the name box
here.
But when you click on Name Manager you see the existing names in your workbook. You can just click on
one and press delete or click on edit and adjust the name and then click on OK. You see by default that
the scope of these names are for the workbook.
That's why you can use them from any sheet without referencing the sheet name.
You can also create names which their scope is just for that worksheet.
From the dropdown list you can select the sheet and then create your formula reference. Let's create
one name directly from the name manager here.
I'm going to click on new.
I want to call this back
and that name should apply for the whole workbook and it should be this cell here.
So this is kind of like my bookmark to always go back to the index sheet and then click on OK and close.
Now every time we click on this dropdown and we select back, we always go back to our index sheet.
OK so this is how you can use range names as bookmarks but also for clarity in your formulas.
Now I don't advise you to go overboard by naming everything. Just name the few important things that
you're going to be using throughout your Excel report or your Excel model but leave the other ones as
cell references.
So for this report if I was planning to use the discount rate in different places I would give this
a name but I will not give this range a name and use that in the formulas.
I'll just use the usual cell referencing.