Payroll in Excel (Table of Contents)
Introduction to Payroll in Excel
- While most of the organizations turn their head towards software like Tally or ADP for payroll or they give a contract to some outsourcing companies to provide them the payroll services without any fuss. Several reasons are there for this approach. However, the core out of those is, it is really 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, there are still some businesses who prefer to do payroll work on their own (in-house) and manage it accordingly.
- Having already said it is time, cost and manpower consuming task, it provides you total control over your employee’s payroll as well as of each penny you are spending on them at the same time. In order 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. With the range of simple formulae and it’s simple layout makes excel standout for those who are doing payroll manually for their employee. In this article, we are going to see 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 on 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, therefore, create some column with names which can hold the values for the 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 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, a 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 better understanding.
In this example, if 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 total hours worked during the month. Same is the case with total hours overtimed. Also, the Pay/Hour and Overtime/Hour are in USD.
Step 5: Formulate Gross Pay. Gross Pay is nothing but 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). In the payroll sheet, it 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 formula the answer is shown below.
Drag the same formula cell F3 to cell F6.
Step 6: In order 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. The formula for Income Tax, therefore, becomes as – 0.15 * Gross Pay.
Which 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 cell G3 to G6.
Step 7: You have to mention other deductibles if any for a particular employee. 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 nothing but 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). Here, Income Tax and Other Deductibles are summed up and then subtracted from Gross Pay. See the screenshot below for better understanding.
Drag the same formula in cell I3 to cell I6.
This is how we create the payroll under excel to manage the things on our own.
Step 9: Add all the employee names working for you in this payroll one by one and set their total worked hours, overtimed hours, deductibles and charges accordingly. For Gross Pay, Income Tax and Net Pay, just drag the 4th cell of respective columns to have the details formulated. Also, add some formatting to the cells and add total at the end of the sheet. The final Payroll should look like this.
After using 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 a kind of security because you are monitoring your own payroll there.
- There might be some more columns added like health insurance premium, life cover premium, 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 –
