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
