Working with named ranges
One of the extremely useful things that Microsoft Excel allows is to assign desired name to ranges, even variable ones (which means that they need to be defined only once). This feature comes in very handy in designing dashboards and is one of the best practices for dashboard creation.
What does it mean?
It simply means that one can select a specific area (containing data) inside a sheet and give it a specific name, which is more intuitive. Let’s take an example to explain this. In the picture below, we have placed drug names and their corresponding sales data in the range ‘B2:C6′ on ‘Sheet 1’ of an Excel workbook.
To name this range, we simply
- Select the area/range (‘B2:C6′)
- Go to the ‘name box’ (this is the area just before the formula bar; see picture below)
- Enter a name of choice, and hit Enter/return key.
- Thats it!
And now we can refer the range, ‘Sheet1!B2:C6’ as ‘Sales_Database’. To explain more, what this means is that now, instead of writing the formula
VLOOKUP (“P-taxel”, Sheet1!B2:C6,1,0) - Let’s call it Option 1
We can write
VLOOKUP (“P-taxel”, Sales_Database,1,0) – Let’s call it Option 2
An obvious question is, why do we need to do all this?
Well, the answer is, it gives more flexibility. lets say, we had the same formula as in Option 1 above at 20 different places in the workbook. What would happen if one fine morning we realized that our data set need to change in terms of the size? So, from the existing ‘B2:C6′ it now needs to be ‘B2:C100′. Obviously, to make the formula work as usual, we need to change the range reference of all the formulas by editing them manually. So the new formula at all those places would have to be:
VLOOKUP (“P-taxel”, Sheet1!B2:C100,1,0)
And what would happen if we need to change it again… daunting task… isn’t it!
So how does named range help here?
In case of named range, when something like this happens, all we need to do is change the reference of the named range i.e. no need to revisit any of the formulas at all.
To accomplish this I will go to the ‘formula tab’ on the ribbon, click on ‘name manager’, select the named range that I need to modify from the list of named ranges, go to the field which says ‘refers to’ and change the reference from ‘Sheet1!B2:C6’ to ‘Sheet1!B2:C100’.
The picture below displays all the steps you need to follow to do this:
So this solves a lot of things, but we still need to manually change the reference every time new data is added. So is there a better way to do this?
Yes, there is. It’s called dynamic named range and can be made using two excel formulas:
- OFFSET – you would remember that this formula returns a reference based on the parameters defined
- COUNTA—this one returns the number of non blank cells in a range
To start, I go to the ‘formula tab’ on the ribbon, click on define name, enter the desired name in the ‘name field’ and enter the following formula in the ‘Refers to’ field.
=OFFSET (Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$1048576),2)
Let’s see how the different parameters fit in this formula and how they work:
As you can see in the picture above, the real trick is in keeping the ‘height’ parameter variable by counting the number of non-blank cells (drug names) in the ‘column B’—the obvious assumption here is that the column B would not have blank cells.
Similarly you can make it even more dynamic by keeping the ‘width’ variable as well. How? Simply replace the width parameter by a similar COUNTA formula:
=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$1048576),COUNTA(Sheet1!B2:XFD2))
So now you can keep adding data in rows and columns without bothering about your formulas.
We’ll be using Named ranges a lot in our dashboards to make them dynamic as shown above. To see this concept in action you can download and explore our SphInX dashboard from the Crab Projects section.
Feel free to reach out to us at query@crabsheet.com in case you have any queries regarding the concept of Named Ranges and we would be happy to address them.