Named ranges

Overview 

This is an extract from our pre-course videos for our renewable energy and infrastructure project finance modeling course.

Video

Video Transcript 

In the previous lesson we talked about a named range or naming cells. In this lesson we will learn what a named range is, some common name ranges, their benefits and how to add, edit and remove them. For those of you who have done some modeling, you may know that we repeat the use of certain numbers and calculations a lot. For example, the number of months in a year or the tax rate. So if we use these numbers a lot, it’s sometimes nice to see a better reference in the formula to that number than cell B536. Instead, it may be helpful to have say, tax rate written out in the calculation. Exploring this example further, let’s say I’m calculating my tax using a 30% tax rate based on earnings before tax, which is often called EBT. instead of writing tax equals C 10 times B536 equals 30, I could choose to have tax equals $100 times 30% equals $30. But as you have learned, we don’t like hard codes and the problem with hard codes is that no one can see where the numbers come from.

So what about instead we have tax equals EBT times tax rate equals $30. You can see how this can be really clear to the user. We can name a particular cell or a number of cells and use that reference instead of B536. This is naming a range just where range is a fancy word for an individual cell or a group of cells, or name ranges are really just named cells. There are two main reasons we use name ranges. Number one: as seen, it can make a model much easier to understand. It’s one of the core principles of best practices modeling. Number two: name ranges are also required for VBA macros and there’s more than that if you take class with macros included.

But Haydn, is there a limit to how many name ranges I can use?

Well, I’m not sure if there is a technical limit per say, but it’s mostly irrelevant to me. Just imagine for a second that we have named every single cell in the model. I don’t know about you, but remembering the name of hundreds or thousands of cells in a model is well beyond my mental capacity. So we here at Pivotal180 prefer to limit name ranges to just a few cells. They’re there to make users job easier after all. So limit them perhaps to 10 in a model. So what are some common name rangers? Well, they’re the, say, 10 references that you use all the time. For example, the number of days in a year or the months per year or our tax rate. Let’s say you have a monthly model and you’ve used annual revenue inputs, you’ll consistently be dividing by 12 months in a year to determine the monthly cashflow. Or if you have a quarterly model, then you would divide annual cashflows by the number of quarters per year and so on and so forth. You can see some common ones we use on screen and these are the ones that we’ll use in our courses and we’ll cover those as we get to them. And so then how do I create a named range? How do I add one? Well, it’s actually really simple. I just click on one cell or a group of cells and then navigate to the left of my formula bar and type in the desired name range. In this case cell D2, and I type in days, underscore, year. If I now select D2, you can also see that it says days per year in the formula bar. It hasn’t overwritten D2, this is just an alternative to calling it D2.

I can also edit and delete the name ranges from the name manager in Excel, and you can access this under formulas, name manager. All the shortcuts are shown on screen. Here, you can select the name range and click edit to change its reference or name or simply delete the range itself if it’s not used. The name ranges are a great way to help a model user understand calculations.

But be careful. Don’t add too many for fear of confusing me or worse, your boss. As we get further into the course, we will take a slightly deeper dive into name ranges, and I look forward to that.