3D Function

Reading Time: 2 minutes

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

A 3D formula simplifies your writing

Say you have a workbook that contains one sheet per month. You want to create a worksheet that will synthesize all these month in a worksheet "Year".

Workbook with a the result of each month in a different worksheet

What we want to do is to add each monthly 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+March!B3+ ...

The formula is correct, but it's really boring to have to select each worksheet one by one. So let's see how we can get the same result but with another technique. 💡😍

Analyze of the structure of the workbook

If you look at the previous formula, you can noticed that we have selected the cell B3 for each worksheets

The idea is to build a formula that will read all the cell B3 through the different worksheets. Exactly like if you have a magic eye and you can see through 😉😎

Principe of a 3D formula

How to build a 3D function?

Follow these steps to build a 3D formula

  • Activate the cell where you want your result
  • Start to write the function SUM


  • Then, select the cell B3 in the sheet January


Here is the trick 😉

  1. Press the Shift key and keep it hold
  2. Then select the worksheet December
  3. Release the Shift key

The formula becomes


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

  • Finish by closing the parenthesis
  • And press Enter.


Result of a 3D formula


When you have to create a formula though many worksheets, it's easy to build a 3D function to use in your formula many cells from different worksheet.

It's easy to build and easy to read 👍😃😍

Related posts

Excel for free

Frédéric LE GUEN

Functions LEFT – RIGHT – MID

Frédéric LE GUEN

Function COUNTIF

Frédéric LE GUEN


romeo 06/12/2019 at 09:33

This is amazing. This can help me in my business in a very fast way, Thank you so much

Frédéric LE GUEN 06/12/2019 at 10:05

You're welcome

Falym 09/02/2019 at 05:22

Ah, it's work for me. If I knowed this technique I've not using boring formula sheet1!cell1 +sheet2!cell1 + ... Thanks.

Frédéric LE GUEN 12/02/2019 at 19:40
Areej Al kolak 07/01/2019 at 10:52

it doesn't work with me

Frédéric LE GUEN 08/01/2019 at 09:12

It should. This technique exists since the beginning of Excel. Try with 2 sheets first and then for my worksheets


Leave a Comment