Employees Assignment

The purpose of this assignment is to

1. Understand how to create a table from a spreadsheet

2. Understand how to Convert the table to a Range.

3. Understand the how to insert subtotals in the table.

Preparation: (Review these terms)

  • Insert a Header
  • Inserting rows

New Terms:

  • Create a Subtotal Sheet (Step7, Page 1. (Reference the Subtotal Information Sheet)


Learning to divide a table in to subtotals helps display a large range of data into manageable summaries. Following the steps in the Subtotal Information pages and doing the practice exercise, will help you quickly create this last assignment. (THIS ASSIGNMENT IS WORTH 20 POINTS. THE RUBRIC IS ON PAGE 2.)

Employees Assignment

1. Open the file called Employees and save it to your storage device using the same name.

2. Rename Sheet 1 as Employees.

3. In Cell A1 type the label Department; in B1 type the label Job Title; in C1 type Last Name; in D1 type First Name; in E1 type Extension.

4. Center and bold all the labels in Step 3.

5. Center the data in Columns C, D and E.

6. Insert a Header with the following words on two lines in the center and your name on the right side:

College of Importance

Honored Employees

7. Create a Subtotal Sheet following the steps to make anew sheet, copy and paste information to this sheet, create a table, and Convert to Range. These are Steps B – M on the Subtotal Information sheet. Adjust the columns if necessary.

8. Click in any cell in the range table. Click the DATA tab and choose Subtotals from the Outline Group.

9. In the Subtotal Dialog box complete the information as follows:

  • At each change in: Department
  • Use function: COUNT
  • Add subtotals to: Job Title

10. Make sure Replace current Subtotals and Summary below data are checked.

11. Compare your worksheet to the Completed Subtotal Sheet below.

12. Save this document again as Employees.

13. Submit the document through Blackboard or print if instructor requests it

Completed Subtotal Sheet

Check the rubric below to be sure you have completed all tasks.