Excel Payroll Formulas
Learn how to use the most common excel payroll formulas to calculate hours worked, employee deductions, and more.
Updated on October 5th, 2021
The SMB Guide is reader-supported. When you buy through links on our site, we may earn an affiliate commission. Learn more
Excel payroll formulas make it easy to calculate important figures for each pay period, including hours worked, income tax deductions, and vacation pay. Formulas can be used anywhere in an Excel spreadsheet to find a variety of sums.
By entering a formula into a cell in your spreadsheet and then pressing the enter key, you can instantly calculate the number that you are looking for. You can even format a cell so that it automatically displays a result based on information that you have entered into other cells.
Excel Payroll Calculator - Free Download
Use our Excel Payroll Calculator to calculate pay for a pay period.
Common Excel Payroll Formulas:
Formula | Description |
|---|---|
=(A1)+(B1) | Used for simple addition to find the total of hours worked, overtime hours, and vacation time. This formula can also be used to find gross pay, and to calculate net pay by subtracting deductions. |
=A1/B1 | Calculates percentages so that employers can find numbers like the amount of income tax to deduct from gross earnings. The part is divided by the whole. Click the "Percent style" button so that the resulting number is automatically displayed as a percentage in your spreadsheet. |
How to Use Excel Payroll Formulas:
1. Gross earnings.
To calculate the gross earnings of an employee during a certain pay period, multiply the cell with the number of hours worked, by the cell with the rate of pay, using the format =(A1)*(B1).
2. Deductions.
Deductions like income tax or health benefits can be calculated by subtracting their percentage from gross earnings. For example, if you have gross earnings of $550 in column A1, and the income tax that must be deducted is 12%, you can enter =(A1)*0.12 in the cell that you would like the deduction to appear in. You can do the same for any other deductions and then add them together to calculate total deductions.
To find net pay, subtract what is in the cell for total deductions from the cell with gross pay. For example, =(A1) - (B4).
3. Paid time off.
If your employees are entitled to paid time off, then you likely have a system for accruing vacation time for every day or hour worked. If it is accrued hourly, you first need to divide the total number of vacation hours that they are entitled to in a year by the number of working days in the year.
For example, if an employee works 30 hours per week and there are 365 working days in the year, divide 30 by 365. 30/365=0.08. This means that for every day worked, the employee accrues 0.08 days off. With 0.08 as your multiplier, you can enter the days worked in the pay period and multiply them by 0.08. The result is how much PTO has been accrued in the pay period.
How Do You Calculate Payroll?
Learn how to calculate payroll for your business. Includes information about calculating hours, wages, and deductions, as well as frequently asked questions.
Oct 1, 2021
FAQs:
How do I calculate payroll hours in Excel?
You can use the SUM formula to add up hours in two or more cells. You can also use a payroll calculator to simplify your payroll calculations.
What is the formula for calculating in Excel?
Excel has a variety of built-in formulas.
How do I calculate gross pay in Excel?
Gross pay is simply the hours worked during a pay period multiplied by the rate of pay. You can multiply these two cells to get total gross pay.