In Excel, open a blank worksheet.
In cell A1, type Payroll- 2019. Make the font bold, 14 point (The type face is your choice).
In A2, type Weekly Payroll- 9/20 9/26 12 point, not bold.
In line 3 type the following:
A3, type Last Name,
tab to B3 and type First Name, continue to press tab to move to each cell to the right.
C3 Dependents
D3 Pay Rate
E3 Hours Worked
F3 Gross Pay
G3 Federal Tax
H3 State Tax
I3 Net Pay
In the records for lines 4 through 9, fill in the data that you make up with six employee names, the number of dependents (children), the pay per hour and the number of hours worked for the week. The rest of the cells remain blank. You will use formulas to generate the data for these.
Once you have data in columns C through E, use the following formulas.
In cell F4, Gross Pay type =D4*E4 You are multiplying the pay rate and hours worked. The * is the multiplication sign. Always begin a formula with the equal sign =.
In cell G4, type =0.22* (F4-C4*24.32). This gives a tax credit for each dependent.
In cell H4, type = 0.04*F4.
In cell I4, type =F4-(G4+H4) Study each formula to understand what cells the data is from.
Once the data is generated for one cell, you can fill the formulas for rest of the column using the Fill Handle. Click on cell F4 and a green box appears. In the lower right corner is a little green box. Hover your curser until it turns into a solid + Left click and hold, then drag to the bottom of the employee list. Fill in the data for the rest of the columns F through I.
Next, you will generate totals for each column.
Click cell E11, just below the last employee in the hours worked column and click the Auto Sum in the Editing tool (far right) on the Home Tab. See the screen shot for where we are up to this step.
Click the cell with the sum and drag the Fill Handleto the right to sum all columns.
Now you will use the Conditional Formatting tool to analyze the data.
Select the Pay Rate data for all employees, excluding the sum total.
From the Home tab, go to Styles and click the down arrow for the Conditional Formatting tab.
Select Top/Bottom Rules, Top 10%. You will find who earns the highest hourly rate. This is especially helpful for a spreadsheet with many employees.
Use at least two more of the conditional formation options and choose different color schemes for each.
Select the Gross Pay column, excluding the sum total. On the Home Tab, go to the E or Sum symbol on the Editing Tab and select the Average option. Do the same with the Maximum and Minimumoptions. See the screen shot below.
Now, you make the spreadsheet look better.
Left select cell A1, hold the mouse and drag to cell I1. On the Home tab Alignment tab, choose Merge & Center. Do the same with row A2.
Fill the rows A1 and A2 with colors of your choice.
Add a bottom border from the Font Tab.
