Making “3D Formula”

It is useful to create a formula that gets its data from different “Worksheets”.

This kind of formula is called a “3D Formula”.

These get their information from different “Worksheets” and show us the results in a “Formula” cell.

When we use a “3D Formula”, we need to slightly modify the syntax of our “Formula”.

We use certain characters for Excel to know which “Cells” of the “Worksheets” it should use.

The characters that we use for this are:

! character, is used for separating worksheets and cells of worksheets.

$ character, is used for an “Absolute Cell Reference” to it.

: character, is used to separate “Worksheet” names in “3D formula”

; character, is used to separate individual “Worksheets” and “Cells”.

I’ll give an example:

Let us assume that I’ve entered sales for January, February and March, each displayed in their own “Worksheet” (“Jan.” – “Feb.” -” March”).

Now we want the “Worksheet” named “1st KW” to show total turnover of the 1st quarter.

First we click on the tab of the worksheet “1st KW”.

We want the cell B2 to calculate the turnover of the months January, February and March from the Belgium region.

So we first select the cell B2, and type the ‘=’ sign.
Then we click on the tab of the Jan “Worksheet”.
In the Jan. “Worksheet”, we select the cell B2, and click the ‘=’ sign on our keyboard.
Then we click on the tab of the Feb. “Worksheet” and do exactly the same, so click on the cell and enter the ‘=’ sign.
And last we click on the tab of the March “Worksheet”, select the cell and click Enter.
We see our “Formula” on top in the “Formula bar”.

We would be able to repeat everything for the Netherlands, France, Italy and finally for the Total, but I’m pretty lazy, so I copy and paste it into cell B2 B3, B4, B5 and B7.

“3D Formula” notation (“Syntax”).

We could enter “3D Formula” in different ways.

The simplest way is what we have already seen above.

A second way is:
We select the cell in the “Worksheet” called “1st KW” and type ‘= SUM (‘

then we type the name of the first “Worksheet” as “Jan.” followed by a colon, and then the name of the final sheet, “March”.

Then we type the exclamation mark (!) and the cell references used for each “Worksheet”, where we wish to base our calculation, which should be the same, so we enter “B2”.

And click “Enter”.

this gives the “Formula” “= SUM (Jan.: March! B2)” with the same result.

A third way is:

We start again with the ‘=’ sign in the “Formula bar”.
Followed by the calculation we want to execute, in our case, “SUM”, open the brackets.
Then we select the “Worksheet” followed by an exclamation mark (!) and the “Cells”.
Then we type a comma character (,)
And this is repeated as many times.

“= SUM (Jan.! B5; Feb.! B5; March! B5)”

and click “Enter”.

Whichever way you choose, the result is the same for all three.

“3D Range”

In Example 2 (above), we see that a “Formula” uses a “3D Range”, to put together its functionality.

A “3D Range” is a single-cell range, which is used on different “Worksheets”.

In the example we give, “B2” as the cell that is referenced in all “Worksheets”.

When we refer to a “3D Range”, we use the colon (:) to separate the names of our “Worksheets”, followed by the same cell, namely “B2”.

The “Cell reference” is used as a “Cell range”.

In a “3D Range”, the “Formula” allows us to refer to the same cell in different “Worksheets” and not different cells in the various “Worksheets”, otherwise it is an ordinary “3D formula” and we would use the first or third method, described in the previous section of this lesson.

Featured Courses

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