If your month-end routine involves exporting a CSV from Xero Payroll NZ, pasting it into a spreadsheet and rebuilding the same pivot tables all over again, this article is for you. Microsoft Excel is still the most popular reporting tool on the planet - so rather than replacing it, let's point it at a live copy of your Xero Payroll NZ data instead. Your formulas, pivot tables and charts stay exactly as they are, and updating them becomes a single click.
In this article, I’ll take you through the steps to connect Excel to your Xero Payroll NZ data, and get started customizing the reports you need.
Connect to Xero Payroll NZ
If you don’t already have a SyncHub account, you can grab a free trial here - go on, I’ll wait - it only takes a minute and you can cancel as soon as you’ve finished this tutorial if you like.
Once connected, you'll find data from all these endpoints streaming down from Xero Payroll NZ, such as this example from the Employee table:
| FirstName | LastName | StartDate | JobTitle | |
|---|---|---|---|---|
| Oliver | Williams | oliver.w@company.nz | 2024-05-01 00:00:00 | Software Developer |
| Sophia | Anderson | sophia.a@business.nz | 2024-04-15 00:00:00 | Project Manager |
| Liam | Brown | liam.b@enterprise.nz | 2024-06-10 00:00:00 | HR Specialist |
After connecting, visit your SyncHub Dashboard and grab your new database credentials - you'll need them below.
Reading your Xero Payroll NZ data from Microsoft Excel
We now just need to tell Excel where to find your data. Select a new SQL Server connection from the Data ribbon:
When prompted, enter the Server and Database that SyncHub provided in the first step above:
Next - and this is the part that trips most people up - switch to the Database credentials tab (highlighted by the arrow below), then enter the username and password provided to you by SyncHub in the step above:
Don’t worry about which level to apply the settings to - database or server - it does not matter to SyncHub.
Troubleshooting
This is the point where you are most likely to run into issues - your login won’t be accepted. After triple-checking that you have used the correct server/database/username/password, the most common resolutions we see are:
- try opening Excel in Administrator mode
- if you are in an office, perhaps you have some firewall restrictions?
All going well, Excel will now present you with the list of tables from Xero Payroll NZ, so simply select the data that you’d like to report on. Excel keeps things really simple by adding each “table” from SyncHub as a new tab.
Keeping your data up-to-date
SyncHub updates it’s staged data from Xero Payroll NZ in near-realtime, but unfortunately Excel does not have a similar feature. But not to worry - the solution isn’t too onerous. Every time you want to refresh your data, simply click the (appropriately named) Refresh button in your Ribbon:
Beyond Xero Payroll NZ
Xero Payroll NZ-specific reports are essential, but the true power of SyncHub comes when you augment your Xero Payroll NZ data with additional information:
- SyncHub allows you to pull in data from multiple Xero Payroll NZ accounts, and compare/report/aggregate from within the same report. See this blog article for details on aggregating your Xero Payroll NZ data.
- Most businesses use multiple cloud platforms. SyncHub provides connectors to a wide range of popular cloud platforms. Imagine the insights you could gather by consolidating this information into a single dashboard (or see this case study for real-world examples).
So what are you waiting for? Grab a free trial of SyncHub here and see what you can do. In ten minutes from now, you could be reporting against your Xero Payroll NZ data and taking your first steps towards a data-driven business.