post-thumb

Top 7 Advantages of Using Named Ranges

Over the years of building spreadsheets, I’ve unearthed many handy tricks and practices. Among them, named ranges take the top spot for their extensive utility. I use named ranges in my spreadsheets as a rule, barring disposable, ad hoc computations. They aid in crafting spreadsheets swiftly, reducing errors, and ensuring a resilient, future-proof result. Let’s delve into the top seven benefits they provide.

Video guide to named ranges in Google Sheets:

Youtube video Using Named Ranges in Google Sheets

1. Formulas Are Easier to Read and Understand

While it may sound counterintuitive, we usually spend more time reading formulas than writing them. Every time you need to change something in a formula or understand why the result is off, we need first to read and understand the formula, and only then do something with it. For example, try to understand what this formula does:

Function without named ranges

Without looking into these ranges, it is impossible to understand what this formula sums up. Compare the same formula with named ranges:

Function with named ranges

It is clear at a glance what this formula does, it reads almost like a sentence: if A8 is not an empty sum X for a given date in A8 and category…

Use powerful and consistent names with these best practices.

This yields multiple benefits:

  1. People spend much less time on reading and editing formulas.
  2. Fewer mistakes in the formulas.
  3. It is easier collaborate in the document and bring other people to working with the document – you will avoid the situation when only one person is the “formula wisperer” understands what is going on.

2. Simplifies Writing Formulas with Autocomplete

Often you need to refer to the same range many times to analyze data from different angles. With regular ranges like A2:A100 it is tedious and error-prone, to say the least: start typing in a formula, try to find the necessary sheet, select range, hope that you did not miss any rows or columns. It is especially “fun” if there are multiple ranges in the formula, which is always the case in data analysis.

With named ranges you get autocomplete for free: just start typing in the name. Given an effective naming convention, you can easily work with any number of named ranges.

Autocomplete with named ranges

3. Prevent Issues with Shifting Ranges

As you well know, there are two kinds of ranges: relative and absolute (fixed, pinned). The difference is when you copy or drag cells, relative references will change based on the shift in cell positions, while absolute ones will stay as is.

Avoid shifted ranges issues with named ranges

In many cases, especially in data analysis, you want fixed ranges. Forgetting to pin the range and drag the formula is the most common error I see in spreadsheets.

This kind of error is especially dangerous, as it is 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.

4. Always Use Correct Ranges

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 column 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:

Avoid using wrong ranges

Of course, you can make a mistake in the named range itself, but it is only one place, where you can easy rectify it.

5. Automatically Update Ranges Where It Is Otherwise Impossible

In most cases when you change a range (add/remove rows or columns) all formulas that refer to this range automatically update. However, there are several notable exceptions when a formula accepts not a range, but a string: “A1:A10” instead of A1:A10. These ranges will not change on their own.

  • IMPORTRANGE(url, rangeName): imports data from another spreadsheet. Very frequently I see cases when a client imports data from one sheet into a dozen and needs to add columns/rows or just move the range. With named ranges you can do it in one place.

    Make IMPORTRANGE dynamic with named ranges

  • INDIRECT(rangeName): allows referring to a range when it is not known beforehand or can be changed dynamically by the user:

    Make INDIRECT dynamic with named ranges

  • Conditional formatting: custom formulas in conditional formatting cannot directly refer to a range on a different sheet. To do it you have to use the formula INDIRECT:

    Make conditional formatting formulas dynamic with named ranges

Using named ranges there automatically negates limitations of these formulas.

6. Change Sheets Easier, Faster, and Safer

Imagine that you need to add extra rows to existing functions. If you use regular ranges you need to either edit it in each and every formula, or strategically insert rows or columns so the range automatically extends everywhere (which is not always possible). If you miss some formulas their result will be wrong and the error difficult to detect.

With named ranges you need to change the range in one place and everything else is taken care of.

7. Cleaner and More Professional Documents

If you frequently work with spreadsheets or freelance in this field, named ranges can significantly elevate the professionalism of your spreadsheets. What is more, they will be more professional, because this is what professionals do: they build robust, easy to extend and work with tools.

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.