Naming Ranges

Instead of always the identifying the same “Range” to provide in the “Formula”, where we need to use the same “Range”, we can make use of a scope identification.

To put it simply, we can give a “Range” a name.

First, we select the “Range” of cells that we want to give a name.

We click in the cell name space (left of the formula bar), and type a name and click “Enter”.

In this case I choose ‘Sale2000.”

“Range” names can not contain spaces.

We can see if any ranges are present in our workbook by clicking on the black arrow next to the textbox.

We may jump to the “Range” that we choose in the list from any “Worksheet” in our “Workbook” at any time.

We can also use “Range” names in “Formula”(s).

It is important to know when we use “Range” names in our “Formula”(s) that we make a specific reference to a specific cell “Range” in a particular “Workbook”.

“Formula”(s) using “Range” Names have to use them with “Absolute cell reference”.

i.e. when you copy the “Formula”, it will always refer to the original “Cells”.

The use of a “Range” Name in “Formula”(s)

We can use “Range” names in “Formula”(s) instead of “Cell references”.

When we have new “Formula”(s), we simply type the “Range” name instead of a cell.

We can also change existing “Range” names that we added to a “Formula”.

To do this we first create a “Range” name with the exact same “Range” as what is already indicated in our “Formula” (s).

Then select the “Formula” cell (s) that you want to replace the “Range” names for.

Then click “Insert” from the menu, choose “Name” and then click “Apply” in the submenu.

A dialog box opens:

Select the “Range” name, and click ‘OK’.

Result, the”Formulas” remain as “Formulas” with cell referencing “Range” names.

Creating “Range” Names in “Header’

We can make our column titles or row titles to have range names.

To begin, we first select the cell “Range” for us, including the column titles or row titles:

Click “Insert” in the menu bar, choose “Name” and then click “Create”.

A dialog box opens:

In the “Create Names” dialog box, please make certain that the names for the “Ranges” relate to the correct location.
In our case it is indeed the left column.
So we click on OK.

We see that the “Range” names are added to our list:

Deleting “Range” Names

If we want to delete “Range” names, we click on the “Insert” menu, choose “Name” and click “Define”.

This opens the “Define Names” dialog box in which we select the “Range” names, and click “Delete”.

Once we finish deleting our “Range” names, click ‘OK’.

Naming “3D Range”

We can also give a name to “3D Range”.
If you remember, “3D Range”, is a common “Range” of a cells across the different worksheets.

As long as the cell in each “Worksheet” is equal we can give a name and we can also use this “Range” name in “Formula”(s).

To do this we select “Insert” from the menu bar, choose “Name” and click “Define”.

This opens the “Define Name” dialog box.

In the “Names in Workbook” we type a name that we want to give our “3D Range”.

You must remember that no spaces are allowed.

In the “Refers to:” section, we click on the button on the far right corner.

Then we click on our first worksheet in the “3D Range” (“Jan.” )

Hold down the “Shift” key on your keyboard and click the last worksheet in the “3D Range” (“March”).

Then click the cell you want to use for the “3D Range” (“B3”).

Now everything should look like:

Click “Back” button on the right side of the dialog box.

And in the dialog box, click the “Add” button:

And click OK.

If we now use “3D Range” name, we must manually enter these into our “Formula bar”.

Because, as you can see, “3D Range” names do not appear in our list.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats