Spreadsheet Features for Payroll and HR
Learning how to utilise spreadsheet features efficiently is a key skill required in the payroll profession. As payroll software like Buddy continues to improve their tech, data analytic skills are sometimes disregarded.
However, with creativity and know-how, utilising spreadsheets can unlock great potential, especially in combining and aggregating data from multiple sheets. These features are generally similar across multiple spreadsheet software.
General Suggestions on When to Use Spreadsheets
Downloading and analysing data over spreadsheets can be useful to:
- obtain totals of payroll data across multiple periods
- reconcile payroll data with end-of-year lodgement files (csv exports)
- use data from employee reports in payroll or leave balance reports
You're encouraged to think creatively and combine reports to make your reporting efficient and more robust.
VIDEO: Utilising Impactful Features
Watch this guide on the most frequently used features here:
Freeze View
Rule #1 for data readability is you should freeze important headers (rows) and columns to keep them in your view at all times.
If you find yourself struggling forgetting which row number you were looking at, this is something that will help you out.
Filters
Apply multiple filters to view data for specific sub-data. For example, if you are downloading payroll reports across multiple periods, then you can filter for an employee's ID Card or Names to view only that person's totals.
You could also combine filters, such as filtering for a specific role in a specific department.
Pivot Tables
Master pivot tables to create totals (or averages) across multiple rows. Rather than filtering for each employee, we can use a pivot table to get the totals of each employee in one sheet.
VLOOKUP to Combine Data from Multiple Sheets
You may want to use features like VLOOKUP to combined data from multiple sheets. One example is you want to add a column in your payroll report to show employee data such as the hourly rate or the tax rate.
Creative Solutions!
Tasks can often require the use of multiple features. For instance, to reconcile payroll reports with FS3, you could:
- Create Pivot Tables to get Payroll Data totals
- Use VLOOKUP from the Pivot Table to compare with FS3 data from Lodgement Files
- Use formulae to obtain differences between multiple columns
- Apply filters on columns to observe unusual patterns