Microsoft® Excel:
Practice 1
RUBRIC
|
0
|
3
|
5
|
8
|
10
|
|
Less
than 25% of items completed correctly.
|
More
than 25% of items completed correctly
|
More
than 50% of items completed correctly
|
More
than 75% of items completed correctly
|
All
items completed correctly
|
Each
step to complete is considered a single item, even if it is part of a larger
string of steps.
Objectives:
The
Learner will be able to
1. Apply Currency formatting to cells in Excel
2. Use the AutoSum tool to add cells
3. Use the Function tool to calculate the
average of a range of cells
4. Insert an IF function
5. Rename spreadsheet
Working with Equations
Enter the labels and
format the labels big, bold, and centered
·
In
Cell A1 type: First Name
·
In
Cell B1 type: Last Name
·
In
Cell C1 type: Department
·
In
Cell D1 type: Salary
Add
the data
Type at least
five sample records. For example:
|
|
A
|
B
|
C
|
D
|
|
1
|
First Name
|
Last
Name
|
Department
|
Salary
|
|
2
|
Dimple
|
Singh
|
Training
|
$34,000
|
Format
the Columns
Select column D
and use the Currency tool
Creating
Equations
Select Cell D7
and use AutoSum to add up the SUM of the Salaries in the D Column.
Select Cell D8
and use Insert Function to calculate the AVERAGE of the Salaries.
Using IF functions
This activity
compares the employee’s salary with the Average in Cell D8.
Insert the label
"Compare" into cell E1
In E2, insert the
IF function.
In the Wizard, enter the following
information
Logical Test: D2>D8
Value_if_true:
"Above"
Value_if_false:
"Below"
Use the Insert
Function wizard to put the correct formula for the remaining cells.
Save the spreadsheet and name it: Practice 1
Microsoft® Excel:
Practice 2
Objectives:
The Learner will be able to:
1. Explain what
labels are
2. Sort Excel data
by using the labels in the header row
3. Create a Custom Sort
4. Modify the Custom Sort Order
5. Change Page
Orientation
6. Create Custom headers and footers
7. Save the spreadsheet
Sort Data
Work
with Sample Data
Open the sample
Excel list, Sales.XLS
When prompted,
SAVE to your Documents folder
Review
the Data
|
|
A
|
B
|
C
|
D
|
E
|
F
|
|
1
|
Month
|
Client
|
Category
|
Service
|
Class
|
Date
|
|
2
|
January
|
Rick
Towner
|
Private
|
Training
|
Access
|
1/12/2004
|
|
3
|
January
|
Darlene
Davis
|
Private
|
Training
|
Access
|
1/15/2004
|
|
4
|
January
|
Hometown
Community College
|
Educational
|
Training
|
Word
|
1/15/2004
|
|
5
|
February
|
Hometown
Community College
|
Educational
|
Training
|
PowerPoint
|
02/05/2004
|
|
6
|
February
|
Harmony
Kitchen And Bath
|
Corporate
|
Training
|
Excel
|
02/07/2004
|
|
7
|
February
|
Database
Consultants
|
Corporate
|
Training
|
Access
|
02/10/2004
|
|
8
|
February
|
Bay
County
|
Government
|
Training
|
Outlook
|
02/12/2004
|
Sort
the Data
Select the entire spreadsheet
and Sort the data by Month
Did the Months sort as expected or did they sort
alphabetically?
Try the Sort again: use the CUSTOM SORT and change the
Order to Custom List
Modify the Page Layout
Format the following Page
Layout Options:
Make the orientation "Landscape"
Create a Custom Header and type a sample company a name
in the center
Create a Custom Footer with the current date on the right
Save the spreadsheet and name it: Practice 2
Microsoft® Excel:
Practice 3
Objectives:
The
Learner will be able to:
1. Enter data into a Spreadsheet
2. Use AutoFill with labels, data and formulas
3. Format Cell Borders and Contents
4. Calculate the total across the rows
5. Calculate the total for each column
6. Use Conditional Formatting
Create a Time Sheet
|
|
A
|
B
|
C
|
D
|
E
|
F
|
G
|
|
1
|
Monday
|
Tuesday
|
Wednesday
|
Thursday
|
Friday
|
Saturday
|
Total
|
|
2
|
8
|
8
|
8
|
8
|
8
|
8
|
48
|
|
3
|
|
|
|
|
|
|
|
|
4
|
|
|
|
|
|
|
|
|
5
|
|
|
|
|
|
|
|
|
6
|
|
|
|
|
|
|
|
|
7
|
8
|
8
|
8
|
8
|
8
|
8
|
48
|
|
8
|
|
|
|
|
|
Overtime
|
8
|
Enter the Labels in the first row
In Cell A1 type: Monday
Use the AutoFill handle to add
Tuesday through Saturday
Calculate the Total
In Cell G1 type: Total
In Cell G2 create the equation:
=Sum(A2:F2)
Use the AutoFill handle to fill down
that equation to G6
Calculate the Daily Total
Enter sample data
in cell A2 through F2
Select Cell A7
and AutoSum the total
Use the AutoFill
handle to add this equation to Cells B7 through G7
Format the cells
Make the Labels Bold
Align all of the text Centered, in the middle of the
cells
Calculate the overtime in Cell G8
The equation in cell G8 would be: =G7-40
Use Conditional Formatting on Cell G8
Save the spreadsheet and name it: Practice 3
Microsoft® Excel:
Practice 4
Objectives:
The Learner will be able to:
1. Enter data into an Excel Spreadsheet at
least 75% of the time
2. Use Data Validation to create a DropDown
Control at least 75% of the time
3.
Rename a sheet in an Excel workbook at least 75% of the time
4.
Apply Conditional Formatting at least 75% of the time
5. Use
the Fill Down command
6.
Enter data using Drop Down Controls at least 75% of the time
Create Drop Down Controls
Create a list of employees
Type in the following information in Column A
Bill Smith
Kaylee Wild
Helen Pulaski
Corey Haas
Angelique
Riol
Select the data and name the range. In the Name Box type: Employees
Name the sheet: Employees
Create a list of locations
on another spreadsheet in the same workbook
Enter the following Locations in Column A and sort them A-Z
Ann Arbor
Brighton
Lansing
Flint
Detroit
Grand Rapids
Pontiac
Select the names and name the range. In the Name Box type: Location
Name the sheet: Location
Create a schedule on another
spreadsheet in the same workbook
Rename Sheet3: Schedule
In Cell A1 Type: Employee Name
In Cell A2, use Data Validation to create a Drop Down control
using the "Employees" source.
Include an Input
Message that says: "Select an employee from the list"
Fill down Five
rows
In Cell B1 Type: Location
In Cell B2, use Data Validation to create a Drop Down control
using "Location" as the source
Include and Input
Message that says: "Select a Location from the list."
Fill Down five
rows
Apply Conditional Formatting
Fill in 3 rows of
Employees with locations.
If the Location
is Pontiac, format the text to be GREEN. Does
Pontiac show up Green? ;-)
Save the spreadsheet and name it: Practice 4
