Week 5 Overview

This is the last week of instruction. Three different topics are introduced, with only two assignments due.

The first two topics are required for the exercised called Student Gradebook Assignment. They are 3-D referencing and VLOOKUP. The 3D referencing allows you to add up figures in multiple sheets and place the results on a Summary Sheet. The VLOOKUP allows you to locate specific values in a table. The computer will read across the row and finds the value in a column you specify. Be sure to do the practice parts before completing the assignment. There are new formulas for both topics.

The last topic is creating a Subtotal Sheet. Steps are outlined for creating Sheet Names, changing a spreadsheet into a table and then into a range as well as using a dialog box to create Subtotals and Grand Totals automatically.

Learning Outcomes: The student will:

  • Understand the concept of 3-D referencing
  • Understand the VLOOKUP function.
  • Apply the 3D Referencing and VLOOKUP functions
  • Understand the difference between IF and the AND functions
  • Apply the IF and the AND functions
  • Understand the use subtotals
  • Apply the subtotal feature to actual data.


1. 3D Referencing Sheet Information and Practice. This information explains the formula used for 3D Referencing and gives a short practice exercise to try

2. VLOOKUP Information. Another new formula that allows creates a way to look up information and deliver that information back to a cell reference point.

3. Student Gradebook Assignment. This assignment reinforces both the 3D Referencing and the VLOOKUP features. (20 points)

4. Subtotals Information and Practice. This information directs you step by step to create a subtotal sheet. There is a practice to complete before doing the assignment.

5. Employees Assignment. Follow the steps and complete the Subtotal. Sheet. (20 Points)

Submitting your Work:

1. When completing each task, make sure to save your document to the desktop or your personal storage unit (memory stick, jump drive, etc.)

2. Click on the underlined word(s) at the top of each assignment. An “email-like” format appears with the number of points possible in the upper left corner.

3. Find a box “Browse My Computer.” Click on that and find your saved document.

4. Click Submit.

5. For additional help, please see the EXTRA HELP folder in the CONTENT area called “How to Submit Your Work.