Supplier ABN Checker spreadsheet

A free spreadsheet for checking the ABNs on your supplier list before you pay. It opens in Google Sheets and Excel, and you don't need an account to download it.

Download the template (.xlsx)

You need the ABN Formula add-on for live results

The Business name, ABN status and GST registered columns use ABN Formula formulas. They work fully only with the ABN Formula add-on for Google Sheets installed. Without it, the sample rows show demo values and new rows show "Install ABN Formula". The Valid format? checksum column works anywhere, with or without the add-on.

What's in it

Suppliers sheet with columns for Supplier, ABN, Valid format?, Business name, ABN status, GST registered, Invoice amount and Notes. The header row is frozen, and rows are ready for 50 suppliers.
Valid format? is a free formula that checks the ABN checksum in both Google Sheets and Excel. It turns green for TRUE and red for FALSE. Spaces and hyphens in the ABN are fine.
Business name, ABN status and GST registered use =ABN_NAME, =ABN_STATUS and =ABN_GST. They show demo values until the add-on is installed, then pull live from the ABR. Active shows green, Cancelled red, and GST Yes or No are colour-coded.
Instructions sheet explaining each column, the free manual method using ABN Lookup, and how the formulas work.
The example rows use the same sample ABNs as the demo on our homepage. Replace them with your own suppliers. One of them, 51 824 753 557, deliberately fails the checksum so you can see a red result.

How the checksum column works

Every ABN is 11 digits with a check built in. The formula takes away 1 from the first digit, multiplies each digit by 10, 1, 3, 5, 7, 9, 11, 13, 15, 17 and 19, and adds the results. If the total divides evenly by 89, the number is correctly formed. A FALSE result almost always means a typo. A TRUE result only means the number is well formed. It doesn't tell you the ABN is active, who it belongs to, or whether the business is registered for GST. Only the Australian Business Register (ABR) can tell you that. Read the full checksum guide.

Open it in Google Sheets

Download the .xlsx file.
In Google Sheets, choose File › Import › Upload, pick the file and select Replace spreadsheet or Insert new sheet(s). Or upload it to Google Drive, then right-click it and choose Open with › Google Sheets.
If you open it from Drive, use File › Save as Google Sheets so that add-ons and custom functions work.

In Excel, just open the file. If Excel shows a Protected View bar, select Enable Editing so that the formulas recalculate.

Fill in names, status and GST

The lookup cells are already filled with ABN Formula formulas wrapped in IFERROR, for example =IFERROR(ABN_NAME(B3),"HARBOUR & CO PTY LTD"). They show demo values until the add-on is installed, then pull live from the ABR. Empty rows show "Install ABN Formula" as soon as you type an ABN, until the add-on is installed.

=ABN_NAME(B3) for the entity name on the ABR
=ABN_STATUS(B3) for Active or Cancelled
=ABN_GST(B3) for "Yes (from YYYY-MM-DD)" or "No"
=ABN_CHECK(B3) for status, legal name, GST, GST since, state, postcode and the time it was checked, across seven columns

The free alternative is to look each ABN up on ABN Lookup and type the details over the formulas. In Excel the lookup columns always show the fallback text, because the add-on is only for Google Sheets.

Check the whole column in one go

ABN Formula for Google Sheets™ is free for 50 different ABNs a month. Pro covers 10,000 different ABNs a month for A$9 a month or A$79 a year.

See pricing See all five formulas

This template is general information, not tax or legal advice. ABN status, entity names and GST registration come from the ABR. ABN Formula is not affiliated with, endorsed by, or part of the Australian Government, the Australian Taxation Office or the Australian Business Register. Microsoft Excel is a trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC.