Microsoft Excel Training- From Beginner to Expert in 6 Hours

EXCEL'S NAMED RANGES

FREE EXCEL HELP

Lesson 23 - Using Named Ranges in Excel as an Alternative to Cell References

<< PREVIOUS LESSON |NEXT LESSON>> | BACK TO EXCEL LEVEL 1 TRAINING INDEX

NAMED RANGES IN EXCEL

Excel allows us to give Worksheet ranges names that can make our formulas easier to read. For instance if we use the above example that we used for the SUMIF Function, we could name our Criteria range (B1:B7) "Names" and our Sum_range (A1:A7) "Amounts". This would make our formula a bit easier to read.

There are however some basic rules for naming cells that we must adhere to. These are listed below and are from the Excel help file.

Guidelines for naming cells, formulas, and constants in Microsoft Excel

The first character of a name must be a letter or an underscore character. Remaining characters in the name can be letters, numbers, periods, and underscore characters.
Names cannot be the same as a cell reference, such as Z$100 or R1C1.
Spaces are not allowed. Underscore characters and periods may be used as word separators � for example, First. Quarter or Sales_Tax.
A name can contain up to 255 characters.
Names can contain uppercase and lowercase letters. Microsoft Excel does not distinguish between uppercase and lowercase characters in names. For example, if you have created the name Sales and then create another name called SALES in the same workbook, the second name will replace the first one.

There are a couple of ways we can name ranges so let's jump straight in with an example.

Delete the contents of cells A1 and B1 and type the word Amounts in cell A1 and Names in cell B1.
Type 5, 20, 40, 50, 100, 200 in cell A2:A7 respectively. Type the names Bob, Dave, John, Fred, Dick, Jill in cells B2:B7 respectively, if they are not there already.
Now either select the range A1:B7 with the mouse, or push Ctrl + Shift + * this will make Excel select the Current region. The Current region is defined as all the non-empty adjoining cells surrounding the active cell. The * (asterisk) must be the one on the same key as the 8).
Go to Insert>Name>Create. This is the Create names dialog box and is used to create names based on the current region row and/or column headings. In this case we only have column headings so ensure that only the "Top row" check box is checked.
What we have done by doing this is told Excel that we wish to name the range A2:A7 Amounts and the range B2:B7 Names. In other words use the headings in the top row to name the selected ranges directly below them. Click OK
Now select cell C8 and push Shift + F3 or go to Insert>Function. Locate the SUMIF function either from within the category Most recently used or Math & Trig then click OK.
Click the collapse dialog button on the Range argument box and select range B2:B7. You should see Excel place the name Names in the Range argument box. This is our named range B2:B7. Click the expand dialog button.
Type "D*" in the Criteria argument box.
Click the collapse dialog button on the Sum_range argument box and select range A2:A7. You should again see Excel place the name Amounts, this time in the Sum_range argument box. Click the expand dialog button. Now Click OK.

You should have the formula =SUMIF(Names,"D*",Amounts) giving the result of 120.

Using the Create names dialog is the easiest method to use if we are going to be calling our ranges the same names as the column and/or row headings as it saves typing and typos. We could, if we wanted, type the names in ourselves, by typing them directly into the Name box. The Name box is on the left of the Formula bar. If we click the small drop arrow on the Name box, we should see both our named ranges Amounts and Names. If you select either one Excel will take you straight to the chosen named range and select it. It is in this box that we can type a name in directly.

To see what I mean select the range A1:B7 and click in the Name box. Type the word Data and push Enter. Now select any cell outside of range A1:B7 and select the name Data from the Name box, you will be transferred automatically to the Data selection.

The other thing we need to know when dealing with named ranges is how to delete them. This can only be done in one way and this is via the Insert name dialog box.

Go to Insert>Name>Define or push Ctrl + F3. This will display our Insert name dialog box.
You should see the three names we had created amongst the listing. Select the name Amounts.
If you now look in the Refers to box you should see =Sheet1!$A$2:$A$7. The Sheet1! May be different if the Worksheet is called something else.
All we need to do now is click Delete and the named range Amounts will be deleted.
Do the same for Data and Names then click OK.

You will notice that our SUMIF formula is now retuning #NAME? This is Excels way of telling us the formula within the cell contains text is does not recognise. Delete the #NAME?

The Insert name dialog is also the only place we can edit named ranges. In the above example we could have altered the Refers to range for any of the names to another range. For example we could have changed =Sheet1!$A$2:$A$7 to =Sheet2!$A$2:$A$7 and/or =Sheet2!$L$1:$M$70 or any valid range address.

<< PREVIOUS LESSON |NEXT LESSON>> | BACK TO EXCEL LEVEL 1 TRAINING INDEX

Instant Download and Money Back Guarantee on Most Software

Microsoft Excel Training- From Beginner to Expert in 6 Hours

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