Chapter Demonstrable Skills
After completing the Excel Chapter 9 portion of the Excel & Data Analysis unit, students will be able to:
|Demonstrable Skill||Learning Activities
& Informal Assessments
|Create 3-D formulas which reference data on other worksheets.||
|Create validation rules to control data values.||
|Simultaneously modify multiple worksheets.||
A 3-D reference is a pointer to a cell in the other worksheet . In a 3-D reference, the worksheet name is separated from the cell reference by an exclamation mark.
=SUM(‘First Worksheet:Last Worksheet’!RangeOfCells)
For example, in =SUM(‘Qtr1:Qtr4’!E3), Qtr1:Qtr4 are four worksheets named Qtr1, Qtr2, Qtr3, and Qtr4. E3 refers to a cell on each of those worksheets.
Tips & Tricks
Don't type 3-D references - you can, but it's easy to make a mistake! Start the formula then click on the worksheet and cell you want to reference! It's much easier .
- M. A. Poatsy, K. Mulberry, C. Krebs, L. Hogan, A. Rutledge, and E. Cameron, Exploring Microsoft Office 2013, 1st ed., vol. 2. Upper Saddle River, NJ: Pearson Education, 2013.
- R. G. Phipps and B. M. Powell, “Excel Chapter 9,” Computer Science 101, May 18, 2016. Available: http://cs101.wvu.edu/materials/units/excel-data-analysis/excel-chapter-9/.