Introduction to Excel Template for Payroll

Payroll is the process of calculation and distribution of the employees’ wages in an organization. Most businesses use software like ADP or Tally for payroll or hire some outsourcing companies to provide them the payroll services. However, this consumes a lot of time and manpower. So businesses these days prefer to do payroll manually, which is done via MS Excel. In order to run payroll in Excel, we require a standardized payroll Excel template that can be used month to month. The template should consist of tabs for each month with prefilled information like tax rates and formulas that can calculate the employee’s salary, taxes, and deductions.

Below is some general information that is included in most of the payroll templates:

Employee details like:

Employee Name
Date of Joining of employee
Wages or the pay of the employee
Number of hours worked by the employee
Leaves taken by an employee
Overtime rate of the employee
Total overtime hours worked by an employee
Gross pay paid to the employee
Income tax and other deductibles
Statutory payments made
Taxable benefits
Net pay paid to the employee

How to Create Payroll Template in Excel?

Let us say we wish to create or generate a payroll template in Microsoft Excel. Now following steps can be used to do so (basically in Excel 2013):

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)

Price
View Courses

120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,452 ratings)

Open a new workbook in Microsoft Excel and save it:

Now create the following column names in a fresh worksheet:
Employee Name: Consisting of a list of employees’ names
Pay: Consisting of the employee’s wages or pay per hour
Total Hours Worked: Consisting of the total number of hours worked by an employee in a month
Overtime: Consisting of an employee’s overtime rate per hour
Total Overtime Hours: Consisting of the total number of overtime hours worked by an employee in a month
Gross Pay: This is the monthly payment amount to the employee without any deductibles
Income Tax: This is the tax that is payable on gross pay
Other Deductibles (If any): These are the deductibles other than the income tax
Net Pay: This is the pay or salary the employee will receive after all the deductions from the gross pay.

Now add these details of all the employees column-wise, like employee name in column A, per hour pay rate in column B, and so on till column E, i.e. till Total Overtime Hours. The following screenshot illustrates this:

So we can see in the above screenshot that we have added the required employee details under relevant column names in the table created. Pay and overtime rate are on an hourly basis, while overtime hours, total hours worked, gross pay, and net pay are all on a monthly basis.

Now we calculate the Gross Pay, which is the sum of the product of per hour pay, total hours worked and per hour overtime rate, total overtime hours, i.e. Gross Pay can be calculated as Gross Pay= (Pay * Total Hours worked) + (Overtime* Total Overtime Hours). The following screenshot illustrates this:

Now income tax is to be calculated on gross pay. Let us consider an 18% tax for all employees. So income tax can be calculated as Income Tax = (0.18 * Gross Pay). The following screenshot illustrates this:

Now other deductibles, if any, like some loans or health insurance premiums, can be mentioned under this. However, it would be zero for an employee if there is no other deductible. The following screenshot illustrates this:

Now net pay is the final amount that an employee gets in hand after all the deductions from gross pay are made. So, if we deduct income tax and other deductibles from gross pay, we will get the net pay for an employee. So, Net Pay can be calculated as Net Pay= (Gross Pay – (Income Tax + Other Deductibles)). The following screenshot illustrates this:

So this is how a payroll template is created by ourselves in MS Excel.

This way we can create the payroll for all other employees by adding their details to the table created and then calculating their wages, tax, etc. We can find the sum total of all under all the columns so that the template looks like the below:

Now, this is the process for one month. We can now create tabs for each month in this manner so that this template is filled with prefilled information like tax rates and formulas that can calculate the employee’s salary, taxes, and deductions for each month and can be used and customized as and when required.

Things to remember about Excel Template for Payroll

Payroll is used to keep a record of the employees’ information, their wages, payment dates, taxes, deductions, and the number of hours worked.
Using payroll templates in business is a fast and easy method for them to standardize their payroll process.
There is no single standardized form for the use of a template.
Users can even create their own payroll templates in Microsoft Excel, which is beneficial as the payroll can be monitored by the user itself.
Payroll templates can help maintain a confidential detailed list of the employees of a business.
The free payroll templates are eligible or suitable for any number of customizations and can be edited or created just the way the user wants.
The payroll templates in MS Excel can be easily converted to PDF format and sent to the client via mail.
Some additional columns like the premium of health insurance, federal allowances, date of joining, pre-tax holdings, taxable benefits, statutory payments made, etc., can also be added to the payroll template.
Some other softwares used for payroll calculation and that are available for free are:
Employee Register
Dashboard Payroll
Employee Timesheet
Payroll Accounting
Employee Absence Schedule
Employee Payroll Calculator
Wage Tax Calculator
Blue Timesheet, etc.

Each of these has separate tabs and worksheets for each section of the employee details and payroll services.

Quiz Result
Total Questions	Correct Answers	Wrong Answers	Percentage

Introduction to Excel Template for Payroll

How to Create Payroll Template in Excel?

Things to remember about Excel Template for Payroll

Recommended Articles

About Us

Apps

Resources

Certification Courses