Buzz
Today, Mytour will walk you through the process of calculating payroll for employees in the USA using Microsoft Excel. While setting up a payroll sheet from scratch can be quite exhausting, Microsoft provides a convenient payroll template for Excel, compatible with both Windows and Mac computers.
Steps
Create a Payroll Sheet
Visit the Payroll Calculator website. Open https://templates.office.com/en-us/Payroll-calculator-TM06101177 in your computer's web browser.
- This spreadsheet is a free Excel template provided by Microsoft.
Click on Download. This blue button is located near the bottom of the window. The template will begin downloading to your computer.
- Depending on your browser, you may need to select a save location and click Save before the file downloads.
Open the template. Double-click the downloaded Excel file to open the template in Excel.
Click on Enable Editing. This button is located in the yellow bar at the top of the Excel window. The Excel file will be unlocked, allowing you to make edits.
Save the document. Before editing the template, press Ctrl+S (Windows) or ⌘ Command+S (Mac), enter a file name (e.g., "Payroll 5.12.2018"), and click Save. This ensures the payroll sheet is automatically saved as a separate file. You can then proceed with payroll calculations.
Enter employee information
Click on Employee Information. The heading is located at the bottom left corner of the Excel window. This ensures you are on the Employee Information sheet.
Add employee names. Enter the employee's name in the first empty cell under the "Name" column.
Enter the hourly wage. Input the amount the employee is paid per hour in the first empty cell of the "Hourly Wage" column.
Enter employee tax information. Ensure you have the employee's tax details, then fill in the cells under the following headings:
- Tax Status – The tax status number (usually "1") as shown on the employee's W-2 form.
- Federal Allowance – The federal allowance number, which determines the employee's tax bracket, typically found on the W-4 form.
- State Tax (Percentage) – The percentage of state tax applicable in your state.
- Federal Income Tax (Percentage) – The federal income tax percentage based on the employee's tax bracket.
- Social Security Tax (Percentage) – The current Social Security tax percentage.
- Medicare Tax (Percentage) – The current Medicare tax percentage for U.S. government elderly care programs.
- Total Taxes Withheld (Percentage) – The percentage of total taxes withheld, automatically calculated after filling in other tax fields.
Determine employee deductions. This depends on the employee's benefits, investments, etc.:
- Insurance Deduction (Dollars) – The dollar amount withheld for insurance.
- Other Regular Deduction (Dollars) – Any other regular deductions or amounts withheld.
Add additional employee details. Once all employee information has been entered, you can proceed with calculating payroll.
Calculate Payroll
Click on the Payroll Calculator tab at the bottom of the page. The payroll sheet will open.
Locate the employee. Identify the first employee whose information you entered on the Employee Information sheet. Their name will appear at the top of this page.
Enter hours worked. In the "Regular Hours Worked" column, input the number of hours the employee has worked (e.g., 40) during the pay period.
Add vacation or sick leave hours if applicable. If your employee has used vacation or sick leave, note the corresponding hours in the "Vacation Hours" or "Sick Hours" column.
Enter overtime hours and rate. If your employee has worked overtime (e.g., over 40 hours in a week), input the overtime hours in the "Overtime Hours" column, then enter the overtime rate (in dollars) in the "Overtime Rate" column.
- The overtime rate is typically 150% higher than the employee's regular rate ("time and a half").
Include additional deductions if necessary. In the "Other Deduction" column, enter any dollar amounts for deductions outside the regular ones.
- For example, if the employee has taken a deduction for equipment purchases, input the amount here for a one-time payment.
Review the employee's net pay. The "Net Pay" column will display the employee's take-home amount; if this figure seems reasonable, you have successfully calculated their payroll.
- You can also verify pre-tax amounts in the "Gross Pay" column.
Proceed to calculate payroll for other employees. For each person listed under "Employee Name," adjust the data to determine their net pay.
- You can review employee pay stubs under the PAYROLL PAYSTUBS or Individual Paystubs tabs at the bottom of the page after completing the calculations.
Tips
- After completing a payroll cycle, save the worksheet before clearing it and creating a new payroll sheet.
Warnings
- Avoid deleting any pre-formatted cells (e.g., cells with existing formulas) as this may cause errors in the payroll sheet.
