Excel online training course

excel lesson 11, 3D formulas

Making 3D formulas

Its very usefull to make a formula that gets its information from multiple worksheets.
This sort of formula is called a 3D formula.
They get their information from multiple worksheets and show us the results in a formula cell.

When we use 3D formulas we have to adjust the syntax of our formulas a bit.
We use certain characters to let Excel know which cells in which worksheet he must use.
The characters we use for this are:

! character, is used to seperate worksheets from cells.

$ character, is used to give a absolute reference.

: character, is used to seperate worksheet names in 3D formulas.

; character, is used to seperate individual worksheets and cells from each other.

I will give an example:
Lets assume that i made some sales for Januari, Februari and March which have been entered in their own worksheet (Jan, Feb, Mar).

Now we want to get the total sales of the 1ste quarter in the worksheet "1st Qtr".
For we click on the tab for the "1st Qtr" worksheet.
We want to calculate the turnover in cell B2 for the months Jan, Feb and Mar for Jeff.
So first we will select cell B2 and type the = sign.
Then we click on the tab of the worksheet "Jan".
In the "Jan" worksheet we choose cell B2 and click the "+" character on our keyboard.
Then we click on the tab for the "Feb" worksheet and we repeat what we did for the other tab, so select the cell and push the "+" character on your keyboard.
And finally we click on the "Mar" tab, choose the cell and push the "Enter" key on our keyboard.
Now you can see a formula in the formulabar.
We can repeat this entire process for Andrew, Patrick, Jessie and total, but since i am a bit lazy i will copy cell B2 and paste it in cell B3, B4, B5 and B7.

Writing 3D formulas (syntax)

We can enter 3D formulas in more than one way.
The easiest way to do it is explained above.

A second way to do it is:
We select the cell in the worksheet "1st Qtr" and type " =SUM( ".
Then we type the name of our first worksheet: Jan.
followed by a colon, and followed by the name of the last worksheet: Mar.
Then we type the exclamation mark (!) and type the cel reference for each worksheet where we want to base our calculation on, in this example its the same so we enter B3 and push the "Enter" key.
This will give us the formula " =SUM(jan:Mar!B3) " with the same result.

A third way to do it is:
We start with the "=" character in the formulabar.
And next the calculation we want to execute, in our case SUM, open brackets.
Then we choose the worksheet followed by a exclamation mark and the cell.
Then we type a semicolon character ";"
And repeat this for all worksheets.
Finally we push "Enter".

Which ever way you choose the do it, the result is equal for each of them.

3D Range

In example two i gave above, we can see a 3D Range, used to contruct a formula.
A 3D Range is a one cell range, which is used over multiple worksheets.
In the example i give there, is B3 the cell which is refered to in all worksheets.
When we refer to a 3D range, then we use a colon to seperate the names of our worksheet, followed by the same cell, B3.
This cell reference is being used as cell range.
In a 3D range formula the cell which we refer to can neer be different in the multiple worksheets, otherwise its a normal 3D formula and we will use the first or third example which i explained about in the previous part of this lesson.