↑ Return to Functions

3D Function

Excel performs calculations in a spreadsheet but it is also possible to perform calculations across multiple worksheets, this is called 3-dimensional (3D) functions

Presentation


Say you have a workbook that contains one sheet per month and another that will synthesize the year. Year in the sheet you want to calculate the amount of B3 in annual sales.

 

The mistake in such cases is to select each sheet, one by one and add the cell to have a final formula that looks like this.

= January!B3+February!B3+Mars! B3+ …

 

 

While the calculation will ultimately correct, its implementation is an absolute burden and few people will venture to repeat this formula several times.

Performing a 3D formula

The analysis of the previous formula shows that we add systematic the cell B3 of each sheets.

 

So we will create a formula that will add all cells B3 through the worksheets January to December, just as if you had the opportunity to read through the worksheets.

To perform this calculation easily, you will write in cell B3 of the sheet ‘Result of the year’, the function┬áSUM.

 

Then, you select the cell B3 in sheet in January. Your formula begins as follows.

=SUM(Janvier!B3

Now is all happens. You press the Shift key and select the worksheet December.

 

This manipulation results the modification of your formula as follows:

=SUM (‘January:December!’B3

 

The symbol between January and December means that you select all the sheets in your workbook between January to December.

Complete your formula by closing parenthesis and press Enter.

=SUM(‘January:December!’B3)

Fill the next document (directly in this webpage) to practise the 3D formula.




Leave a Reply

%d bloggers like this: