Instructions for Excel Payroll Lab

TECH 1110

Instructions for Excel Payroll Lab

1.  Using the tutorial found at: http://www.jmu.edu/computing/tutorials/microsoft/excel/

or any other tutorial of your choosing, review the directions for using a spreadsheet. You might want to print out the tutorial for easy reference. You will need to familiarize yourself with the named features of the Excel spreadsheet and using a tutorial is an excellent way to do this.

2.  Open Excel. You will see a new spreadsheet called Book 1. Click on File and then, Save As. In the file name box, type lastname_payroll, using YOUR last name. In the Save In box, choose the appropriate folder or drive in which to save your file. Be sure to click on the Save icon as you work on this assignment.

3.  You are now ready to begin your lab assignment. In cell A1 type, Employee. Using the right arrow, go to cell B1 and type Hours, C1 and type Rate, D1 and type Gross, E1 and type Deductions, F1 and type Net, G1 and type Tax Rate. (Any column that is too narrow for the label to fit can be widened. Position your cursor between Column A and Column B in the top row. It is the row that is not numbered. As you move the cursor to that vertical line, wait until the cursor changes from a white cross to a vertical black line with a two-headed horizontal arrow across it. Then drag the vertical line to the right to make the column wider.)

4.  Go to cell A2 and type in Jones, A3 and type in Velasquez-Smith. (This column will now need to be widened.) Go to A4 and type in Mitchell, A5 and type in La Gioia, and A6 and type in Humphrey.

5.  Now type in the number of hours worked by each employee. 25 hours for Jones, 34.5 hours for Velasquez-Smith, 51.75 hours for Mitchell, 15.5 hours for La Gioia, and 47 hours for Humphrey.

6.  In cell C2, type 15.24, in cell C3, type 10.75, in cell C4, type 5.5, in cell C5, type 21.35 and in cell C6 type in 42. Use only decimal points when needed and do not enter any dollar signs. The formatting will come later.

7.  When you entered the names, you entered labels. Labels will align to the left. When you entered the hours worked and the hourly rate, you entered values, which align to the right. (Note that the values will be used for calculations.) Now you will enter a formula that will calculate the gross pay of Jones. In cell D2, enter the formula =B2*C2 which will multiply the contents of cell B2 and cell C2. Press enter and you will see the displayed value that was calculated by the formula. If you hold down the Control key and press the tilde key, (The tilde is a horizontal wavy line which can be found just below the Esc key.) you will see the spreadsheet change to the formula view. (You can also do this by going to Tools, Options, View and click on the box to the left of Formula. You have to go back and remove the check from the Formula box when you want to go to the displayed value view.) Holding control and pressing the tilde key is like a toggle switch. It will toggle back and forth between formula view and displayed value view.

8.  Make D2 the active cell again by putting the cursor on it. You will also see D2 in the name box and the formula that you entered previously in the formula bar. Move the white cross slowly to the small black rectangle in the lower right hand corner of that cell until the cursor changes to a black plus sign. Grab the black rectangle with the plus sign and, while holding down the left mouse button, drag it down to cell D6. You have copied or replicated the formulas and all of the cells in Column D are calculated. Look at the formula view again and note that the formulas have changed to reflect the new location. This is called copying the formulas using a relative reference. The row numbers change relative to the row above as the dragging occurs. For more information about relative references, go to: http://www.fgcu.edu/support/office2000/excel/functions.html

9.  Click on G2. Enter the tax rate as a decimal. A 26% tax rate would be the decimal equivalent of .26. Click on cell E2 and enter the following formula: =D2*G$2 and then copy or replicate the formula down to cell E6. Look at the cell formulas and note what you see. Because of the dollar sign (it does not signify dollars and cents in this case. It is a special symbol that causes the 2 to stay the same.) As you always want to refer back to cell G2 and no others, you have created an absolute reference, rather than a relative one. See the following Web site for further information: http://www.jmu.edu/computing/tutorials/microsoft/excel/move/creating.shtml

10.  Now move the cursor to F2. Enter the formula =D2-E2, which calculates the Net, pay for Jones. Move the white cross to the lower right of the cell until the black + appears over the small black rectangle. Hold the left mouse button down and drag the new cursor down to cell F6, which will calculate the Net, pay for all employees.

11.  Enter the label Total in cell A7. Enter the function =sum(B2:B6) indicating that you wish to find the sum of the range of cells beginning with B2 and ending with cell B6. Move your cursor back to B7 and, using the + symbol on the lower right hand black rectangle in that cell, hold down the left mouse button and drag the cursor to cell F7 which calculates all totals for the columns between B7 and F7.

12.  Enter the label Average in cell A8. Enter the function =average(B2:B6) in cell B8. Using the drag feature calculate the average of all cells from B8 to F8.

13.  In cell A9 enter the label Largest and using the function =Max(B2:B6) display the largest number in each column. In cell A10 enter the label Smallest and use the function =Min(B2:B6) to find the smallest number in each column.

14.  Go to File, Page Setup and change the page orientation to Landscape. Select Margin and at the bottom of that box under the words Center on page, click on Horizontal and Vertical. Go to Header/Footer and click on Custom Header. In the right section, type your name, Tech 1110 and your section number. Click on OK. Then click on Custom Footer. Move your cursor to the center section. Click on the Calendar Icon, press enter and click on the Clock Icon. Go to OK and now go to print preview. You will see that your spreadsheet is centered and your name and section are in the upper right hand corner and the current date and time at centered at the bottom.

15.  Go back to Setup and Page. In the Scaling area, either increase or decrease the value from 100 to the best number that will enlarge the spreadsheet while keeping it on one page. Right mouse click the tab at the bottom of the page that is labeled Sheet 1. Go to Rename and call it payroll. Highlight the cells from A1 to G10. Go to Edit and click on Copy. Click on the Sheet 2 tab at the bottom of the spreadsheet. Put the cursor on cell A1. Go to Edit and Paste. You have now copied the spreadsheet and all of its labels, values, formulas and functions to a new sheet. Rename it Payroll Formatted. Go back to the Payroll sheet and press Esc to deselect the Copy feature. Return to the Payroll Formatted sheet.

16.  Go to cell G2 and enter .345. Notice how all of the values in columns E and F change due to the new tax rate.

17.  Highlight the cells from A1 to G1. Move your cursor to the Formatting Toolbar and find the formatting feature called Align Right. Click on it and you will note that all of the labels now appear over the values, aligned to the right. Highlight the range of cells A1:A10 and align these labels to the right. Highlight the cells that indicate the number of hours worked by each employee. Search the formatting bar for the Comma Style icon and click on it. Using either the Increase Decimal or the Decrease Decimal icon, round off the hours worked to one decimal place.

18.  Highlight the range of cells from C2:F10. Go to Format, Cells, and Accounting and select 2 decimals places and then go to OK. Enlarge any columns that may have a symbol such as ######## which indicates that the column is too narrow to display the complete number.

19.  Click on G2 and using the Percent Style icon, format for percentage. Using the Increase or Decrease Decimal icon, round off to the nearest tenth of a percent.

20.  With the cursor on A1, click on Insert, Row. In cell A1, type in a title such as Mary Sullivan’s French Bakery, using your name and your business. Highlight the cells from A1 to G1 to designate the cells in which you wish to center your title. By moving your cursor over the Formatting Toolbar, find the icon called Merge and Center. Once you click on it, the title will be centered across the columns that you selected. Change the font of the title and enlarge it.

21.  Go to File, Page Setup and Sheet. Click on Row and Column Headings, then OK. Go back to your spreadsheet and highlight cells A1 to G11. Find the Borders icon and open it by clicking on the black triangle on the right side. Choose the second grid in the bottom row called All Borders and click on it. With the cells still highlighted, go back to Borders and this time click on Thick Box border in the lower right hand corner.

22.  Center the page vertically and horizontally, add grid lines and row and column headings and change the orientation to landscape. Put a header and footer on the spreadsheet and enlarge the size of the spreadsheet to where it is as large as it can be and still have all the columns on one page. Go to the formula view and size the spreadsheet down so that all of the columns show on one page. Staple together and submit.