Skip to content

Two Examples of Using Range Names

One of the things I talk a ton about is the importance of using Range Names in your spreadsheets.

Whether it’s Microsoft or Google Sheets, using Range Names is one of the best and smartest things you can do.

Using Range Names will:

  • Make your spreadsheet more accessible
  • Make it easier to craft formulas
  • Make it possible to reference your spreadsheet in the future
  • And so much more!

To help demonstrate these points, I will give you two practical examples – one in Google Sheets and the other in Excel.

Example 1 – Using Range Names in Excel

Take a look at the formula below.

=VLOOKUP(E3,A2:C6,3,FALSE)

This formula uses the VLOOKUP function to look up the cost of a specific food. However, no range names are used.

Take a look at the same formula but with Range Names this time instead of standard cell references.

=VLOOKUP(theFood,foodCostTable,3,FALSE)

As you can see, the formula instantly becomes more readable and intuitive. It becomes crystal clear exactly what the VLOOKUP function is searching for and what it is trying to find.

Without the Range Names, you would have to take a close look at the dataset to figure out what’s going on. But with Range Names, all the context is built right into the formula.

Moreover, when you come back to this formula weeks or months after you created it, you will immediately be able to figure out what it was written to do.

Alas! This is the true power of Range Names.

Example 1 – Using Range Names in Google Sheets

Take a look at the formula below.

=SUMIFS(C2:C8, A2:A8, E2, B2:B8, E5)

This formula uses the SUMIFS function to calculate the total spending of a specific category in February. However, no Range Names are used.

Here is the formula again, but this time with Range Names.

=SUMIFS(amountRng, monthRng, theMonth, categoryRng, theCategory)

All of a sudden, the formula makes a lot more sense.

You can easily see what checks the SUMIFS function is doing to make its calculations.

Even without ever setting eyes on the data, you can infer that the function is only adding together the amounts that match up with the selected month and fall under the chosen category.

There is no way you could make this same inference from the formula without the use of Range Names.

Once again, Range Names prove to make the formula much more intuitive and easier to understand.

Hopefully, these examples do a good job of demonstrating the power and effectiveness that range names have to offer. After all, they really can make a difference in your overall spreadsheet design.

This Post Has One Comment

  1. I feel like name ranges are so underestimated. More people should be using them to unlock the power of the name ranges!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top