Add Depth to Your Excel Spreadsheet with 3D Functions

A 3D formula is one that references cells or ranges across multiple worksheets with the same layout. It may help to think of your sheets as stacked on top of one another, with ranges going through the stack of worksheets.

The values in the same cells in each worksheet can be used in formulas as long as the worksheets follow the same pattern, and the cells have the same data format.

Using values from multiple worksheets can be very useful. For instance, a company may have a different worksheet for each division so that it can break down each division’s profits and losses. Within this Excel file, you can create a worksheet that crunches all the profits and loss data, and shows the totals for the company as a whole.

To add amounts across multiple worksheets in Excel 2010:

  1. Select the cell where you want the total to go, and start entering the Sum function, “=sum(”.
  2. Go to the first sheet to be included in the selection, and select the desired cell.
  3. In the worksheet tabs at the bottom of the spreadsheet, hold <shift> and [Click] the last worksheet you want included. This will create a range of cells across all the worksheets from the first one selected to the last one, including all the ones in between.
  4. Once you’ve got the range of worksheets you want, press ENTER to complete the formula.

The resulting formula will look something like this:

=SUM(Sheet1:Sheet3!B2)

For a spreadsheet that includes the worksheets Sheet1, Sheet2, and Sheet3 An image of Excel worksheet tabs, this formula will calculate the total of the B2 cells in Sheet1, Sheet2, and Sheet3.

NOTE: The order of these sheets is important. Changing the order of worksheets from Sheet1, Sheet2, Sheet3 to Sheet1, Sheet3, Sheet2, would mean that Sheet2 would no longer be included in the equation.

Remember that the new worksheet should follow the same layout as the others, and the cells should correspond to the same ones on each sheet.

And it gets better: you can replace the Sum function with many different functions such as PRODUCT, AVERAGE, and MAX (which returns the largest value), as well as others.

Using 3D functions in Excel 2010 can be extremely useful, so don’t limit yourself to one worksheet.

Share!