Excel Tip Range Names

Range Names as Absolutes

I decided to talk about Range Names today since my blog of yesterday was on absolute cell references. Whenever you name a cell, Excel treats it as an absolute cell reference. So, lets talk about this a bit and see why it can be extremely useful to give a cell or a range of cells a name.

• First, you can use range names in formulas. =Sum(expenses) is a lot more explanatory as well as easier to remember than =sum(F15.F36). This can be very useful in those budget spreadsheets that you use a couple of times a year and have trouble remembering what things are.



• You can use range names to navigate around a workbook as a range name can only be used once in a book. Select the range name and Excel will automatically hop you over to the cell or range of cells that you named - even if they are on a different sheet.



• You can use a range name in a print area so if you are printing different sections of the same file every month it would be easier to remember a range name than cell references.

Okay- you get the idea. I personally like to use range names in Vlookups and other functions where I need to refer to a table - particularly if I am going to copy that function down. Why? Because range names are absolute cell references and if I incorporate the range name into the Vlookup, I dont have to worry about making sure that the cell references are absolutes cell references - it does it automatically!! Honest....

So, now that I have convinced you of your need to know about range names, let me tell you how to create one. There are a couple of ways but this works in all Excel versions and frankly it is the easiest way to do it so I wont bore you with the other ways.

1. Select the cell or range of cells that you want to name.


2. Click the Name box, located at the left end of the formula bar, just above Column A .


3. Type in the name that you want to use.


4. Press the ENTER key.

I told you it was easy!

Okay.. now for the rules...
Names can be up to 255 characters but most people keep them very short so that it is easy to remember and easy to type.
Range names cannot begin with a number ( 2008 Sales is wont work but Y2008_Sales would )
Range names cannot contain any spaces. (You can use underscores though)

• Careful- in Excel 2007, some range names such as Tax08 won’t be valid range names as it is considered a cell address due to the size of the 2007 Excel spreadsheet!

Okay- last thing... if you want to select the Range Name simply click the drop down arrow beside the Name box.
If you want to see the range names or use them in a formula, click the F3 key to provide a list of them. (In Excel 2007, you can also use the Name Manager found on the Formula Ribbon).

download
alternative link download