CS 100 (Learn) — CS 100 (Web) — Module 04
NOTE: If your internet access is restricted and you do not have access to YouTube, we have provided alternate video links.
Note: This video transcript has been slightly modified. Corrections are marked with strikethrough, and alternative wording has been placed in [square brackets] to correct some of the awkward or confusing phrasing in the videos.
In this video we are going to learn how to use named cells and named ranges.
Let's go back to Bob's Widget Palace where the unit price of a widget is $5.
We want to [create] a spreadsheet which is going to keep track of customer orders, so I may have customer name and then we are going to say how many widgets they want to order and then we are going to calculate what the price of that order is going to be and then we are going to calculate the tax and then we are going to calculate the total amount on the entire order.
Let's enter a customer: Sally is ordering 10 widgets. Now what we normally [would] do right here is enter the quantity and then multiply that by the unit price, and I want to use absolute references because I want to make sure when I fill the cell down (or copy the cell down) we are referencing the correct unit price.
What I am going to do is show you an alternative approach (It is more of a complimentary approach).
Before I do this, I am going to take a bit of a detour. Recall that you can have multiple worksheets in a workbook, just like you can have multiple pages or sheets of paper in a regular book.
We were always on Sheet1 for the examples we have been doing, so technically the location of this particular cell is C4 within sheet one.
I am only bringing that up because we are going to see [Sheet1] in a second, [and] I don't want it to be confusing for you.
Instead of referencing C4 all the time, we can give that cell a special name. The way that we are going to do this is go to the formulas tab then click on a button called "define name".
Excel is kind of magic: it looked [at] the cells to left of [of the current cell] and saw that it has the content "unit price" [and guessed] that we want to call our cell "Unit_Price"... so we are going to stick with the name it automatically generated for now.
You will notice that what we are saying is that the words "Unit_Price" like that the name we are giving is actually going to be the name of this particular cell which is on sheet1 the absolute reference C4 (which is exactly what we want). I am going to click OK.
Now, something magic is going to happen. When I go to calculate this price for this quantity I am going to multiply the quantity and then I am just going to start typing "Unit_Price" so when I type the word [with "U"], what is going to happen is Excel is going to want to autofill [autocomplete] which is just like autocorrect or some other features you may [be familiar with] on your smartphones or other [applications].
It started to look for all functions that begin with "U": there aren't that many: UNICHAR, UNICODE, UPPER and you [may] also notice in there "Unit_Price": it is trying to guess that maybe we want to reference the cell "Unit_Price". That is fantastic.
So to complete this, the tab key is the most effective... so I press the [TAB] key and now the contents of this cell (the formula) is =C8*Unit_Price and it calculates it correctly. Of course, I am going to want to format that as a dollar amount and we are good.
I am also going to make these columns a little bigger to store some dollar values.
So now we have calculated, for this particular cell, if Sally ordered 10 widgets it is going to cost $50.
Let's enter a new cell for tax and we are going to enter HST which is 13% currently [in Ontario].
To calculate the tax I am going to want to give that particular cell a new name.
Now, instead of using the the button I used before, what we are going to do is bring our attention up to here to something called the name box... you probably haven't paid much attention to it.
Watch what happens when I move around the worksheet it actually just displays the currently active cell, so when I move to cell C5 it displays C5. Watch what happens when I move to C4: it shows me there C4 has a special name... its name is "Unit_Price", so when I go to cell C5 it is still called C5.
What I am going to do is go [to the name box] and just enter HST as the name of that cell.
So now, to calculate the tax I am going to take the current price and multiply that by HST: there is $6.50 of tax on that, and then the total of course is going to be the price plus the tax.
Now we can see how this is going: Joe comes in and orders 15 widgets and now I can autofill this amount this formula down and it is going to calculate Joe's order.
Let's have Frank come in, and he orders three and then a once again I can autofill this down.
There [are] couple of different advantages to using named ranges.
The first is that it makes our spreadsheet easier to understand and easier to follow. Note when I look at the formula for this particular cell, it says D10 times HST because I've given that cell C5 a meaningful name. It makes the formula easier to interpret, so if someone else is looking at the spreadsheet it is easier for them to figure out what is going on.
Also, you will note we didn't have to worry about absolute references because it was built-in when it defined HST: it refers to the absolute reference $C$5 so we don't have to mess around with absolute references as much as we normally would.
We can look at our spreadsheet see some meaningful names and understand what is going on.
You can also assign a name to a range. For example, I might select these three cells and I might now name that range "Total_Sales".
You don't need to use underscores, [but] I typically do because I am a nerd and I like underscores... you do not have to use underscores in your spreadsheets.
What I want to show here is the total sales, and what I am going to do is say "=SUM(" and I am going to start writing total sales, and there we go... Notice when I typed it, it also showed on the screen the highlighted range I was referencing. We can now see that the total sales for Bob's Widget Palace is currently one $158.20.
I am going to show you one quick thing: earlier we talked about moving cells and, the same thing will apply when I move this cell here you will notice currently $C$5 is HST, but maybe I want to move the HST somewhere else... now cell F2 is HST. Note that these cells did not appear to change at all but still work.
If I change the HST rate (the new government comes in and says we don't want a high HST rate we are going to do 4%) then everything changes correctly: it is still referencing HST but HST is now in cell F2. When I move cells, the names of the cells move with them.
The last thing I am going to show you is what happens if you have a new customer. Billy comes in and wants to order a hundred widgets... that is a big order so we are all quite excited about that: it is going to bring in over $520 to the store, but we have a problem. The problem is the total sales do not reflect Billy's order. So what is the problem? When I look at the cell it still shows "Total_Sales" but "Total_Sales" was only those three cells... that was the range that I defined for those three cells but when you are using names you have to be careful.
Usually you only use names on a fixed size so you don't have this problem but if you are going to use it for a dynamically sized area like this we are going to need to be able to change the size of the range. To do this, I am going to go back to [the] formulas [ribbon] and I am going to use something called the "name manager". There it shows me all of the areas in my spreadsheet that have a name. I am going to go to total sales and edit it. It shows me the range, and I am going to now highlight the new range and now it says total sales is F8:F11. I am going to click OK [and] close and now total sales reflects Billy's big order.
That is using named cells and named ranges in Microsoft Excel.