Hello and welcome to mr Tompkins edtech, and my series on Microsoft Excel. In this video
we are going to be looking at naming ranges. If you are just dropping in, you might like
to go watch this intro video first, which will explain a bit more about this series
and tell you where you can down load the excel workbook that accompanies this series. If
you’ve already done that, then lets get into it.
Now a range in excel normally refers to a rectangular area on the spreadsheet and is
defined by its top-left and bottom right cells, separated by a colon. So this table here is
referred to in Excel as the range C10:G16. Ranges are used as the input value in many
of the functions used in excel. The =SUM() function adds up all the values in a given
range, so if I wanted to add up these 8 numbers, I could type in the formula =SUM(range) and
then inside the brackets I’m going to write the range that I want to sum. So in this case
its =SUM(topleft to bottom right) which would be =SUM(B3:C6), which you can see gives me
the value 27. Now there are lots of functions in Excel which use these ranges as the input
value. Another example is =count(), which is going to count the number of values in
the input range, so if I type in =count(B3:c6) its going to count the number of different
cells in the range, giving me the answer 8.
Normally in a big spreadsheet in excel, we are going to have areas of the sheet which
we use for different types of information, and so we might end up having lots of different
ranges that we are going to want to use in our formula. Now these ranges are not so handy
for humans to remember, and if you are anything like me then when you’re looking around
your spreadsheet a few days or weeks after you made it, you will struggle to remember
what all these different ranges were for, and why you needed them in the first place.
Now to make your spreadsheet a little bit easier to follow, we can give these ranges
a more meaningful name and then use those names in any of the formulas and functions
that require a range, instead of the top-left/bottom right corners that excel likes to use.
So lets give this group of numbers a name. I’m going to call them “numbers”, and
I can do that by selecting the range and then tapping into this box in the top left hand
corner of the sheet that normally displays what cell I’m currently focused on. I’m
going to type the name I want to give this range – so lets type in “numbers”. Now
if I tap somewhere else on the page, and then come back to this same box up here and type
“numbers” in again, it’s not going to rename this new cell as “numbers”. Instead
its going to refocus excel on the range I gave it before and you can see it jumps straight
back on this range. Now I have defined this range as “numbers”,
I can use it in any formula that expects a range as part of its input values. So instead
of writing =sum(b3:c6) like I did earlier, I can now write =sum(numbers) instead. I can
also use it in loads of other formulas. =count(numbers) is going to tell me how many
cells there are in the range, so gives me 8.
=average(numbers) is going to give me the mean average of the 8 different numbers
=mode(numbers) gives me the modal value Ok, lets take a look at the workbook that
accompanies this series. (If you haven’t already downloaded it, you’ll find a link
down below). Open the book on “tip1” and lets have a look at this table up here at
the top. You can see this table has some basic facts
about a group of students, so let’s name it “students”. First select it by dragging
from top left to bottom right. Now lets type “students” into the name box up here.
Once you’ve named a range, retyping “students” or selecting it from this drop-down list here
will automatically focus excel on our table here.
Now it might not be immediately apparent why this is a useful thing to be doing on your
spreadsheets. But basically the benefits are that it’s a whole lot easier to remember
and it means you can quickly refer to your range when writing formulas without the need
to keep going back to look at what the cell range is. It also makes your formulas so much
easier to read and understand. Lets take a look at a few more examples…
Now I have defined it, I can write “students” in any excel function that expects a range
as its input and get a result. So for example =columns(students) gives me the answer 5 because
the table students has 5 columns. =Row(students) gives me the an answer of 7 for the 7 rows
that make up the table. =index(students,4,2) is going to read out the value stored in the
4th row and 2nd column of our students table, so returns the value “buzz”. If I edit
this to 5,3 instead, it.s going to return “Inboots”.
So looking at these formulas, you can see that they make a lot more sense than the equivalent
formulas that uses the cell references. And as things in Excel tend to get very complicated
very quickly, especially when we start introducing some of the more advanced techniques from
later in this series, its really going to make a difference.
I’d like you to have a go at task 1 down here in the workbook now, so you’ve had
some practice naming a range and recalling ranges. Just follow the instructions here,
and work directly in the page. You can pause the video and go do that now. Once you’re
done, come back and press play again…
OK, so I hope you managed to name the behaviour table and use those names to focus on particular
areas on the spreadsheet. Now you can see from the drop down list there are a whole
bunch of other names that refer to different areas on different pages of this workbook.
For a big spreadsheet you might end up using a lot of these name, so Microsoft have included
a tool for helping you manage all these names, which spookily they’ve called the “Name
Manager”, which you can find in the formulas ribbon. Opening this, you can see it opens
a dialog box which lets you create new names and also to edit and delete existing ones.
You can even use the name manager to create a named range in a totally different spreadsheet.
Just click “new..”, give it a name and then select your range, which can be on the
same page, on a different worksheet in the same workbook or another workbook entirely.
So in this first lesson, we learned how to give a range on a spreadsheet a name, and
how we can use that name in other formula to reduce their overall complexity and make
them easier to read and understand. We’ve taken a look at the name manager, which helps
keep track of all these names and to add more, edit or delete names you made earlier. If
you are happy you understand all about naming ranges, tick this box down here to mark you
have completed this section of the course.
In the next lesson we are going to take a look at the =vlookup() function, which is
a very powerful tool for merging two datasets together. By the time you have worked through
the whole series, you will have all the skills you