«

»

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.

Summarize worksheets easily with 3d formulas

 

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).Excel 3d formula instructions - click 1st 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).Excel 3d formula instructions - click last 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.Sample Excel 3d formula

 

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.

 

About the Author

Jackie Kiadii is an Information Technology professional with a degree in Computer Information Systems. She learned to program with Visual Basic over 20 years ago, and has developed the following Advanced-level Excel courses: Painless Pivot Tables ™, and Jump Start Excel Macros/VBA™, and Excel’s Best Functions – from A to Z™. Her company also offers Excel 2007, 2010 and 2013: Certification Prep, Basic through Advanced,

Need onsite group training? Call 770.498.7333 or email us. Our courses can be customized to your needs. Jackie has provided software training for employees of The Coca-Cola Company, Emory University, Novelis, and more. This training is available for groups of 4 or more students.

Atlanta Computer Training on Facebook Atlanta Computer Training - LinkedIn Software Tips on Twitter

Click to Grab Your Excel Ebook – Free!

Get the free Excel ebook

12 Tips to Empower Excel Users

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>