TeachUcomp, Inc.

P: 877-925-8080

  • HOME
  • VIDEO COURSES
    • All-Access Subscriptions
    • Business Licensing
    • Course List
      • Adobe
        • Acrobat
        • Lightroom Classic
        • Photoshop
        • Photoshop Elements
      • Crystal Reports
      • Employment Skills
        • Interview Skills
        • Resume Skills
      • For Lawyers
        • Excel for Lawyers
        • Outlook for Lawyers
        • QuickBooks for Lawyers
        • Word for Lawyers
      • HTML
      • JavaScript
      • Microsoft Office
        • Access
        • Excel
        • Microsoft Office Suite
        • OneNote
        • Outlook
        • Outlook on the Web
        • PowerPoint
        • Publisher
        • Word
      • Microsoft Project
      • Microsoft Teams
      • QuickBooks Online
      • QuickBooks Pro
      • Sage
        • Peachtree
        • Sage 50
      • SQL
      • Windows
    • Video Course Options
  • SUBSCRIPTIONS
  • MANUALS
    • Adobe
      • Acrobat
      • Lightroom Classic
      • Photoshop
      • Photoshop Elements
    • Crystal Reports
    • For Lawyers
      • Excel for Lawyers
      • Outlook for Lawyers
      • QuickBooks for Lawyers
      • Word for Lawyers
    • HTML
    • JavaScript
    • Microsoft Office
      • Access
      • Excel
      • OneNote
      • Outlook
      • Outlook on the Web
      • PowerPoint
      • Publisher
      • Word
    • Microsoft Project
    • Microsoft Teams
    • QuickBooks Online
    • QuickBooks Pro
    • Sage
      • Peachtree
      • Sage 50
    • SQL
    • Windows
  • REFERENCE CARDS
    • Adobe
      • Acrobat DC
      • Lightroom Classic CC 2018
      • Photoshop 2021
      • Photoshop 2020
      • Photoshop CC 2018
      • Photoshop Elements 2022
      • Photoshop Elements 2021
      • Photoshop Elements 2020
      • Photoshop Elements 2019
      • Photoshop Elements 2018
      • Photoshop Elements 15
    • Google
      • Google Classroom for Teachers
      • Google Docs
      • Google Drive
      • Google Forms
      • Google Sheets
      • Google Slides
    • Microsoft Office
      • Microsoft Office 365
        • Access for Office 365
        • Excel for Office 365
        • Microsoft Office 365
        • Outlook for Office 365
        • PowerPoint for Office 365
        • Publisher for Office 365
        • Word for Office 365
      • Microsoft Office 2021
        • Access 2021
        • Excel 2021
        • Microsoft Office 2021
        • Outlook 2021
        • PowerPoint 2021
        • Publisher 2021
        • Word 2021
      • Microsoft Office 2019
        • Access 2019
        • Excel 2019
        • Microsoft Office 2019
        • Outlook 2019
        • PowerPoint 2019
        • Publisher 2019
        • Word 2019
      • Microsoft Office 2016
        • Access 2016
        • Excel 2016
        • Microsoft Office 2016
        • OneNote 2016
        • Outlook 2016
        • PowerPoint 2016
        • Publisher 2016
        • Word 2016
      • Microsoft Office 2013
        • Excel 2013
        • Microsoft Office 2013
        • OneNote 2013
        • Outlook 2013
        • PowerPoint 2013
        • Word 2013
      • Microsoft Office for iPad
        • Microsoft Office for iPad
    • Microsoft Teams
      • Microsoft Teams
    • QuickBooks Online
      • QuickBooks Online
    • QuickBooks Pro
      • QuickBooks Pro 2022
      • QuickBooks Pro 2021
      • QuickBooks Pro 2020
      • QuickBooks Pro 2019
      • QuickBooks Pro 2018
      • QuickBooks Pro 2017
      • QuickBooks Pro 2016
      • QuickBooks Pro 2015
      • QuickBooks Pro 2014
    • Sage
      • Sage 50 2019
      • Sage 50 2018
    • SQL
      • SQL
    • Windows
      • Windows 11
      • Windows 10
  • STICKERS
    • Chrome OS
      • Chrome OS for Chromebooks
    • Mac OS
      • Mac OS Monterey-Mojave
    • Microsoft Office
      • Word and Excel (Mac) 2022
      • Word and Excel (PC/Windows) 2021-2016 and 365
    • QuickBooks Desktop
      • QuickBooks Desktop 2022-2015
    • Windows
      • Windows 11 and 10
  • SUPPORT
    • Company
      • About Us
      • Blog
      • Contact Us
      • Our History
      • Our Philosophy
        • Customer Service
        • Mission
      • Policies
      • Testimonials
    • Contact Us
    • FAQ
    • Help
    • Lost Password
    • Register a Product
  • LOGIN
    • Manage My Account
    • Member Login
    • My Courses
  • CART
Shop Now

How to Create 3D Formulas in Microsoft Excel 2010

by Keeley Byrnes / Tuesday, March 04 2014 / Published in Excel 2010, Latest

Get the complete Excel tutorial here

 

 

 

 

 

 

 

Creating 3D Formulas

It is helpful to be able to create a single formula that calculates data gathered from multiple worksheets. These types of formulas are called 3D formulas. They calculate information from multiple worksheets and show the result in a selected formula cell.

When you use 3D formulas, you must also expand your knowledge of formula syntax. You will be using additional punctuation marks when you write these formulas in order to tell Excel specifically which cells from which worksheet you will be using. The additional punctuation marks you will need to know are:

1. !- used to separate the sheet name from the cell reference.

2. $- used to denote an absolute value.

3. :- used to separate sheet names in ranged 3D formulas.

4. ,- used to separate individual sheet/cell references from each other.

 

3D Formula Syntax

The best way to learn how a 3D formula works is to look at some examples. Here are three examples of 3D formulas. The first is a simple 3D formula, the latter two are ranged 3D formulas:

1. =Sheet1!B2+Sheet2!B2+Sheet3!B2

2. =SUM(Sheet1:Sheet3!B2)

Or

3. =SUM(Sheet1!B2,Sheet2!B2,Sheet3!B2)

All three of these formulas will display the same answer. You can use any syntax that you like. The change in the syntax from what you previously learned is that now you are inserting the name of the particular worksheet in the workbook, followed by an exclamation point, and then the cell reference.

 

Creating 3D Range References

In example #2 above, you can see a 3D cell range reference used to create a formula. A 3D cell range is one cell range that spans several sheets deep. In example #2, the range is cell B2, but it is cell B2 in all of the worksheets from “Sheet1” through “Sheet3.”

When referencing 3D ranges in formulas, you use the colon (:) to separate the names of the first and last sheets in the range, followed by a single cell range reference. The cell reference given is then used as the selected cell range through all of the worksheets listed. So in the above example, you are adding cell B2 from Sheet1, Sheet2 and Sheet3.

In 3D ranges, the given cell range cannot change from one sheet to another. Otherwise it is just another 3D formula, and you should use one of the other two alternate syntaxes available.

Tagged under: 2010, 2013, 3d, 3d formula, class, course, excel, excel help, formula, help, how-to, microsoft, training, tutorial

About Keeley Byrnes

Keeley is our Director of Marketing and has been with TeachUcomp since 2010. Keeley manages all aspects of our Marketing Department, acts as liaison for our reseller and affiliate partners and also authors courses in software and taxation.

What you can read next

Buy Lightroom Classic CC Training at TeachUcomp, Inc.: A picture of the interface for the digital download or DVD versions of “Mastering Lightroom Classic Made Easy v.CC,” the Lightroom Classic CC tutorial from TeachUcomp, Inc.
Buy Lightroom Classic CC Training at TeachUcomp, Inc.
Recover Unsaved PowerPoint Presentations: Instructions- A picture of the “Open” dialog box showing unsaved presentations in PowerPoint 2016.
Recover Unsaved PowerPoint Presentations – Instructions
Creating Mailbox Rules in Microsoft Outlook 2010

 

Recent Posts

  • A picture showing how to use comments in HTML code.

    Comments in HTML – Instructions

    Overview of How to Add Comments in HTML        ...
  • A picture showing how to use the Pre tag in HTML.

    Pre Tag in HTML – Instructions

    Overview of How to Use the Pre Tag in HTML     ...
  • A picture showing the Start Personalization settings that let you customize the Start menu in Windows 11.

    How to Customize the Start Menu in Windows 11 – Instructions

    Overview of How to Customize the Start Menu in ...

Categories

Archives

  • GET SOCIAL

© 2001-2022 Copyright TeachUcomp, Inc. All rights reserved.

TOP
Sale! $49 All-Access 0 Days 12 Hours 42 Minutes 35 Seconds      $199 $49 Entire Library!
See Deal