Excel online training course

excel lesson 12, Identifying ranges

Naming ranges

In stead of allways entering the same range in formulas, we can use a Range identification.
Or to put it simple, we can give our Range a name.
First we selet the Range of cells which we want to give a name.
We click in the cell "Name Box" (left of the formula bar), type a name and push the "Enter" key.
In this case i will use Sales2009.
Range names may not contain any spaces.

We can view the ranges that are available in our worksheet at any time by clickin on the black arrow next to the "Name Box" field.
We can jump at any time from any worksheet in our workbook to the range we choose in this list.

We can also use range names in formulas.
Its important to know that when we use range names in our formulas we must make a specific reference to a specific cellrange in a specific worksheet.
Formulas which use range names are formulas with absolute cell references.
In other words, wherever you copy the formula to, it will allways refer to the original cells.

Using a Range name in formulas

We can use Range names in formulas instead of cell references.
When we type new formulas, we simply type the Range name instead of a cell reference.

We can also adjust existing formulas by inserting range names.
To do this we first create a range name with the exact same range as what is allready mentioned in our formula(s).
Then you select the formula cell(s) which you want to replace with the range names.

Then click on "Insert", "Name" and "Apply" in the menubar.

The "Apply Names" window will open.
Choose the range names here and click on "Ok".

The result will be that the formulas with cell references will become formulas with range names:

Creating range names through headers

We can use our colomn or row headers to create range names.
To start we first select the cells for our range including the colomn and row headers:

Click on "Insert", "Name" and "Create" in the menubar which will open the "Create Names" window.

In the "Create Names" window you should be sure that the names for the ranges are in the correct location.
In our case this is the left colomn as suggested by Excel.
So we click on "Ok".

Next you can see that the range names are added to the list.

Removing range names

When we want to remove range names, we click on "Insert", "Name" and "Define" in the menubar.
This will open the "Define Names" window where we select the range names and click on "Delete".
Once all the range names that you wanted to delete are deleted you click on "Ok".

Give 3D ranges a name

We can also give 3D ranges a name.
Remember that 3D ranges only have a range of one cell spread over different worksheets.
As long as the cell reference is the same in each worksheet we can give it a name and we can also use this range name in formulas.
To do this we click on "Insert", "Name" and "Define" in the menubar.
This opens the "Define Name" window.
In the field "Names in Workbook" we enter a name which we want to give to our 3D range.
Remember that no spaces are allowed in the name.
In the field "Refers to:" we click on the button excell_small_79.jpg (635 bytes) on the rightside of the textarea.
Then we click on our first worksheet in the 3D cell range (Jan).
Keep the "Shift" key on your keyboard pushed and click on the last worksheet in the 3D cell range (Mar).
Then click on the cell that you want to use for the 3D range (B4).
Then everything should look like this:

Click on the button excell_small_80.jpg (743 bytes) on the rightside of the textarea again.
And in the window click on the "Add" button:

And click on "Ok"
If we want to use a 3D range name now, then we need to enter it manually into our formula bar.

Because you can see 3D range names are not available in our list.