How to Create 3D Formulas in Excel
3D formulas are excellent ways of compiling data over multiple worksheets in Excel. 3D formulas work by effectively lining up worksheets one behind the other and adding through the same cell on each worksheet and offer a much quicker way of compiling data than entering formulas by hand.
This tutorial will show you how to create 3D formulas in Excel.
3D formulas will only work if the data you require is placed in exactly the same cell on each sheet.
1: In this particular workbook we have monthly data (January to July, the deliberate mistake being May is missing) and want to calculate a six-monthly total on a separate worksheet at the end of the workbook.
2: On the Half Year Total worksheet we are going to find the total of BSOD threads started on Vista Forums, so click on cell B3 and type in =SUM(. The formula will display in the selected cell and the formula bar at the top of the worksheet.
3: Click on the Jan worksheet tab and click on cell B3. The formula in the formula bar will now change to =SUM(Jan!B3.
4: Now hold down SHIFT and click each month's worksheet tab one by one (Feb then Mar then Apr and so on) until all months are selected. The formula will now change to =SUM('Jan:Jul'!B3.
5: Now press CTRL+ENTER to enter the formula. You will be taken back to cell B3 on the Half Year Total worksheet which will show the total of BSOD threads started on Vista Forums over six months. The formula in the formula bar now reads =SUM('Jan:Jul'!B3).
You can repeat the process for each cell to give the totals for each category on each forum.