Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Excel Named Ranges Explained

| | Information Helpful? Why Not Donate.

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

Got any Excel Questions? Free Excel Help

By default, Excel uses the A1 style reference for cell addresses. However, we can override this default and apply meaningful names to our cell(s). For those of you who are not familiar with naming ranges within Excel, it is a very simple process that allows us to change the way we read formulas. Instead of having a formula read =$E$1*$E$2, where $E$1 may store a specific Tax Rate, and $E$2 an employees wage, we can use named ranges to make our formula look like =TaxRate*Wage. Perhaps the biggest advantage to doing this is that our formulas become very easy to read and understand.

Naming Ranges - The Basics

To name a range in Excel it is very easy, but there are a few rules that must be adhered to:

The first character of a name must be a letter, or the underscore (_) character.
We cannot name a cell the same name as an existing cell reference, eg; B22 or R22C2.
Spaces are not allowed. However, this can be overcome by the use of the underscore (_), period (.) or capitalising the first letter of each word (eg: TaxRate).
They cannot exceed 255 characters
If it contains more than 253 characters, you will not be able to select it from the Name Box.
Names are not case sensitive (taxrate is the same as TaxRate).
All named ranges are absolute by default eg; TaxRate will have a cell reference of $E$1.

To name a basic range in Excel, you simply select the cell or cells you wish to apply a name to, and click at the top of your screen immediately to the left of your formula bar in what is called your Name Box. (This will highlight the active cell address that you will type over - If you haven't noticed before, this box always displays the address or name of the active cell). Type the name that you wish to apply to your range selection. Once you have done this, simply push Enter.

From now on, no matter where you are in your Workbook, you will be able to click the drop arrow to the right of the Name Box and you will see a list of all standard named ranges. Click on the one you require and you will be taken to that location. When you type a formula Starting with an = sign, and then use your mouse pointer to select the cell you wish to reference, instead of Excel inserting the A1 style reference address, it will automatically insert the name you have given the cell.

If you have already set your workbook up with possibly thousands of formulas referencing only cell addresses, and you decide that you should name your ranges but cannot face the task of manually going through changing all cell references to your newly named ranges, don't despair because Excel has made this extremely easy to do. The best way to see this is to follow this simply example.

In C1 type =A1*B1
Copy this relative reference formula down to cell C3.
Select cell A1 and name it aRange1
Select A2 and call it aRange2
Select A3 and call it aRange3
Repeat steps 3 - 5 Starting from cell B1 and using the name bRange1 etc.

Imagine you have hundreds of formulas like this all referencing these cells. Rather than go through and change each individually, you could perhaps use Excels Edit>Replace feature to replace all "A1" with "aRange1", but this is really tempting fate and could cause all sorts of problems if you are not fully aware of the consequences of what you are doing. Here is the correct method for doing this.

Select the range of cells containing the formulas (that are currently using A1 style reference) you want to replace with named ranges, or only a single cell if you want this to affect the entire worksheet.
Go to Insert>Name>Apply
Select the name(s) you wish to use in place of references
Click OK.

If you now click back in any cell that was previously referencing a cell address, that you have replaced with it's name, you should see that Excel will have automatically replaced all cell references with the named range. You will have noticed there are a few other options available when using this feature which are very easy to use, but as with most things, trial and error on an unimportant workbook is the best way.

Deleting/Modifying Named Ranges

To delete or modify a Named Range, you must go to Insert>Name>Define or (Ctrl+F3). Once here you simply select the name you wish to delete and click Delete. If you are wanting to modify where the Named Range refers to, simply select its name and then modify accordingly in the Refers to: box at the bottom, then click Add.

Creating Names Very Quickly

Let's look at how we can use existing column and row labels to name ranges in Excel. For this example do the following:

Type the headings Head1 to Head5 in cells B1:F1.
In cell A2:A6 type the row headings Row1 to Row5.
In the cells B2:F6 type any data you wish.
Select the range A1:F6
Go to Insert>Name>Create
Ensure that Top row and Left Column are the only options checked
Click OK.

If you now select the drop arrow to the right of your name box, you will see that by selecting one of the newly created names you will be taken directly to that range. This can save a lot of time and mis-typing if you have a large table of data. Don't be concerned that Excel may run into problems if you use too many named ranges, as I have frequently worked on Workbooks containing hundreds without any problems.

What we can now do is cross reference our table and very easily extract out information at the intersection of two named ranges. For example, if we wanted to find out what resided in the intersecting cell of the named range Head3 and Row4 (eg; cell D5), all we simply need to do is type =Head3 Row4 and push Enter. It is very important to note the space between the two named ranges.

As you can see this method can be used in place of some very deeply nested Look-up formulas. The one that springs to mind immediately is the VLOOKUP and/or the HLOOKUP.

Naming Non-Contiguous Ranges

Our ranges that we name are usually continguous, in other words all cell boundaries are adjoining. However, if there is a need to name non-contiguous ranges, we can do this simply by selecting our cell(s) and holding down our Control key. Once we have our selection, we simply again go up to our Name Box and type in our name. Just check you have incorporated the correct range, by selecting the name from the Name Box and seeing where Excel takes you.

Lets assume we have named our non-contiguous range MyRange and it incorporates many different cell references. We can now replace a formula that may have looked like =SUM(A1:A5,D6:D10,Q50:Q57,BB30:CC43) with =SUM(MyRange). Just be very careful when doing this as not all Excel's functions that take ranges as their arguments will exclude the cells between the non-contiguous named range. As with all things, if uncertain, give it a thorough test first.

Naming Constants

It is often when developing a spreadsheet that you will be constantly referring to a specific figure for calculations, such as a Tax Rate, Superannuation Rate etc. This often means either referring to a cell containing the value or simply typing the value in the cell itself. What we can do is instead of naming a cell or range of cells, is name a constant value. For example, you may be using calculations such as =(EmployeeRate*TaxRate)-8%, where the 8% represents the employers Superannuation contribution. We can easily replace the constant value 8% (0.08) with a more meaningful name. To do this:

Go to Insert>Name>Define
Type the name SuperCont in the Names in Workbook: box
Click in the Refers to: box and type in 0.08.
Click Add, then OK.

The two advantages of doing this are:

It makes our formulas easier to read at a later date.
We only need change the Superannuation Contribution rate at one central point for it to take effect thoughout our entire workbook.

Naming Formulas

As mentioned above Excel uses Absolute cell references for named ranges, this is normally what is required. But there might be times when a Relative reference would be better. Assume you have a list of 300 employee names in Column A, Column B has their pay rate per hour, Column C the hours they have worked. We would normally use a formula like: =$B2*($C2*24). Note we must multiply the hours by 24 to get the correct result, otherwise a time value like 8:00 would have a real value of 0.333333333333333 times this by 24 and you get a true value of 8. Lets replace this formula with a named formula. Note that the cell selection in this case only reflects a table set up as stated above.

Select cell D2 and go to Insert>Name>Define
In the Names in Workbook box, type in PayOwing
In the Refers to box type =$B2*($C2*24)
Click Add, then OK

In cell D2 type =PayOwing and copy this formula down and you will have the pay owing for each employee in the list. The very important point to note here is that we had cell D2 selected before we went to Insert>Name>Define and we used a relative row reference in our formula. This means that whenever we copy this named formula PayOwing, it will always multiply the cell two columns to the left on the same row by one column to the left *24.

Sheet Level Names

Normally when you name a range the name is at the Workbook level, meaning the name refers to a specified range on a specified Worksheet. Once the name has been used it cannot be used again to represent a range on another Worksheet. However, sometimes it can be very handy to have one name that will refer to a specified range on the active sheet at the time. Here is how it is done!

Assume we have a Workbook with 3 Worksheets. These 3 Worksheets are simply named Sheet1, Sheet2 and Sheet3. We want to have a named range called MyRange (can be any legitimate name) that will refer to the range Sheet1 A1:A10 when on Sheet1, Sheet2 A1:A10 when on Sheet2 and Sheet3 A1:A10 when on Sheet3. Here is how

Activate Sheet1
Select the range A1:A10
Click in the Name Box. This is to the left of the Formula Bar below File Edit on the Worksheet Menu Bar.
Type Sheet1!MyRange
Push Enter
Do the same for Sheet2 and 3 using Sheet2!MyRange and Sheet3!MyRange

Now activate any sheet and click the drop arrow on the Name Box. You should see only one occurrence of the name MyRange. Select this and you will be taken direct to the range A1:A10. Now activate any other sheet and do the same. You will always be taken to the range A1:A10 of the active sheet.

The reason we can do this is because we preceded the name with the sheet name followed by the ! (exclamation mark). If you go into Insert>Name>Define you will note that you only see one name and that name is the one that refers to the current active sheet.

If your Worksheet name includes spaces you cannot simply use Sheet1!MyRange. What you must use is 'Sheet 1'!MyRange. In fact you can use the single apostrophes with a Worksheet name with no spaces. It is a good idea to always use the single apostrophes when referring to Worksheet name as it covers all bases.

Another little trick that can often come in handy is using a relative reference named range. By default, named ranges are absolute but we do not have to leave them this way. For example try this.

Select cell A11 on any Worksheet
Go to Insert>Name>Define
In the Name Box: type: MyNumbers
In the Refers to: box type =A$1:A$10
Click Add then Ok.
Now enter the number 1 in cell A1
Select cell A1 and hold down the left mouse button over the Fill Handle - small black square bottom right of selection.
Hold down the Ctrl key and drag down to cell A10
Enter 1 in cell B1
Drag down to cell B10 without holding down the Ctrl key.
In A11 enter the formula =SUM(MyNumbers)
In B11 enter the formula =SUM(MyNumbers)

You should get 55 and 10 respectively. The reason for this is that we had cell A11 active when we went to Insert>Name>Define and referred our range name to A$1:A$10 which is a relative Column and absolute Row named range. It's the $ sign that forces any range to be absolute.

When we use the name MyNumbers in a formula, it will always refer to the 10 cells immediately above. If you used =SUM(MyNumbers) in cell A11 of another Worksheet it will still refer to cells A1:A10 on the sheet which was active when we originally created the range name.

Ok, lets take this one step further. Suppose we want to simplify the summing of the 10 cells above. Here is how

Select cell A11 on any Worksheet
Go to Insert>Name>Define
In the Name Box: type: MySum
In the Refers to: box type =SUM(A$1:A$10)
Click Add then Ok.
Now enter the number 1 in cell A1
Select cell A1 and hold down the left mouse button over the Fill Handle - small black square bottom right of selection.
Hold down the Ctrl key and drag down to cell A10
Enter 1 in cell B1
Drag down to cell B10 without holding down the Ctrl key.
In A11 enter the formula =MySum
In B11 enter the formula =MySum

As you will see we get the same results now but without the need for the Sum function. Have a play about with these types of names, mix up the absolute and relative references and nest a few function together, they can be very handy and save a lot of work in some cases.

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft