Learn Excel Now

Conquer the Fear of Excel

Keep up to date on all the newest live trainings, events, and tutorials.

  • Home
  • Training
    • Live Training
    • eLearning
    • On-Demand Training
  • All-Access
  • Resources
  • Library
  • Contact

Creating 3-D Formulas in Excels for Multiple Worksheets

November 11, 2015 by Tyrone Pernsley

A 3-D formula in Excel can be used to calculate data using multiple worksheets in a workbook. Check out these tips to learn how to create and use 3-D formulas in Excel.

What 3-D Formulas in Excel  Look Like

The syntax for a 3-D formula is “worksheetA:worksheetB!reference”

  • WorksheetA is the first worksheet that you want to be included in the calculation.
  • WorksheetB is the last worksheet that you want included in the calculation.
  • The reference is the cell or cells that contain the values that you want to be a part of the calculation.

Excel 3-D Formulas image 1.jpg

 

Functions that You Can Use with 3-D Formulas

  • SUM: This calculates the sum of all the values you select
  • AVERAGE: This calculates the average of all the values you select
  • COUNT: This counts the number of cells in the range that contain numbers
  • MAX: This returns the largest value in the set of values that you select
  • MIN: This returns the smallest value in the set of values that you select
  • PRODUCT: This multiplies all the numbers that you select
  • STDEV: This estimates the standard deviation based on a sample
  • VAR: This estimates the variance based on a sample
  • VARP: This calculates the variance based on the entire population

 

Creating 3-D Formulas in Excel

To create a 3-D formula, first select the cell where you want to enter the function. Now, on the “Formula” tab, select the function that you want to use. From there, you will need to select the data that you want to be included in the calculation. First, click on the sheet for the first worksheet that you want to reference, then hold the “Shift” key and click on the last sheet that you want included. Now you will need to highlight the cell(s) that should be a part of the calculation. Make sure that the values are in the same location on all of the worksheets that you are using. When you are finished selecting your sheets and cells, click “Enter” and your function will be calculated based on the data you selected.

3-D Formulas Shortcut for Adding Values

To quickly calculate a sum with a 3-D formula, you can use this shortcut. In the worksheet that you want your data to be calculated in, select a cell and click “AutoSum.” Click on the first worksheet that contains the data that you want calculated then hold down the “Shift” and click on the last worksheet that you want to be included in the calculations. Finally click on a cell that you want the calculation to be calculated from, and press “Enter.” Your solution will appear in the cell that you clicked on originally.

Liked this Excel combined charts guide? Have questions? Leave your comments below; we’d love to hear from you.

Getting Social with Excel: Spread the word and share the knowledge!

Kevin – Learn Excel Now

 

Filed Under: Charts Tagged With: charts, Excel 2010, formulas

Keep up to date on all the newest live trainings, events, and tutorials.

All Access Subscription
Our 1-year all-access subscription provides step-by-step guidance for mastering Microsoft Office applications, with workshops, exercises and quick reference eGuides. It's perfect for anyone that needs to brush up on certain skills or even folks who need to learn the programs - from a beginner’s level.
Excel Foundations
Take the fear out of Excel with this 20 module self-paced training course. You will cover the most essential topics to develop a solid foundation of Excel or a lifetime of mastery.

Learn Excel Now helps you conquer the fear of Excel. By providing self-paced and instructor-led training and free strategies and guides, we leave no Excel mystery unsolved.

Sitemap

  • Home
  • Training
    • Live Training
    • eLearning
    • On-Demand Training
  • All-Access
  • Resources
  • Library
  • Contact

Connect with Us

  • Facebook
  • Linkedin

Contact

Learn Excel Now
questions@learnexcelnow.com
1-800-964-6033
384 Technology Drive
Malvern, PA 19355

Microsoft® Office Excel® is a registered trademark of the Microsoft Corporation in the United States and other countries. All rights reserved.

Copyright © 2020 ­ Learn Excel Now

Copyright © 2021 · News Pro Theme on Genesis Framework · WordPress · Log in