EngradeWikis › MS Excel Chapter 5

MS Excel Chapter 5

Course Objective: 33.02

Create documents using advanced features in word processing, database, spreadsheet, presentation, and multimedia software.

You will have mastered the material in this chapter when you can:
Create and manipulate a table
Delete sheets in a workbook
Validate data
Add calculated columns
Use icon sets with conditional formatting
Use VLOOKUP function to look up a value in a table
Print the table
Add and delete records and change field values in a table
Sort a table on one field or multiple fields
Display automatic subtotals
Urse Group and Outline features to hide and unhide data
Query a table
Apply database functions, the SUMIF function, and the COUNTIF function
Save a workbook in a different file format

As per the syllabus, there will be Excel 2007 projects to complete. Each Excel project is worth points of your final grade. Please see the Rubric below for grading.


Extra Credit assignments will not be accepted unless all other assignments for the chapter are turned in on time. Extra Credit assignments are due by the end of the term and will not be counted as late.

Read Chapter 5, EX 338 to EX 399. Creating, Sorting, and Querying a Table and complete the instructions in each section.

1. Complete the instructions for creating"Silver Photography Accessories Sales Rep" worksheet in Chapter 5.

2. Do the Learn It Online Chapter Reinforcement exercises on page 400. Record your answers in a Word document.

3. Read "Apply Your Knowledge" on Page EX 400-401 and complete "Apply 5-1 Fritzs Luxury Kennel Guest List" workbook as per instructions.

4. Read "Make It Right" on Page EX 404-405 and complete "Make It Right 5-1 Van Dyl Kitchen Accessories Sales Rep List" workbook as per instructions

5. Read "Extend Your Knowledge" on Page EX 402-403 and complete "Extend 5-1 Jensen Basketball Poles Complete” workbook as per instructions.

In The Lab
Complete the instructions and submit the final Excel workbooks ts as instructed in each section.

1. Read Lab 1 on page EX 405-408 Creating, Filtering, Sorting a Table to Determine subtotals and complete the instructions to create Lab 5-1 Whitman Publishing Sales Rep Table"workbooks as directed in Part A. Create the Lab 5-1 Whiltman Publishing Sales Rep Table CSV file.

2. Read Lab 2 on page EX 408-411: Sorting, Finding, and Advanced Filtering and complete the instructions to create the workbook Lab 5-2 Consultant Specialist Table workbook. SLave the workbook at each print instruction with a different name or create a new worksheet at each print instruction.

3. Read Lab 3 on page EX 412-413: Creating a Table with a Lookup Function and complete the instructions to create the Lab 5-3 Lab 5-3 Do-Gooders' Student Club Table and Lab 5-3 Do Gooders' Student Club Table Final workbooks.

Cases and Places

1. Read Cases and Places #1 Inventory Level Priority on page EX 414. Follow the instructions to create Fairview "Educational Percussion, Inc Inventory List" workbook.

2. Read Cases and Places #3 Filtering a Table and Multiple Condtional Formats on page EX 414. Follow the instructions to create the "Educational Percussion, Inc Inventory filters and conditional format” workbook.Create a separate workbook or worksheet at each print instruction.

3. Read Cases and Places #5 Creating a Table of Students on page EX 415 . Follow the instructions to create the "Student table" workbook. You may worth with a partner or you may complete this by yourself. Let the instructor know your plan. Be sure to run sorts, determine subtotals and use the database , COUNTIF and SUMIF function to generate statistics.