9 reasons to use named ranges in any spreadsheet, Google Sheets and the like
How to build spreadsheets faster, with fewer bugs, and change them later with ease
UPDATE: new and improved version of the post on the top benefits of using named ranges in Google Sheets.
—
Spreadsheets are used for both personal and business needs with unrivaled success to accumulate, analyze and visualize all kinds of data. However, as with any powerful tool, it is important to use spreadsheets right. With great power comes g̶r̶e̶a̶t̶ ̶r̶e̶s̶p̶o̶n̶s̶i̶b̶i̶l̶i̶t̶y an opportunity to make a great mess.
Broadly speaking, apart from creating a functioning spreadsheet, every spreadsheet builder has three additional goals:
- Build spreadsheets fast. With pressing business needs, it goes without saying. If you can spend less time with the same or even better result, you should.
- Minimize the number of bugs in a given spreadsheet. Unfortunately, spreadsheet errors are not a question of if, but of when. According to some estimates around 80–90% of spreadsheets contain errors ranging from inconsequential to catastrophic. There are countless horror stories when they cause massive harm. There is a great video When Spreadsheets Attack! with rather colorful illustrations.
- Build with a change in mind. Useful spreadsheets rarely stay unchanged for long. At some point, a spreadsheet needs to be updated, either to fix bugs or extend functionality.
Any instrument or feature that helps with either of these objectives is something to look for.
Enter Named Ranges. They check all boxes ☑️: allow building spreadsheets faster, with fewer bugs and the resulting spreadsheets are easier and safer to change.
I use them in literally all of my projects and will go as far as saying that building a spreadsheet without named ranges is like programming without variables or constants, always hard-coding all values. It may (hardly) suffice for a small script, while anything larger will be very unstable and painful.
Named ranges require little effort: you can create them in well under a minute, but they will save you from dozens of minutes to hours. Pretty impressive return on investment.
What is a named range and how to create one?
Just to be on the same page, a small introduction to what named ranges are. If you are already familiar with them, you can skip to the next section.
A named range is simply a reference to a regular spreadsheet range with an arbitrary human-readable name (official documentation).
The easiest way to create a named range:
- Select the cell range in question
- Right-click on the selected range to get into the context menu
- Select Define the named range
- Enter named range name
- Hit Enter or click Done
You can see all named ranges in the spreadsheet in the Named ranges panel (“Named ranges” in the “Data” section of the main menu). Here you can manually create a new range or edit existing as well.
9 reasons to use named ranges
Below I will be giving examples for Google Sheets, however, the same principles are applicable in other instruments, including offline Excel.
The points go from basic to more advanced.
1. Autocompletion and overall less manual work
Often you need to refer to the same range many times over to analyze data from different angles. Without named ranges it is tedious and error-prone, to say the least: start filling in a formula, try to find the necessary sheet, select range, hope for the best. It is especially “fun” if there are multiple ranges in the formula, which is often the case in analytics.
Having a named range you do not even need to remember its full name: autocomplete will shoulder the load. Given an effective naming approach, you can easily manipulate dozens, even over a hundred named ranges. Read more on the best practices for naming ranges.
2. Increase the readability of formulas and reduce the number of bugs
Named ranges dramatically increase the readability of formulas. Why readability is important?
First, you read formulas more than you write them. At some point, a spreadsheet needs to be updated, either to fix bugs or extend functionality. Here named ranges shine: with them, it is easier, faster, and much less prone to bugs.
Second, even if you do not plan to update the spreadsheet (which is almost always not the case), you are much less likely to make a mistake with named ranges than without them.
Finally, spreadsheets can live for a long time. It is much easier to rediscover what it does if it is prepared with named ranges.
Let’s look at a simple real-life example from a personal budget spreadsheet, first, without named ranges:
Looking at the formula it is impossible to understand what these ranges represent without further investigation. What is more, without additional context it is difficult to understand what the formula calculates and why.
Now, the formula with the same result:
At a glance, it is clear what inputs formula has and what it ultimately does.
3. Automatically make cell ranges absolute
As you well know, there are two kinds of ranges: relative and absolute (aka “fixed”, “pinned”, etc.). The difference is when you copy or drag cells, relative references will change based on the shift of cells, while absolute ones will stay as-is.
In many cases, especially in data analysis, you want fixed ranges. Don’t know about you, but once in a while, I forget to fix a range, introducing a bug: the target range shifts changing the formulas’ result.
This kind of error is especially dangerous, as it difficult to catch: usually there are no error messages (unless you stumble upon a circular reference) and it can hide for very long. The consequences may be especially dire if you are dealing with money, somebody’s KPIs, etc.
Named ranges are absolute by their nature, leaving nothing to chance. Since I started using them such issues are 95% things of the past. 5% are for cases when I do need a relative range.
4. Automatically check if the range is correct
There will be no error if you input a syntactically valid but incorrect range. For instance, if you make a mistake in a row number or col letter.
It will not happen with named ranges: the named range either exists or not. You will see an error immediately if it doesn’t:
Of course, you can make a mistake in the named range itself, but it is only one place, which is easy to rectify.
5. Allow automatic ranges updates when otherwise it is infeasible
In most cases when you change a range (add/remove rows or columns) all formulas which refer to this range automatically update. However, there are several notable exceptions, a formula accepts not a range, but a string: “A1:A10” instead of A1:A10.
- IMPORTRANGE(url; rangeName): allows import data from another spreadsheet.
- INDIRECT(rangeName): allows to refer to a range when it is not known beforehand or can be changed dynamically by the user.
- Conditional formatting: in custom conditional formatting formulas you cannot directly refer to a range on a different sheet as you do in formulas. To do it you have to use the formula INDIRECT. I usually refer to a different sheet in conditional formatting when I need to allow for a change in conditions for extra flexibility (for instance coloring certain number ranges or tags, which may change).
If you use named ranges in these formulas instead of direct references the changes in the named ranges will automatically translate to these formulas nullifying their limitations.
On a separate note: it is possible to achieve a similar result without named ranges per se, emulating them via intermediate ranges. However, this solution is much worse and I see no reason why to choose it over named ranges.
6. Change sheets safer and faster
Spreadsheets rarely stay the same. Often you need to add additional options to a dropdown, extend data range, add more kinds of data. With named ranges, all you have to do is to change the range in one place.
A real-life example: a company used google sheets for tracking its client’s debts. Depending on the status of the debt and latest communications with the debtor, the company assigned the client a status/type.
At first, when I was building this spreadsheet (actually, the system of spreadsheets to connect different data sources and departments), I used a named range referring to a range of 10 cells, allowing for 10 kinds of statuses. In a month number of statuses exceeded 10 and I had to extend cell range for available options: with the references to available options via a named range it was easy as pie.
In another project at some point, I had to move settings and other stuff to a dedicated settings spreadsheet. Again, with named ranges, it took much less time and worked like a charm.
7. Dynamically change range
Sometimes you need something very dynamic, say a 2+ level dropdown menu. While there is no general solution for it in Google Sheets without App scripts, simple cases can be done purely on named ranges.
Another example: sometimes you need to use one analytical report on several data sets. Named ranges allow to seamlessly switch between them.
8. Change range for experiments or data substitution
In not so rare cases you need to test how a report will work on a different data set to experiment or for testing purposes. If you use named ranges you can substitute the data source in one place by simply changing the reference used by the named range. The rollback is as simple: change the range back.
9. More effective collaboration
Complex spreadsheets may involve the work of several people, developing different parts of the system. Named ranges help here as well.
First, they provide a common toolbox for all parties involved. This speeds up the development and makes spreadsheets more consistent. With a good naming convention, development achieves warp speeds, compared to the no-named-ranges alternative.
Second, with increased formulas readability it is easier to add people to the team or change them.
All in all, named ranges are the best thing after sliced bread.
Do you know of other reasons to use named ranges? Or maybe you know of any not to? Feel free to share in the comments.
