# Excel Tip: Summarize Multiple Worksheets Quickly with 3D Formulas

Untitled Document

by Jackie Kiadii – Atlanta-based Excel Trainer

Imagine this. You have a workbook that has sales figures for your company’s 50 locations. Each location has its own worksheet, and you’d like to summarize the information onto one worksheet. You shudder at the thought of having to create a formula using 50+ signs or an Excel Macro to accomplish the task. Looking for an almost instant way to summarize a workbook that has multiple sheets? Well, if all of the sheets you are trying to summarize have identical layouts, I have a solution for you – Excel’s 3D formulas.

Granted, 3D formulas take a little getting used to and you have to set up your workbook properly. I have created instructions that simplify the process and trust me, once you’ve created a couple of 3D formulas they’ll be a snap.

## Benefits of 3D Formulas

#### 3D Formulas instantly summarize multiple worksheets.

Whether your workbook has 5 sheets or 75, (1) the steps are the same and (2) they take the same amount of time to set up.

#### 3D formulas work with a variety of functions.

Although these instructions use the SUM function, you can also use COUNT, AVERAGE, MIN, MAX and a variety of functions in 3D formulas.

## 3D Formula Workbook Setup

In order for 3D formulas to work, your workbook must be set up properly.

1. The worksheets you’re summarizing must have identical layouts. If the sales figures for January are in cell B1 in one sheet, for example, they should be in cell B1 for all.

2. Your summary worksheet must be before the first sheet you want to summarize or after the last one.

## Step-by-Step Instructions: Creating a 3D Formula

1. Click the cell in your summary sheet where you want to place your results.
2. Type the equal sign, the name of the function, and the open parenthesis symbol.For example, =SUM(.
3. Click the name of the 1st worksheet you want to summarize (by clicking the worksheet’s tab). 4. Hold down the SHIFT key.
5. Click the name of the last worksheet you want to summarize (by clicking the worksheet’s tab). 6. Release the SHIFT key.
7. Click the cell you want to summarize. For example, cell B2.
8. Click the ENTER key.  Hitting the enter key will take you to your summary sheet, where your 3D formula is calculated. This formula shown above simply adds the values in cell B2 in all the January and December sheets as well as all of the sheets between them. As I mentioned earlier, you can use other functions (AVERAGE, MIN, MAX, etc.) within 3D formulas.

### Ready to try 3D formulas?

Try the 3D formula and let me know how it works – share your comments below.

###     