Intermediate 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	Deeter	Poohbah	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: Excel Intermediate Practice 1

Intermediate 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: Excel Intermediate Practice 2