Chapter 2 Formulas, Functions, and Formatting
I. Enter Data
1. Enter titles in A1 & A2 as below:
2. Enter Column Titles as below: (wrap text for cells D3 & E3)
3. Enter More Data as below: (You can copy and paste it to your worksheet at A4)
| Charvat, Emily | 3/3/2009 | 1 | 65.25 | 20.5 |
| Chen, Bin | 6/14/2010 | 2 | 80 | 25.85 |
| Felski, Noah | 10/11/2008 | 0 | 64.5 | 12.6 |
| Kersey, Jane | 3/4/2011 | 1 | 68.5 | 21.45 |
| Merna, Thomas | 1/15/2010 | 3 | 78.25 | 22.6 |
| Pollitt, Sherry | 11/15/2008 | 2 | 49.25 | 18.25 |
| Prasad, Rao | 2/15/2008 | 0 | 33.5 | 9.35 |
| Washington, Yolanda | 5/11/2006 | 2 | 79.25 | 23.75 |
| Zica, James | 4/14/2011 | 1 | 80 | 19.65 |
| Totals | ||||
| Average | ||||
| Hightest | ||||
| Lowest |
Note that if you
see ##### after you paste it, it means that there is
no enough room to display numbers.
How will you fix it? (Think!!!)
II Do Calculations
1. Calculate the first person's information by the following formula:
Gross Pay = Hours Worked * Hourly Pay Rate
Federal Tax = 0.22 * (Gross Pay - Dependents * 24.32)
State Tax = 0.04 * Gross Pay
Net Pay = Gross Pay - (Federal Tax + State Tax)
Tax % = (Federal Tax + State Tax ) / Gross Pay
2. Format Tax% into % format with 2 decimal places.
3. Highlight the five formula you just created and hold the special corner to drag it down for other people.
4. Calculate the totals for columns D, F, G, H and I on the cells D13, F13, G13, H13 and I13.
5. How do you get total for Tax %? (Think!!!)
6. Calculate the average, highest, and lowest for Dependents on the cells C14, C15, and C16.
7. Copy the 3 formula to right.
8. Delete cell J14 since the average of Tax% is mathematically invalid.
III Format the Worksheet
1. Change the Workbook Theme: Click Page Layout / Themes / Trek
2. Format Titles:
- Make the title and subtitle in the center of each row.
- Choose Title style for both cells. (Home / Cell Styles / Title)
- Decrease the size of subtitle to 16.
- Change background color for
both titles.
- Apply "Thick Box Border" .
3. Choose Heading 3
cell style for column titles (center each column title)
and total cell style for
the row total.
4. Give boldface to cells A14, A15 and A16.
5. Format Dates:
6. Center C4 to C12.
7. Apply $ to cells E4 to I4 and F13 to I13.
8. Apply Comma to E5 to I12 and D4 to D16.
9. Apply a Currency Style Format to E14 to I16.
10. Apply Conditional Formatting: Select D4 to D12. Color the background for the cells >70.
11. Make each column width large enough.
12. Click Page Layout / Orientation / Landscape for ready to print. (Use print preview to see out put.)
13. Copy sheet 1 to sheet 2.
14. Rename sheet 1 as Biweekly Payroll Report.
15. Rename sheet 2 as formula and show formula for this sheet. (Ctrl + ~)
Save your file and send it to your instructor.