Payroll in Excel (Table of Contents)
Introduction to Payroll in Excel
- While most organizations turn their heads towards software like Tally or ADP for payroll, or they give a contract to some outsourcing companies to provide them with payroll services without any fuss. There are several reasons for this approach. However, the core of those is that it is very time-consuming to do all the manual work for payroll and have it set. Apart from that, it consumes manpower engaged as well. With all the ease it makes for them to be considered, some businesses prefer to do payroll work on their own (in-house) and manage it accordingly.
- It is a time, cost, and manpower-consuming task; it gives you total control over your employee’s payroll and each penny you spend on them simultaneously. To do payroll manually, you need something (definitely a tool) that is powerful, versatile, and easy to handle at the same time. With all these requirements, you can easily guess the tool. Yes, you might have guessed it right! Microsoft Excel. The range of simple formulae and its simple layout make excel stand out for those who do payroll manually for their employee. This article will show how we can create a payroll manually from scratch.
Example of Creating a Payroll in Excel
Payroll in Excel is very simple and easy. Let’s understand how to create the Payroll in Excel with a few steps.
Step 1: Open a new blank excel spreadsheet. Go to Search Box. Type “Excel” and double-click on the match found under the search box to open a blank excel file.
Step 2: Save the file in the location you want your payroll to be saved so that it does not get lost, and you will always have it with you.
Step 3: In this newly created file where all your employee payroll information would be stored, create some columns with names that can hold the values for certain parameters/variables. Enter the column names in the following hierarchy.
- Employee Name (column A): Contains your employee name.
- Pay/Hour (column B): Contains per hour pay rate to the employee without any currency symbol.
- Total Hours Worked (column C): Contains total hours worked by an employee in a day.
- Overtime/Hour (column D): Overtime rate per hour without any currency symbol.
- Total Overtime Hours (Column E): Number of hours employees work overtime in a day.
- Gross Pay (column F): Payable amount to the employee without any deductibles.
- Income Tax (column G): Tax payable on Gross Pay.
- Other Deductibles (If Any) (column H): Deductibles other than Income Tax.
- Net Pay (column I): Payment, the employee, will receive in hand after all the deductions.
Step 4: Add the details column-wise, like Employee Name in column A, the number of hours worked and hourly paying rate, etc. I will say input the fields with no formula (From column A to column E). See the screenshot below for a better understanding.
In this example, as you can see, the Total Hours Worked and Total Overtime Hours are considered on a monthly basis (because we pay the employee on a monthly basis, right?). Therefore 160 means the total hours worked during the month. The same is the case with total hours of overtime. Also, the Pay/Hour and Overtime/Hour are in USD.
Step 5: Formulate Gross Pay. Gross Pay is the sum of the product of Pay/Hour, Total Worked Hours, and Overtime/Hour, Total Overtime Hours. (Pay/Hour * Total Hours Worked) + (Overtime/Hour * Total Overtime Hours). The payroll sheet can be formulated under cell F4 as =(B2*C2)+(D2*E2). It’s a simple formula, anyway. However, you can see the screenshot below for a better understanding.
After using the formula, the answer is shown below.
Drag the same formula from cell F3 to cell F6.
Step 6: To calculate the Income Tax, you need to check how much percentage of tax your employee pays on the total gross pay. Income Tax is always calculated on Gross Pay. In this case, we will consider 15% of Income-tax on all the Gross Pay. Therefore, the income tax formula becomes – 0.15 * Gross Pay.
In terms of excel payroll sheet can be formulated under cell G2 as =0.15*F2 (Column F contains Gross Pay amount).
Drag the same formula in cells G3 to G6.
Step 7: You must mention other deductibles for a particular employee if any. These deductibles may contain the premium of health/life insurance, professional taxes, EMI amount if any loan is taken from an organization, etc. add these amount values under column H. If there is no other deductible for a particular employee, you can set the value under column H for that employee to zero.
Step 8: Now, finally, we come towards Net Pay. Net Pay is the amount that gets credited into your employee’s bank account after all the deductions from Gross Pay. Therefore, in this case, we will deduct (subtract) Income Tax (column G) and Other Deductibles (Column H), which can be formulated under cell I2 as =F2-(G2+H2). Income Tax and Other Deductibles are summed up and then subtracted from Gross Pay. See the screenshot below for a better understanding.
Drag the same formula in cell I3 to cell I6.
This is how we create the payroll under excel to manage things independently.
Step 9: Add all the employee names working for you in this payroll one by one and set their total worked hours, overtime hours, deductibles, and charges accordingly. For Gross Pay, Income Tax, and Net Pay, drag the 4th cell of respective columns to have the details formulated. Also, add some formatting to the cells and add the total at the end of the sheet. The final Payroll should look like this.
After using the SUM Formula, the answer is shown below.
Find the Sum Total below.
Things to Remember About Payroll in Excel
- There are more advanced tools available to have your payroll done. However, creating your own in excel gives you security because you are monitoring your own payroll there.
- Some more columns might be added, like health insurance premiums, life cover premiums, etc. However, this is a simple layout.
Recommended Articles
This is a guide to Payroll in Excel. Here we discuss How to Create a Payroll in Excel, along with a practical example and downloadable excel template. You can also go through our other suggested articles –
23 Online Courses | 9 Hands-on Projects | 110+ Hours | Verifiable Certificate of Completion
4.9
View Course
Related Courses
