Chapter 4
Section 4.3 – Creating Formulas with 3-D References
Learning Objectives
- Entering formulas that reference another sheet.
- Using the SUM function to add up multiple sheets.
Download and open FILE: CH 4.3
Using 3-D formulas allows an Excel user that ability to create Summary worksheets sheet in many multiple sheet workbooks is utilized to present totaled information from the other sheets in the file. This is done to give a quick synopsis of all the other sheets in one convenient location. For this reason, the Summary sheet is usually the first sheet in multiple sheet files. Summary sheets “pull” data from the other sheets using three-dimensional (3-D) cell references.
Let us try adding up all the monthly amounts in our Sales Summary by Quarter Report sheet:
- Click in C4 in the Sales Summary by Quarter Report sheet.
- Type =SUM(. (Make sure to type the open parentheses!)
- Click on the January sheet.
- Hold the SHIFT key down and click on the March sheet.
- Click on cell G1 and press ENTER. Cell C4 should display the sum amount of $26.861,212.
- Repeat these steps for the remaining three quarters.
- Total the amount of sales for 2019 in cell C8.
Skill Refresher
3-D References in Formulas:
- To reference a cell in another sheet, use the formula syntax =SheetName!CellAddress.
To enter a 3-D reference:
- Click on the cell where you want the formula to appear and type =.
- Click on the sheet with the cell you want.
- Click on the cell in the sheet you want, and press ENTER.
Key Takeaways
- 3-D references in formulas allow you to use data from one or more sheets on another sheet.
