Published by


Boozad's Avatar
Better Call Saul

Location: Wolves, England
Posts: 1,634

Show Printable Version 


How to Create 3D Formulas in Excel

information   Information
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.

Note   Note
3D formulas will only work if the data you require is placed in exactly the same cell on each sheet.



Here's how:

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.

Click image for larger version. 

Name:	1.jpg 
Views:	382 
Size:	405.8 KB 
ID:	43357

Click image for larger version. 

Name:	2 Half Year Total Blank.jpg 
Views:	370 
Size:	374.6 KB 
ID:	43358

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.

Click image for larger version. 

Name:	3 Enter Sum 1.jpg 
Views:	272 
Size:	376.4 KB 
ID:	43359

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.

Click image for larger version. 

Name:	3 Enter Sum 3.jpg 
Views:	209 
Size:	467.9 KB 
ID:	43360

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.

Click image for larger version. 

Name:	3 Enter Sum 4.jpg 
Views:	461 
Size:	461.9 KB 
ID:	43361

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).

Click image for larger version. 

Name:	3 Enter Sum 5.jpg 
Views:	161 
Size:	404.1 KB 
ID:	43362

You can repeat the process for each cell to give the totals for each category on each forum.


That's it,
Gav.