Named ranges – the opportunities for adaptation and reuse that referring to ranges of data conceptually rather than by cell references really changes your approach to how you use Excel. You immediately start by assuming that the data shape will change – and since it nearly always does – you are totally prepared for it.
How to use Named Ranges.
I always set these up for the most basic of spreadsheet. Why? Because every spreadsheet ends up being something more than you thought it would be. I hate referencing cells in formulas like this
How much clearer to say
So that you can follow along, please set up this data in your empty worksheet
It’s Simple to set up named ranges
You are going to create a named range ‘MyExpenses’, with the following definition
Here is how in Excel 2016 – Formulas – define name
Now, wherever you are in your workbook, you can just refer to ‘MyExpenses’ in any formula, rather than having to know where that is. So why is this important ?
Lets say that you need to refer to this range in a few places, and that range could shrink or grow. So you don’t really know whether its a2:a4 , or a2:a6 or whatever. By defining a named range, you only have to adjust the size of the range in one place and for as long as your other formulas refer to ‘MyExpenses’ they will always refer to the current definition of what that is.
So we’ve now fixed it that you only have to update 1 place when the definition of where to find ‘MyExpenses’ changes. That’s still prone to mistakes though – you may forget. Also if you have a number of ranges, you may change one and not the other. Nicer if somehow the data itself would define the range to be looked at. Well it can, of course.
Strangely enough, for something so obviously useful you would think there would be some kind of shorthand built into Excel to do this, but as usual there is not. However, since the definition of a Named range can contain any excel syntax, we can use excel functions to determine the size of the range we are to define.
Assuming that there are no blanks in our data column, we can use the COUNT() function to know how many cells have amounts in them and therefore where the range should end. Enter the formula below in an unused cell. The result should be 3
Here I’ve set $a$10000 as the maximum it could ever be. If my range started at $A$1, instead of $A$2 , i could have used $A:$A. Another Excel oddity.. If we add or subtract numbers to column $A, then the result will change to match the number of entries there are. There is a nicer, but more complex way to do this using the MATCH() function but we’ll go back to that elsewhere once we look at the uses for MATCH()
The other function we need is the very useful OFFSET(), which put simply allows you to define a new range that is a certain number of rows and columns away from a particular cell, and that has a particular number of rows and columns. Its usage is as follows
=OFFSET('complicated sheetname'!$A$2, how many rows down from here, how many columns across from here, number of rows, number of columns)
Using these two functions we can change the definition of ‘MyExpenses’ so that it adjusts itself to however much data is present. Now go back into Name Manager and change the definition of MyExpenses to be
=OFFSET('complicated sheetname'!$A$2,0,0,COUNT('complicated sheetname'!$A$2:$A$10000),1)
This will create a range that starts at $A$2 and will adapt its size according to the amount of data in column $a. You can check that it has worked by asking how many rows are in MyExpenses. Enter this into an unused cell. The result should be 3. Try adding additional data in cell A5, then deleting it again. You should see the result changing.
You can also check that the range has the correct number of columns. The result should be 1.
Now that you have verified that all this is working, goto another sheet and enter. The result should be $53.90
Why use Named Ranges.
These are fundamental to creating spreadsheets that are adaptable, reusable and error free . However most people don’t use them. It’s well worth the effort to set them up right up front even if you think you won’t need them..
Try to think up names for your ranges that will mean something to other people. The most successful worksheets are the ones that other people find useful.
The most successful dynamic ranges are those that need no maintenance and adapt to the data in the sheet.
For help and more information join our forum, follow the blog or follow me on Twitter