EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Formula and Functions Payroll in Excel
Secondary Sidebar
Excel Functions
  • Excel Formula and Functions
    • Compatibility Mode in Excel
    • VLOOKUP Names
    • Advanced Formulas in Excel
    • Excel Function for Range
    • Excel Template for Payroll
    • Excel Sum Time
    • Payroll in Excel
    • Complex Formulas in Excel
    • Pivot Chart in Excel 2016
    • Advanced Excel Formulas
    • Best Basic Excel Formulas
    • Use Excel As Your Calculator
    • Print Command Button in Excel
    • Excel SUM MAX MIN AVERAGE
    • Excel Basic Functions For Beginners
    • Interactive Excel Dashboard
    • Features of 2016 Excel Workbook
    • What's New In Excel 2016
    • Microsoft Excel Skills
    • Microsoft Excel Shortcuts
    • Microsoft Excel Tips and Tricks
    • Excel Skills
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (36+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (220+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Excel Training
  • Excel Advanced Training
  • Excel Data Analysis Training

Payroll in Excel

By Madhuri ThakurMadhuri Thakur

Payroll in Excel

Payroll in Excel (Table of Contents)

  • Introduction to Payroll in Excel
  • Example of Creating a Payroll in Excel

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.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

You can download this Payroll Excel Template here – Payroll Excel Template

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.

payroll in excel 1-1

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.

payroll in excel 1-2

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.

payroll in excel 1-3

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.

payroll in excel 1-4

After using the formula, the answer is shown below.

payroll in excel 1-5

Drag the same formula from cell F3 to cell F6.

payroll in excel 1-6

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.

payroll in excel 1-7

In terms of excel payroll sheet can be formulated under cell G2 as =0.15*F2 (Column F contains Gross Pay amount).

payroll in excel 1-8

Drag the same formula in cells G3 to G6.

payroll in excel 1-9

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 -7

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.

step -8

Drag the same formula in cell I3 to cell I6.

step 8-1

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.

step 9

After using the SUM Formula, the answer is shown below.

final payroll

Find the Sum Total below.

Sum Total

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 –

  1. MAX Formula in Excel
  2. ROUND Formula in Excel
  3. NPV Formula in Excel
  4. PPMT Function in Excel
Popular Course in this category
Excel Training (23 Courses, 9+ Projects)
  23 Online Courses |  9 Hands-on Projects |  110+ Hours |  Verifiable Certificate of Completion
4.9
Price

View Course

Related Courses

Excel Advanced Training (16 Courses, 23+ Projects)4.9
Excel Data Analysis Training (17 Courses, 8+ Projects)4.8
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

Download Payroll Excel Template

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Payroll Excel Template

EDUCBA

डाउनलोड Payroll Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more