3D Formula in Excel

3D Formula in Excel
Last Updated on 02/10/2023
Reading time: 2 minutes

In Excel, you can write functions using the same cell's reference but with several continuous worksheets, this is called a 3D formula.

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 months 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 sale.

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 the structure of the workbook

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

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 formula in Excel?

Follow these steps to build a 3D formula

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

=SUM(

  • Then, select cell B3 in the sheet January

=SUM(Janvier!B3

Here is the trick 😉

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

The formula becomes

=SUM('January:December'!B3

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.

=SUM('January:December'!B3)

Result of a 3D formula

Conclusion

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

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

8 Comments

  1. Max
    12/02/2022 @ 21:30

    The position of the exlamation mark is wrong:
    =SUM('January:December!'B3
    should be:
    =SUM('January:December'!B3

    Reply

    • Frédéric LE GUEN
      13/02/2022 @ 09:19

      Correct 🙂

      Reply

  2. romeo
    06/12/2019 @ 09:33

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

    Reply

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

      You're welcome

      Reply

  3. Falym
    09/02/2019 @ 05:22

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

    Reply

    • Frédéric LE GUEN
      12/02/2019 @ 19:40

      Reply

  4. Areej Al kolak
    07/01/2019 @ 10:52

    it doesn't work with me

    Reply

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

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

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

3D Formula in Excel

Reading time: 2 minutes
Last Updated on 02/10/2023

In Excel, you can write functions using the same cell's reference but with several continuous worksheets, this is called a 3D formula.

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 months 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 sale.

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 the structure of the workbook

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

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 formula in Excel?

Follow these steps to build a 3D formula

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

=SUM(

  • Then, select cell B3 in the sheet January

=SUM(Janvier!B3

Here is the trick 😉

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

The formula becomes

=SUM('January:December'!B3

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.

=SUM('January:December'!B3)

Result of a 3D formula

Conclusion

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

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

8 Comments

  1. Max
    12/02/2022 @ 21:30

    The position of the exlamation mark is wrong:
    =SUM('January:December!'B3
    should be:
    =SUM('January:December'!B3

    Reply

    • Frédéric LE GUEN
      13/02/2022 @ 09:19

      Correct 🙂

      Reply

  2. romeo
    06/12/2019 @ 09:33

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

    Reply

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

      You're welcome

      Reply

  3. Falym
    09/02/2019 @ 05:22

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

    Reply

    • Frédéric LE GUEN
      12/02/2019 @ 19:40

      Reply

  4. Areej Al kolak
    07/01/2019 @ 10:52

    it doesn't work with me

    Reply

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

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

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *