Excel for payroll staff (useful tools)

Excel for payroll staff (useful tools)

Excel is used in all industries but one of the most common areas is in the HR and Payroll departments.

Tools and Formula that all Payroll staff should master include:


The VLOOKUP formula allows you to lookup an item in a list. A key feature is its ability to create an approximate match. In the payroll environment it is used to:

  • categories employees
  • calculate commissions based on scales
  • combine databases into a single database
  • compare databases to identify differences
  • calculate taxes due
  • and much more

To see how VLOOKUP works watch the free video clips on

Data Filter

The Data Filter is typically used to find data in a list but it is as useful when it comes to cleaning up databases. Instead of spending hours deleting individual rows, you can do this in one go!

You achieve this by asking the filter to show you the rows you DO NOT want and then deleting the rows. You can see how this works on https://www.auditexcel.co.za/video-tutorial/clean-excel-data/

Flash Fill

Excel 2013’s best new feature.

Almost any splitting or combining of cells can be done with no formulas. So if you need to split first name and surname, or combine first name, middle initial and surname you can do it as easily as typing it out.

In fact this new tool can be used to clean a multiple of typical spreadsheet problems.

You can see some examples on https://www.auditexcel.co.za/blog/new-tool-clean-data-excel-2013/

Flash Fill 1- Case Split


The IF function is the function you fall back on when you can’t think of another way to do it.

Useful for calculating overtime it can differentiate between normal hours and overtime hours. If also allows you to identify above or below average performances by employees.

The IF function is available in our free Excel Fundamentals Course


One of the many date formula, this one will automatically calculate the number of WORKING days between two dates taking into account weekends and public holidays. Exceptionally useful in calculating leave days taken. See how it works on https://www.auditexcel.co.za/video-tutorial/net-working-days-excel-formula/

To find out more about useful functions and tools for HR and Payroll staff, email adrian@AuditExcel.co.za