“Range” Names in “Formulas”

When we input “Range” names , we can also use them in “Formulas”. The following is a simple example:

I have cell E4 (the total coffee sold) whose “Range” name for the data is “coffee”. Cell E5 (the total of sold tea) I have given the “Range” name “Tea”.

We now calculate the total by using our “Range” names in cell F5, so we type =coffee+tea. “Excel” will perform this operation perfectly.

But you could also type =21%*coffee, for example, to calculate the VAT.
Or better yet, =coffee*F1, if you want to show the VAT rate in cell F1 (not shown here).
You can actually perform all kinds of operations on a “Range” name.

“3D Range” Names

A “3D Range” is a “Range” name that is used by different “Worksheet”(s).

For example: When I want to know the grand total of the various products for the month of January, I can define a “Range” name that has this information. Once I do, I can use it later in any “Formula”.For this I first select the “Worksheet” named “Antwerp” and click the “Define Name” button in the “Formulas” tab in the “Ribbon”.

In the dialog, I give my “Range” an appropriate name, e.g. “All_Regions_Jan” (1).

Since we cannot use spaces, I use underscores (_).

The Scope box I leave as is, “Workbook” is fine.In the “Refers to:” I select everything and click the backspace button on my keyboard. This will delete what is currently there.INext, I click the tab of my last “Worksheet” while I hold down the Shift key. IMPORTANT: hold down the Shift key (2)

This selects all “Worksheet”(s). Next, select the cells from the “Range” (3), this is B4 to B8, which is the same in all our “Worksheet”(s) and click OK.

 To explain how this works I will make an additional sheet (1) for the totals of all regions. I call this sheet “Region” I select a cell where I want the total for January in all regions (2)

And type = sum(
For your information, “Sum” is a function which tells excel to calculate the “Sum” of a “Range”. More about this feature in the next lesson.

I click the “Use in Formula” (3) button, in the “Formulas” tab on the “Ribbon” and select the “Range” name which I created (All_Regions_Jan).

Close the parentheses, and click Enter. The result is shown below:

Featured Courses

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