INFORMATION TECHNOLOGY SURVEY

Unit 4-- Microsoft Excel

Lesson 1

In this lesson you will learn the basics of using Microsoft Excel-a spreadsheet software program. You will learn how to enter data, save a file, enter labels and values, print a spreadsheet and close a file.

You must use the suggested file name when saving your documents, as you may be using these in later lessons. Each document you create must contain your name/document name/date. This is done using the header/footer option and instructions for doing this are below.

You should create folder for saving lesson 1 files. You may do this as follows:

1. Open Excel
2. Click on the File menu
3. Click on Save As
4. In the Save In section at the top of the box, use the down arrow to select the proper location for your files.
5. Click on the ion that looks like a file folder with a star "Create New Folder"
6. Name the folder Excel lesson 1
7. When saving documents for this lesson, make sure it says "Excel Lesson 1" in the Save In section

How to create a footer for your documents

1. For each of your documents you must create a footer showing your name/document name/ date. This is how to do this.
2. Click on View menu
3. Click on Header and Footer
4. Click on the Header/Footer Tab
5. Click on Custom Footer
6. Type your first and last name in the left section
7. Press tab to move to the center section
8. To insert the document name, click on the icon with a turquoise green "X" on a piece of paper (this will put a code of "&[File] in the box and will print your document name
9. Insert the date by clicking on the "insert date" icon (has a 7 on it). This puts a code of "&[Date] in the box.
10. Click close

Assignment 1

Basic Excel Spreadsheet

1. You are to create Assignment 1 (Employee Payroll)
2. Begin a new document
3. Type the spreadsheet as shown, being sure to move from cell to cell by pressing ENTER, the TAB key or an Arrow key.
4. To type the headings so they will wrap within a cell, do as follows:
   1. Select the row by clicking on the row number (3)
   2. Click on FORMAT, CELLS
   3. Click on the ALIGNMENT Tab
   4. Place a check mark in the box next to Wrap Text (at bottom of screen)
   5. Click OK
5. Save the file as EMPLOYEE PAYROLL
6. Spell check
7. Include the proper footer (directions given above)
8. Print with gridlines and row and column headings which you do as follows:
   1. Click on FILE, PAGE SETUP
   2. Click on the SHEET tab
   3. Put a checkmark in the box for gridlines and the box for row and column headings
   4. Click OK
9. Print one copy

Assignment 2

Creation of Spreadsheet

1. You are to create Assignment 2 (Income) following directions on the document and given below.
2. Begin a new spreadsheet
3. Type the spreadsheet as shown
4. Be sure to wrap text to make the column headings appear on two lines
5. Auto Fit the columns after completely typing the information:
   1. Put your cursor on the longest item in the column
   2. Click on Format
   3. Click on Column
   4. Click on Auto Fit
6. Include a proper footer
7. Format the money columns for currency by:
   1. Highlighting all the numbers
   2. Click on the $ icon to make the numbers with 2 decimals and a $.
8. Spell check
9. Save the document as INCOME (in your folder for Excel Lesson 1)
10. Print the spreadsheet with gridlines and row and column headings
11. Print one copy of the document

Assignment 3

Create a spreadsheet

1. You are to create Assignment 3 (LABOR) making corrections as shown
2. Begin a new spreadsheet
3. Type the information exactly as shown on the sample.
4. Include the proper footer
5. Save as LABOR
6. Preview the document
7. Print one copy with gridlines and row and column headings

Assignment 4

Create and design your own spreadsheet

1. You are to create Assignment 4 (Grades) as shown in the box below

1. Read the directions carefully
2. Use the Column Headings of Student Name, Exam 1, Exam 2, Exam 3
3. Include an appropriate title
4. Save as GRADE (in your folder for Excel Lesson 1)
5. Spell check
6. Include a footer
7. Preview the document
8. Print one copy with gridlines and row and column headings

Assignment 5

Create a spreadsheet

1. You are to create Assignment 5 (TUITION) as shown
2. Begin a new spreadsheet
3. Type the spreadsheet as shown being sure to use the Wrap Text Feature on the column headings.
4. Format the columns with numbers for currency
5. Save as TUITION (in your folder for Excel Lesson 1)
6. Spell Check
7. Include the proper footer
8. Preview the document
9. Print one copy with gridlines and row and column headings

End of Lesson 1

You should have a print out of the following documents:

• EMPLOYEE PAYROLL
• INCOME
• LABOR
• GRADE
• TUITION

Top

Lesson 2

In this lesson you will learn to enter basic formulas, change font face and size, add lines and shading to a worksheet. You will also learn how to insert columns and rows and sort alphabetically and numerically. 

You must use the suggested file name when saving your documents, as you may be using these in later lessons.  Each document you create must contain your name/document name/date. This is done using the header/footer option and instructions for doing this are below.

1. You should create folder for saving lesson 2 files. Refer to lesson for help in doing this.
2. Every spreadsheet must contain the proper footer with your name, document name and date on it.

Assignment 1 – Using Formulas; Formatting Data

1. Open the worksheet INCOME
2. Use Save As to rename the workshop INCOME 1
3. Type the word “TOTAL” in cell A16
4. Find the Net Change for each item listed as follows:
1. Put your cursor in cell E6
2. Enter a formula to subtract Year 1 from Year 2 (example =d6-c6)
3. Copy the formula down column E
5. Enter a Total for Year 1 column by using the Auto Sum icon
6. Enter a Total for Year 2 Column and the Net Change Column
7. Center Align all column headings
8. Center all the data in column one
9. Format the total cells for Currency with 2 decimal places ($00.00)
10. Format the following as indicated
1. Main Title (Jackson Video Company) = sans serif 15 point bold
2. Secondary Title (Sales Income) = sans serif 13 point
3. The column headings = sans serif 12 point bold
4. All the data = serif 10 point
11. Save the document
12. Print one copy

Assignment 2 – Use Formulas, and Format Data

1. Open your document named EMPLOYEE PAYROLL          
2. Use Save As to rename the document as EMPLOYEE PAYROLL 1
3. Make the changes as shown on ASSIGNMENT 2 below.
4. Enter the Labels TOTALS, AVG HRLY RATE and AVG HOURS WORKED as shown on sample
5. To find the GROSS PAY for each employee:

   ·        Enter a formula using cell names to multiply HOURLY RATE x HOURS WORKED

6. To find the amount of Social Security use a formula to multiply Social Security x 7.7%
7. To find the amount of Federal Withholding tax use a formula to multiply Federal Withholding x 20%
8. To find the Net Pay for each employee:

   ·        Enter a formula to take Gross Pay minus Social Security minus Federal Withholding Tax

9. Use the Auto Sum feature to enter a total for the Gross Pay, Social Security, Federal Withholding Tax, and Net Pay columns
10. To find the AVG HRLY RATE
1. Put your cursor where you want the answer
2. Type =Average(b5:b10) [the cells where the hourly rates are shown)
11. To find the AVG HRS WORKED
1. Put your cursor where you want the answer
2. Type =Average(c5:c10) [the cells where the hours worked are shown)
12. Make the font changes as shown below:
1. Main title=Sans Serif 16 point bold
2. Column headings = Sans Serif 13 point bold
3. Bold total amounts
13. Center the Main Title over the entire worksheet by
1. Selecting (highlighting) row 1 from Column A through Column G
2. Click on the Merge and Center Icon
14. Center align column headings
15.  Adjust the widths of the columns as needed to make the column headings fit each column
16. Format all money amounts for two decimal places.
17. Format TOTALS and AVG HRLY RATE for currency ($ with two decimals)
18. Format AVG HRS WORKED for no decimal places (no $)
19. Spell check
20. Save the document
21. Print one copy of the document

ASSIGNMENT 2 – EMPLOYEE PAYROLL 1

Assignment 3 – Copy Formulas, Insert and Delete Columns and Rows

1. Open your file named LABOR
2. Use Save As to rename this file LABOR 1
3. Make the changes as shown on ASSIGNMENT 3 and outlined below
4. Insert one row after the main title and add an additional subheading “Summary of Repair Activity”
5. Center and change the font on the main title to 16 point bold
6. Change the font on the 2nd title to 14 point bold
7. Change the font on the 3rd title to 9 point
8. Center-align the column headings and change them to reverse text (black shade and white text) as shown
9. Find the LABOR COST by multiplying Labor Hours x $40.  Copy the formula down the column for each customer.
10. Find TOTAL PARTS/LABOR by adding Labor Cost and Parts.  Copy the formula down the column for each customer.
11. Enter a formula to find the SALES TAX by multiplying TOTAL PARTS/LABOR by 7.65%.  Copy the formula down the column for each customer.
12. Enter a formula to find the TOTAL BILL  (remember Sales Tax is added) when figuring final costs.  Copy the formula down the column for each customer.
13. Center align the data in Column A
14. Format all money amountswith a comma and two decimal places
15. Format all TOTALS for currency
16. Auto fit the data in each column
17. Change the labor hours for customer 507 to 18
18. Bold and double underline the TOTALS row
19. Save the worksheet
20. Print one copy
21. Preview the document
22. Print one copy with gridlines and row and column headings

Assignment 4 – Copy Formulas, Insert and Delete Columns and Rows

1. Open your file Employee Payroll 1
2. Use Save As to rename the file to Employee Payroll 2
3. Make the changes as shown on ASSIGNMENT 4 and outlined below
4. Change the orientation for the worksheet to Landscape (File, Page Setup, Paper Size)
5. Insert a new column where indicated.
6. Enter the column heading UNION DUES as the heading for the new column
7. Delete rows 14 and 15
8. Enter TOTALS in Column A as shown
9. Enter a formula to figure Union Dues.  They are 1% of the Gross Pay
10. Copy the formula for Union Dues down the column
11. Total the Union Dues Column
12. Re-compute the NET PAY by entering a formula to subtract Social Security, Federal Withholding, and Union Dues from Gross Pay
13. Change the hourly rate for Appleby to 6.75
14. Apply a desired automatic format to the spreadsheet
15. Save the worksheet
16. Spell check
17. Preview the document
18. Print one copy with gridlines and row and column headings

Assignment 5 – Creating a spreadsheet

1. You are to create ASSIGNMENT 5 (Bowling) as described below
2. Begin a new spreadsheet and read the following and then create the appropriate spreadsheet.
3. The Generic High School fall bowling tournament scores are in!  Using the information given below create a spreadsheet for this tournament.
4. For each team you will need to show:
1. Total for Game 1, 2, and 3
2. Grand total for each team (total of all three games)
5. For each member you will need to show:
1. Grand total for all 3 games
2. Average of each member’s scores
6. Red Team

• Angelo: 107, 93, 114
• Bridget: 104, 87, 105
• Calvin: 125, 115, 140
• Devin:  114, 126, 147

7. Green Team

• Enrique:  117, 100, 115
• Fidel:  147, 152, 130
• Greta:  95, 87, 102
• Harry: 111, 110, 115

8. Blue Team

• Ingrid:  164, 157, 148
• Juan: 103, 98, 108
• Karla: 105, 123, 131
• Louis: 85, 88, 94

9. Include an appropriate Title for your worksheet and appropriate column headings
10. Use fonts, borders, and shading to enhance the looks of your spreadsheet
11. Include a proper footer
12. Save as BOWLING
13. Spell Check
14. Preview the document
15. Print one copy
16. Here are some scores that were reported incorrectly.  Change the scores as indicated below.  Save as BOWLING2

• Angelo’s game two score should be 101
• Bridget’s game one score should be 94
• Calvin’s game two score should be 124
• Enrique’s game three score should be 105
• Harry’s game one score should be 107
• Karla’s scores are reversed; they should be 123, 131, 105

17.           Preview the document and print a copy.

End of Lesson 2

You should have a print out of the following documents:

• INCOME 1
• EMPLOYEE PAYROLL 1
• LABOR 1
• EMPLOYEE PAYROLL 2
• BOWLING
• BOWLING  2

Top

Lesson 3

In this lesson you will learn to create graphs using the information from a worksheet. You will create the following types of graphs; column, pie, and line.

You must use the suggested file name when saving your documents, as you may be using these in later lessons.  Each document you create must contain your name/document name/date. This is done using the header/footer option and instructions for doing this are below.

1. You should create folder for saving lesson 3 files. Refer to lesson for help in doing this.
2. Every spreadsheet must contain the proper footer with your name, document name and date on it.

Assignment 1 – Create a Column Graph and Pie Chart

1. Start a new spreadsheet
2. Save the worksheet as BASEBALL
3. Create the worksheet as shown in ASSIGNMENT 1 below

Graph 1

1. Create a bar graph as shown in UNITS COLUMN showing the total sales of each product in June (Perhaps print a copy to use as a model).
2. Select the Column Headings (Product and June)
3. Holding Down the CTRL key, select the remainder of the worksheet, beginning with row 6 through row 10
4. Create a chart by pressing INSERT, CHART
5. Select Column and proceed through the Chart Wizard
6. Use Wilson Athletics Unit Sales – June as the Title
7. On Step 4 of Wizard, click on AS NEW SHEET and type in UNITS COLUMN for title
8. Click on the Title and Press Enter after Wilson Athletics so the title is on two lines
9. Add a Footer as shown on the sample
10. Preview Column Graph
11. Print a Copy

Graph 2

1. Click on Sheet 1 at bottom of screen so you are back at the spreadsheet for Wilson Athletics
2. Create a Pie Chart as shown in PIE CHART (Perhaps print a copy to use as a model).
3. Highlight A6 through B 10 (do not include column headings) and click on INSERT, CHART.
4. Click on Pie Chart and proceed through the Chart Wizard
5. Use Wilson Athletics Unit Sales – June as the Title
6. Click on the Data Labels tab and Click on Show Percent
7. On Step 4 of Wizard, click on AS NEW SHEET and type in UNITS PIE for title
8. Fix the title so it is on two lines
9. Add a footer as shown
10. Print one copy
11. Save the file

Assignment 2 – Create a Column Chart and a Line Chart

1. Start a new spreadsheet
2. Save the worksheet as BATTING
3. Create the worksheet as shown in ASSIGNMENT 2 below.

Graph 1

1. Create a line graph as shown in LINE GRAPH showing the total use for the month of June (Perhaps print a copy to use as a model).
2. Select the Column Headings starting with A4 through G4
3. Holding Down the CTRL key, select the rows 6 and 7
4. Create a chart by pressing INSERT, CHART
5. Select Line and proceed through the Chart Wizard
6. Type Batting Cages Usage for June as title
7. On Step 4 of Wizard, click on AS NEW SHEET and type in BATTING LINE for title
8. Add a Footer as shown on the sample
9. Preview Line Graph
10. Print a Copy

Graph 2

1. Click on Sheet 1 at bottom of screen so you are back at the spreadsheet for Wilson Athletics
2. Create a Pie Chart as shown in BATTING COLUMN (Perhaps print a copy to use as a model).
3. Select the Column Headings starting with A4 through G4
4. Holding Down the CTRL key, select the rows 6 and 7
5. Click on Column and proceed through the Chart Wizard
6. Type Batting Cage Usage for June as the Title
7. On Step 4 of Wizard, click on AS NEW SHEET and type in BATTING COLUMN for title
8. Change the font and size for the title as you prefer
9. Add a footer as shown
10. Print one copy
11. Save the file

Assignment 3 – Create a chart or graph

1. Open the file BOWLING 2
2. Create a column chart for the Red Team showing the three scores for each team member.
3. Add the necessary title
4. Save the chart at COLUMN BOWLING
5. Provide the necessary footer
6. Preview your chart and print one copy

End of Lesson 3

You should have a print out of the following documents:

• BASEBALL (spreadsheet)
• UNITS COLUMN (graph)
• UNITS PIE (chart)
• BATTING (spreadsheet)
• BATTING LINE (graph)
• BATTING COLUMN (graph)
• BOWLING COLUMN (graph)

Top