Save time with functions using Excel

Lab specifications

Part number 058 203 SS

Software version number: 97 or higher

Lab length: Half a day

Hardware/software required to complete this Lab

In order to complete the activities in this Lab, you’ll need:

• An IBM or IBM-compatible computer equipped with Excel 97 (or higher). In addition, if you’d like students to have an opportunity to use their own data files during practice time, suggest that they bring them.
• Excel’s Analysis ToolPak installed and enabled for Lab Activity 1.

Lab description

Overview: Students will use a variety of functions and time-saving techniques with sample files consisting of a business’ personnel files, timesheets, exam scores, and project scheduling reports. Here are the activities covered:

• Track elapsed time
• Get the information you need from your Excel list
• Simplify statistical analysis with functions
• Apply error-handling and auditing techniques

Delivery method: This model supports the Lab instructional method. Using this method, the instructor introduces a business scenario, demonstrates techniques for completing an activity, and then lets students complete the activity.

Target student: Students participating in this Lab should be educated Excel worksheet users who are familiar with:

• Creating and saving a worksheet.
• Opening an existing worksheet and using simple editing techniques.
• Creating formulas by using some of Excel’s basic built-in functions, including Sum and Average.
• Navigating and selecting ranges in a worksheet.
• Changing the appearance of data using formatting techniques.
• Moving and copying formulas and other data.

Prerequisite: The following course (or equivalent knowledge): Excel 97: Worksheets.

Objectives

Upon successful completion of this Lab, students will be able to:

• Create formulas and perform calculations involving date and time.
• Use Database functions and filters to extract information they need from a worksheet.
• Do some data analysis using Excel’s Statistical functions.
• Handle formulas that sometimes result in errors; use Excel’s auditing features.

Lab content

Lab Activity 1: Track elapsed time

• Experiment with cell formats and dates
• Use the Now function to keep the current date in a worksheet
• Create formulas to calculate elapsed time
• Use the Workday function to calculate project due dates

Lab Activity 2: Get the information you need from your Excel list

• Use the Dsum function
• Use the Dcount function
• Use the Sumif function
• Use the If function
• Apply filters

Lab Activity 3: Simplify statistical analysis with functions

• Use the Average, Median, and Mode functions
• Use the Stdev function
• Find highs and lows with the Max and Min functions
• Count items in a list
• Apply filters to a list

Lab Activity 4: Apply error-handling and auditing techniques

• Use the Iserror function
• Nest the Iserror function within the If function
• Use Excel’s auditing features