When working with large amounts of data, it is useful to keep track of similar data in separate worksheets. Data such as monthly, quarterly, or annual information isn’t always best to show in one spreadsheet. However, there may be times when you want to see a calculation of the data on each worksheet. A 3-D Formula will let you easily work with data across the worksheets without having a long complicated formula.

Let’s say you run a business, and you want to see what stock items sell the best on any given day of the week. Looking at just one week’s worth of data doesn’t give you a good picture since you could have had a large party come in on a usually slow day, and throw off your statistics.

Instead, create a spreadsheet with a separate tab for each week you want to track, plus a total tab.

Next, click the Week 1 tab, hold down the Shift key and click the Total tab then release the Shift key. This selects all the tabs in your workbook, and allows you to type information into one sheet and have it show up in all the worksheets in your file.

On the Week 1 tab, click in cell A1 and enter Menu Item, then enter Monday through Sunday in cells B1 through H1. In cells A2 through A7, enter some menu items, such as Widgits, Sprokets, Cogs, and Total. You can enter whatever you want here and go down as far as you need to.

If you want to apply any formatting to all the cells or add total formulas, go ahead and do that now while all the worksheets are still selected.

Using Excel 3D Sum Formula

Now that all the consistent formatting has been done, click on any of the other worksheets in your workbook in order to deselect all the others.

Enter in some sales data for each of the weeks.

Now that all the weeks are populated, you can start using the 3D sum formula in order to get the Total tab populated.

To do this, click on the Total tab, and select cell B2. Enter the following equation:

=SUM(Week1:Week6!B2)

This formula now adds up everything in cell B2 between Week 1 and Week 6. You can copy and paste that formula into the remaining cells to get the totals for all the other menu items and days.

Which 3-D Formulas?

You can use any of the following functions in a 3D formula:

  A B C D
1 SUM      
2 AVERAGE AVERAGEA    
3 COUNT COUNTA    
4 MAX MAXA    
5 MIN MINA    
6 PRODUCT      
7 STDEV STDEVA STDEVP STDEVPA
8 VAR VARA VARP VARPA

To use any of these functions in a 3D formula, click the cell you want to enter it in, and type

=[Name of Function](

Click the tab of the first worksheet in your reference (in the previous example, Week 1).

Hold down the Shift key, and click the last worksheet in the reference (in the previous example, Week 6).

Click the cell or range of cells to be referenced. Complete the formula with any additional arguments that are required, and then press Enter.