Skip to content

Five Reasons to Use Named Ranges

If you find yourself using spreadsheets regularly, or if you just so happen to use spreadsheets every now and then, I highly recommend using Named Ranges in either case.

These are an amazing and super powerful aspect of spreadsheets that is easy to learn and wonderful to use.

If you’ve never heard about Named Ranges before, I will spend the first part of this post discussing what they are and how to create and use them. If you are familiar with the concept, then please feel free to skip right on ahead to my five big reasons for using Named Ranges in the first place.

Named Ranges – What Are They?

In simple terms, Named Ranges are different names that you give to different cells in a spreadsheet.

For instance, you select cell “C6” and give it a name of “johnDoe.” The name does not have to be a “normal” name either. You can call the cell “tennisBallFuzz” if you really wanted to.

Naming a Cell TennisBallFuzz

You are also not limited to a single cell. You can select multiple cells (next to each other or far away from each other) and give a single name to all of these cells at once.

For example, you can select cells “B2” through “D6” and give them a name of “cellPhones.”

Naming a Group of Cells CellPhones

Now whenever you use the name “cellPhones” in a function or formula, you reference the entire group of cells that this name applies to.

Named Ranges – How to Create and Use Them

Creating a named range is fairly easy. All you have to do is:

  • Select the group of cells you want to assign the name to
  • Enter your chosen name into the reference box at the top of your spreadsheet
  • Hit the “Enter” key.

Let’s walk through these three simple steps for sake of example.

Step 1 – Select the Group of Cells

Click and drag your mouse, or hold the “Ctrl” key while you click on multiple cells one after the other.

Selecting a Group of Cells

Step 2 – Enter the Name Into the Reference Box

Type in the name of your choosing in the box located near the top-left corner of your workbook.

Where To Enter the Range Name

Step 3 – Hit the “Enter” Key on Your Keyboard

Press the “Enter” key.

Now that you’ve successfully created a Named Range, you can use it in any function or formula you create in your spreadsheet from this point forward.

All you have to do is type the name into your formula instead of typing something like “A2” or “D4:F6”, and you will successfully reference the cells that your Named Range is applied to.

The Five Reasons to Use Named Ranges

Now that you know what a Named Range is and how to create one, I will go ahead and give you five reasons why you should use them in the first place.

Capitalizing of the power of Named Ranges will allow you to build your spreadsheets faster, more efficiently, and better than ever before.

No. 1 – Named Ranges Allow You To Reference Many Different Sizes of Cells

If you need to reference a single cell. You can use a Named Range.

Naming a Single Cell

If you need to reference 4 cells in 4 vastly different locations, you can use a Named Range.

Naming Multiple Scattered Cells

If you need to reference a group of 1000 cells bunched up next to each other, you can use a Named Range.

Selecting a Group of Cells

Whatever the size or circumstance your function or formula calls for, you can use your Named Ranges to access the cells you need to.

It will save you time. It will save you effort. And it makes things way less complex overall.

No. 2 – Named Ranges Allow You to Reference a Cell from Anywhere

If you are trying to access cell “A14” on “Sheet4” from “Sheet2,” then writing up that reference can be quite a burden.

However, if you just so happen to give cell “A14” on “Sheet4” a name of “EasyToRemember,” then all you have to remember is “EasyToRemember.”

This way, you can reference cell “A14” on “Sheet4” from any sheet or any cell in your entire workbook with ease.

No. 3 – Named Ranges Give More Meaning to Your Formulas

Does the following formula mean anything to you?

= A6 - (B4 + C2)

How about this one?

= Income - (Bills + Expenses)

I bet the second formula makes a lot more sense than the first one.

When you use Named Ranges, you give much more meaning to your formulas. You make your formulas easier to read, and you allow your formulas to better communicate what is going on in the first place.

For instance, when I look at the first formula above, I know nothing about what’s being calculated. When I look at the second formula, I understand that we are dealing with money and exactly which measurements we are using to get our final result.

No. 4 – Named Ranges Make The Spreadsheet Easier to Understand

Just as Named Ranges can add more meaning to a single formula, they can add an exceptional amount of meaning to the spreadsheet as a whole.

Named Ranges make it possible to look at a spreadsheet’s functions or formulas and get an idea of what’s going on. What’s more, if you pull up a spreadsheet that you built over six months ago, you are much more likely to remember how it works if there are Named Ranges to reference.

No. 5 – Named Ranges Can Be Dynamic

Let’s say you are calculating the sum of apples you have logged in your apple consumption spreadsheet calculator.

Now you have to enter in another week’s worth of data. The only problem is that you don’t want to have to update your summation formula to include the cells that will hold your new apple data.

Not to worry, if you created a Named Range that references a table, it will expand to include the all of the new data added to the table.

All of a sudden, your updating formulas problem has disappeared.

All you’ve got to do is enter in your new apple data to see that you’re doing awesome!

This Post Has 2 Comments

  1. This is really neat, I didn’t know this was possible! Is there a handy way to see a list of all defined named ranges and what they reference?

    1. Yes, you can absolutely see a list of all the defined named ranges within a workbook as well as what they reference.

      For Microsoft Excel
      In the Excel Ribbon, simply go to Formulas > Name Manager, and this will open a dialog box with all of your defined named ranges.

      For Google Sheets
      In the Google Sheets Ribbon, go to Data > Named Ranges, and this will open up a side panel showing all of your defined named 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