Skip to main content

Excel Chapter 9

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
Formal Assessments
(excluding Exams)
Create 3-D formulas which reference data on other worksheets.
  • Excel: Formulas & Functions II Participation Project
  • Excel: Formulas & Functions III Participation Project
  • MyLab Lesson A
  • Old Homework #2
  • MyLab Lesson A
Create validation rules to control data values.
  • MyLab Lesson B
  • MyLab Lesson B
Simultaneously modify multiple worksheets.
  • MyLab Lesson A
  • MyLab Lesson A
Protect spreadsheets from changes.
  • MyLab Lesson B
  • MyLab Lesson B

Chapter Notes

3-D references

A 3-D reference is a pointer to a cell in the other worksheet [1]. 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

3-D references

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 [2].

References

  1. 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.
  2. 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/.