Download file hw_excel_L03P58.xls and open it in Microsoft Excel:
Save the document under new name, PayrollRegister.xls, using Save As menu option.
Your job is to modify this spreadsheet to use named ranges instead of relative cell references. This will require both removal of the old formulas, as well as entering the new ones.
First, populate range of values A7:A13 with a missing sequence of employee numbers. Use Auto Fill feature to enter these numbers.
The next step is to define the named ranges that will be later used in formulas and calculations. Proceed as follows:
Select range of values entered in the Regular Earnings column. The range should be D7:D13. Click Insert menu, Name, Define, and enter name for this range: Earnings.
Select range of values entered in the Overtime column. The range should be E7:E13. Click Insert menu, Name, Define, and enter name for this range: Overtime.
Select range of cells in the Gross Pay column, F7:F13, click Insert menu, Name, Define, and enter name for this range: GrossPay.
Select range of values entered in the Federal W.T. column. The range should be G7:G13. Click Insert menu, Name, Define, and enter name for this range: FederalWT. (Please note that use of dots in range names is not allowed, so you may need to manually remove the dots from the suggested name in older versions of Microsoft Office.)
Select range of cells in the Social Security column, H7:H13, click Insert menu, Name, Define, and enter name for this range: SocialSecurity.
Select range of cells in the Medicare column, I7:I13, click Insert menu, Name, Define, and enter name for this range: Medicare.
Select range of cells in the Total Deductions column, J7:J13, click Insert menu, Name, Define, and enter name for this range: TotalDeductions.
Select range of cells in the Net Pay column, K7:K13, click Insert menu, Name, Define, and enter name for this range: NetPay.
The next step is to enter new formulas into calculated fields of the worksheet:
Select GrossPay from the Name Box of the worksheet in the upper left corner of the Excel grid. This should immediately select the corresponding range, F7:F13. Hit the Delete key to clear old formulas. In the Gross Pay column, click cell F7 and enter new formula =Earnings+Overtime to calculate the employee gross pay. Propagate this formula down to each cell in Gross Pay column using the Auto Fill feature.
Select SocialSecurity from the Name Box. This will select the corresponding range, H7:H13. Hit Delete key to clear old formulas. In the Social Security column, click cell H7 and enter new formula =GrossPay*6.2% to calculate the employee Social Security tax amount. Propagate this formula down to each cell in Social Security column using the Auto Fill feature.
Select Medicare from the Name Box and clear old formulas by pressing the Delete key. In the Medicare column, click cell I7 and enter formula =GrossPay*1.45% to calculate the Medicare tax amount. Propagate this formula down to each cell in Medicare column using the Auto Fill feature.
Select TotalDeductions from the Name Box and clear old formulas by pressing the Delete key. In the Total Deductions column, click cell J7 and enter formula =FederalWT+SocialSecurity+Medicare to calculate the deductions. Propagate this formula down to each cell in Total Deductions column using the Auto Fill feature.
Select NetPay from the Name Box and clear old formulas by pressing the Delete key. Using the same techniques as above, enter the formula =GrossPay-TotalDeductions in each cell of the Net Pay column, K7:K13.
Now it is time to compute the summary values for each of the columns:
Click cell D15 and enter formula: =SUM(Earnings), which calculates the total for Regular Earnings column.
Click cell D17 and enter formula: =AVERAGE(Earnings), which calculates the average earnings in given payroll period.
Click cell D18 and enter formula: =COUNT(Earnings), which calculates the total count of entries in the Regular Earnings column.
Repeat the last three steps for each remaining Total, Average, and Count fields under Overtime, Gross Pay, Federal W.T., Social Security, Medicare, Total Deductions, and Net Pay columns.
IMPORTANT! Compare the values on the original spreadsheet with the new version, which is now using named cell ranges inside modified formulas. Verify that all calculated values are exactly the same on both payrolls. To complete this step, you may need to print out copies of both versions and compare them side-by-side.
When done, save your document, close it, and exit Excel.
Submit file PayrollRegister.xls via e-mail attachment sent to:
Igor Kholodov Igor.Kholodov@bristolcc.edu