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, sure, but you’re in total control and are aware of everything being paid out to your employees. There’s a certain sense of security in that.

The simplest way to do this is by using 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 for each and every pay period. From there, you simply enter the hours worked and Excel performs the calculations for you: hey, presto - your payroll is complete. Provided your deductions and calculations aren’t complex, preparing payroll in Excel is actually not all that difficult, so, even if you're not a spreadsheet whizz, let's explore how you can do it.

 

 

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 names as you need):

  • 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's salaries. 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.
excel net pay
  • 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. 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.

 

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. Of course, once you start to scale and your business grows, you might want to consider hiring an accountant, but as a short term fix when you're getting started, Excel is perfect.

Do you have any other tips for calculating payroll in Excel? Let us know in the comments below...

Try Reading