Range Names in “Formulas”

When we enter Range names, we can use them in our “Formulas”.
Consider this simple example where I have cell E3 (the total coffee sold) with the range name coffee data. Cell E4 (the total of the tea) is given the range name Tea.
We wish now to calculate the total, through our range names in cell F4, then we type = coffee + tea. Excel will perform this operation perfectly.

For example you can also type = coffee * 21%, to calculate the VAT. You can also type = coffee * F1, where VAT is given in cell F1.
You can perform all operations on a range name.

3D Range Names

A 3D Range is a range name that is used on different “Worksheets”.
For example, If I wish to get the grand total of all totals from January for different regions, I can enter a range name that has all this information, and I can use it later in a formula.
To do this I select the “Worksheet” first, Antwerp and click the “Define Name” button in the “Formulas” tab in the “Ribbon”.
In the dialog box I give an appropriate name to my range, eg All_Regions_Jan.
Remember that I used only underscore but not spaces.
I leave the “Scope” box as it is, “Workbook” is fine.
In the “Refers to:” I select everything and click the backspace button on my keyboard.
It gives a warning here.
Instead, I click the tab of my last worksheet while I hold down the Shift key.
IMPORTANT: hold the Shift key pressed.
This selects all worksheets.

Then select the cells from the range, this is B4 to B9, which is the same in all our worksheets.
Click OK.

To see how this works I make an additional “Worksheet” for the totals of all regions.
I select a cell where I want the total of January.
I type = sum (
Just for information, sum is a function, and calculates the sum of a range.
More about functions in the next lesson.

Click the “Use in Formula” button in the”Formulas” tab on the “Ribbon”.
Select the range name that I had just created (All_Regions_jan).

Close the parentheses, and click Enter.
Result:

Featured Courses

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