Career Testing
Career Testing
Career Testing
SALARIES / JAN. 11, 2017
version 7, draft 7

How to Prepare Payroll in Excel (with Pictures)

excel payroll
Shutterstock

While most people turn to either do-it-yourself software such as Intuit Payroll, or outsource to a payroll service provider, there are still those business owners out there who prefer the hands-on and personal approach to payroll. Doing it manually is time and labour intensive, but you’re in total control and are aware of everything being paid out to your employees. There’s a certain security in that.

One way to do it is by use of Microsoft Excel (or something similar like the free and open-source OpenOffice Spreadsheet). With it, you create a basic template to be used over and over again - each and every pay period. Once created, you simply enter the hours worked, and it performs the calculations for you. Viola! Your payroll is complete (of course, you then need to manually issue cheques and transfer funds to your employee bank accounts, but that’s a discussion for another day). Preparing payroll in Excel is actually not all that difficult, providing your deductions and calculations aren’t complex.

Step 1: Open a New Excel Spreadsheet

 

open new excel spreadsheet

 

If you don’t have Excel already on your computer, you can quickly and easily download either OpenOffice or LibreOffice. Both of these are free and provide a productivity suite nearly identical to Microsoft Office (word processing, presentations, and spreadsheets). Once installed, open a new spreadsheet. This will become your payroll template.

Step 2: Create the Column Names

 

excel named columns

 

You’ll see that a spreadsheet has letters across the top (to identify each column) and numbers down the left-hand side (to indicate the row). Each cell is named according to its letter and number (A2, B5, and so on). A basic payroll template needs the following columns (you can adjust the name as you want):

  • Employee Name (A1)
  • Pay Rate (B1)
  • Total Hours (C1)
  • Overtime Pay Rate (D1)
  • Overtime Total Hours (E1)
  • Gross Pay (F1)
  • Income Tax (G1)
  • Other Deductions (if any)
  • Net Pay (H1)

You can bold these column names, centre them in the cell (as you would in a word document), make the font size bigger, or anything else to make it look how you want it.

Step 3: Create Some Simple Formulas

Excel (or its equivalent) is useful because of its calculation features. You can create a mathematical formula and apply it to a cell, or an entire column. When you make changes, it automatically updates all the corresponding cells and data.

For payroll, you’ll need to apply some basic formulas to calculate your employee salary. For each column we created, apply the following:

  • Employee Name - No formula. Simply enter an employee name on each row starting in A2 and moving down (A3, A4, A5…)
  • Pay Rate - No formula. Enter the numerical hourly rate for each employee, without any dollar sign. $25.00/hr would be entered as 25. $17.50/hr would be entered as 17.5.
  • Total Hours - No formula. Enter the total number of hours worked during the pay period.
  • Overtime Pay Rate - No formula. Enter the OT hourly rate of pay, without the dollar sign.
  • Overtime Total Hours - No formula. Enter the number of OT hours worked during the pay period, if any.
  • Gross Pay - FORMULA. You want to create a simple formula that will automatically calculate each employee’s gross pay (rate x hours + OT rate x OT hours). Click on the Gross Pay cell for your first employee (the one entered in cell A2) and enter this basic formula in the input field just above the spreadsheet table: =(B2*C2)+(D2*E2) and hit enter. Make sure you include the “=”. This formula tells the spreadsheet to take the number in cell B2, multiply it by the number in cell C2, then multiply the number in cell D2 by the one in cell E2, and finally to add those two numbers together.
    It will display that number - your employee’s gross pay before deductions - in the Gross Pay column. Congratulations! You’ve completed your first spreadsheet calculation. In order to apply that formula to every employee, you need to click on the cell itself (F1). The cell will be outlined in bold, and you should see a little black box in the bottom right corner. Place your cursor over it, click, and drag the box down the length of the entire column. You have now applied the gross pay formula to every employee.
excel gross pay
  • Income Tax - FORMULA. Most of your employees will be paying income tax, right? You can quickly calculate their deduction using a simple formula. You will first need to determine their deduction rate. For our example, we will use a base rate of 10%. Click on cell G1, and enter this formula in the calculation field above the spreadsheet table: =10%*F2, and hit enter. This formula instructs the spreadsheet to multiply the gross pay figure appearing in cell F2 by 10%, our base tax rate. It will automatically do so and display the deduction amount in the cell.
    As with the gross pay formula, you’ll want to apply this formula to the entire column by dragging it down. Of course, be aware that employees may have different tax rates. Group those together that share a rate, apply the formula, then group the next bunch and apply the new formula (say 12%, for example, in which case the formula would become =12%*F2).
excel income tax
  • Other Deductions - FORMULA, if there are any. Depending on where you are, other deductions may include Social Security, Medicare, NIC, CPP, EI, and so on. The formula would reflect either the percent or flat rate.
  • Net Pay - FORMULA. The final column you created is your net pay after all deductions. For the purposes of this example, we’re going to assume income tax as the only deduction, in which case net pay would appear in column H. Click on cell H2 and enter this formula in the formula field: =F2-G2, and hit enter.
  • The spreadsheet will automatically subtract the income tax deduction (G2) from the gross pay (F2) and display the result. Don’t forget to click and drag to apply it to the entire column. Presto! Your employee net pay. If there are other deductions, you can simply add them with a formula along these lines: =F2-(G2+H2+I2) depending on how many other deductions were applied.
excel net pay

Step 4: Customise and Save Your Template

That’s all you need for a basic payroll template. You can bold titles, make column names bigger or centred in the cell, add a pay period at the top or at the bottom. Experiment and play around until it looks exactly as you want it. When it’s finally ready - and BEFORE you use it - save a copy and name it PAYROLL TEMPLATE or something similar.

Make sure you have entered all the data that never (or rarely) changes like employee names, pay rate, OT pay rate, and deduction rates. Then it’s always ready to go. Each pay period, open your template, enter the hours worked (and change anything that needs to be changed, like pay rate if someone got a raise, or tax rate if that number went up or down), and save it for your records, naming it after the pay period for simplicity (PRApr2014, for example).

It’s not fancy, but a spreadsheet template is a convenient and cost-effective way to calculate a relatively basic payroll for your employees. Once it’s setup, you only need to enter the hours, saving you time, money, and hassle. Alternatively, you could look online for payroll templates using keywords like “payroll spreadsheet template [your state, province, or country]”. You can even download a great payroll calculator excel template from the official Microsoft Office website. That said, setting it all up yourself is a great way to get at least a general understanding of how it all works, so it’s probably worth your effort.



Touch up on your Excel skills with this best selling ebook:

 

Let us know how you calculate payroll in the comments below...

See Also: 15 Skills & Traits a Payroll Administrator Needs

Get our FREE eBook!
'6 Steps to Landing Your Next Job'
Career Test
Career Test
Career Test

LEAVE A COMMENT

0 comments

 

RELATED ARTICLES

How to Run Your Business's Payroll in the UK
SALARIES / JUL 01, 2014

Setting up your company in the United Kingdom is a savvy business decision. It has a strong and dynamic economy, with close proximity to the rest of Europe. Once you’ve...

accounting payroll money tax
SALARIES / JUN 30, 2014

You’ve got yourself a business, and things are going well. So well, in fact, that it’s time to hire your first few employees. Congratulations! Growing a business from an...

How to Pay Employees When You Can't Make Payroll
SALARIES / JUN 27, 2014

There are many presumptions today regarding business accounting practices. One of them being that all businesses use payroll to pay their employees. However, this isn't...

How to Get Rich Quick
SALARIES / AUG 01, 2013

If your career isn’t going anywhere, and you can’t see yourself making much money with the job you have, then it is time to put a new plan into action. Because whether we...

Money head
SALARIES / JUL 21, 2015

With a staggering 80% of businesses destined to fail with their first 18 months in operations, according to Bloomberg, and with a root cause most typically due to...

How to Negotiate Severance Pay
SALARIES / MAY 20, 2015

Negotiating a severance package brings images of layoffs, difficult conversations, stress, and personal upheaval – and it can be very tempting not to think about this...

Get our FREE eBook!
'6 Steps to Landing Your Next Job'
G up arrow