Named ranges in excel are brilliant things. They have helped me many times to make my formulas a lot readable, and in many ways a lot handier to work with.

What is a named range in excel?

A named range is a range of cells. or a single cell.
Basically if I use a range of cell a lot (for example a list of my colleagues names) I could just name that range, then I can refer to it a the name you chosen for the range. Handy isn’t it?

Let me show you a practical example

Excel Named Range example with VLOOKUP
Excel Named Range example with VLOOKUP

What did we do here?

Above you can see that we had a little table with prices of fruits.
First we highlighted the whole table and named this range MyRange.
Important!: To hit enter after you name a range. If you tab or click away it will not get set!

Then we wrote a simple VLOOKUP formula where for the table_array we simply hit F3 then from the pop up window (which would display ALL the named ranges in a workbook) selected the named ranges of our table.

If you take a look at the formula you can see that it is very clean this way.


O.K. I set my named range, but I want to change it.
How can I do that?

To change the parameters of a named range.
(That can be either it’s name or the range itself)
You have to go to the Name Manager on the ribbon.
It lives under the Formulas tab

Name manager on the ribbon in excel
Name manager on the ribbon in excel

Once you click on the button a window will pop up.
This window contain all the named ranges in a workbook.

Excel Name manager window
Excel Name manager

In this Name Manager window you can see:

  • Name
  • Value
  • Refers To
  • Scope
  • Comment of a named range.

Doubleclick a Named range and you can edit it:

Most of the time we will edit the Refers to range, as we want to include more cells, or remove something from our range.

Note: The named range have to be a continuous range.
It can’t be a group of individual “scattered” cells.

This is as far as basic named ranges go.
In a more detailed post we will discuss the possibility of Dynamic ranges that contract and expand based on how many items inside of it.
You might want to take a look at our other post on copying formulas here.

We also have a facebook page where you can follow us and get notified of the new posts tips and tricks at ExcelAngel.

[et_pb_ccfcm_facebook_comments_module ccfcm_app_id=”339898187197719″ _builder_version=”4.6.1″ _module_preset=”default”][/et_pb_ccfcm_facebook_comments_module]