Home › Guides

Guide

Bulk ABN lookup in a spreadsheet

Your options for checking a whole column of ABNs at once, from free government tools to a spreadsheet formula.

Checking one ABN is easy. Checking 300 supplier ABNs before an end-of-financial-year review, or a customer list someone exported from an old system, is a different job. The aim is the same each time: confirm every number exists, see whether it is still active, and get the registered name and GST status into the sheet next to it.

How the ABN checksum works

Every ABN has 11 digits, and the first two digits are check digits. The Australian Taxation Office publishes the rule, so you can test a number yourself before you look it up:

1. Subtract 1 from the first digit.
2. Multiply each of the 11 digits by its weight: 10, 1, 3, 5, 7, 9, 11, 13, 15, 17, 19.
3. Add the 11 results together.
4. If the total divides evenly by 89, the number passes the checksum.

Take 51 824 753 556. After subtracting 1 from the first digit, the digits are 4 1 8 2 4 7 5 3 5 5 6. The weighted products are 40, 1, 24, 10, 28, 63, 55, 39, 75, 85 and 114, which add up to 534. 534 is 89 × 6, so the number passes. Change the last digit to 7 and the total becomes 553, which leaves a remainder of 19, so it fails.

A pass only tells you the number is well formed. It does not tell you the ABN was ever issued, who holds it, or whether it is still active. For that you need the Australian Business Register (ABR).

Step 1: clean the column first

1. Put all the ABNs in one column, with a header such as “ABN” in the first row.
2. Format the column as plain text (Format → Number → Plain text) so Google Sheets does not change the numbers.
3. Remove obvious duplicates with Data → Data clean-up → Remove duplicates. Each different ABN only needs checking once.
4. Run the checksum on every row so you can fix typos before you spend time on lookups. This free formula works in any sheet. Put it in B2 and fill down:

=LET(d, REGEXREPLACE(TO_TEXT(A2), "[^0-9]", ""),
  IF(LEN(d) <> 11, FALSE,
    MOD(SUMPRODUCT((VALUE(MID(d, SEQUENCE(11), 1)) - (SEQUENCE(11) = 1))
      * {10;1;3;5;7;9;11;13;15;17;19}), 89) = 0))

Fix or follow up anything that returns FALSE. There is no point looking up a number that cannot be an ABN.

Step 2: free ways to look up many ABNs

The Australian Business Register offers several free tools. Each one suits a different kind of user.

• ABN Lookup website. Search abr.business.gov.au one number at a time. It is free and accurate, but slow for a long list, because each result has to be copied back by hand.
• ABN Lookup web services. The ABR offers free web services for developers. You register for an authentication GUID and call the service from your own code. This works well if you can program, and it is what ABN Formula uses behind the scenes.
• ABN Bulk Extract. The ABR publishes public ABN data as a large downloadable extract on data.gov.au. It suits analysts who want to match a big list offline, but it is a heavy download, needs some data skills to join, and is only as current as the latest extract.

Step 3: bulk lookup with ABN Formula

ABN Formula for Google Sheets™ puts the register lookup inside the sheet. There are two ways to use it.

Check selected cells (best for a long list)

1. Open ABN Formula, then Open sidebar, once in the spreadsheet to authorise lookups.
2. Select the ABN column, including the header row if it has one.
3. Choose ABN Formula → Check selected cells.

The add-on writes seven columns to the right of your selection: Status, Legal name, GST, GST since, State, Postcode and Checked at. Because these are written as values, they will not recalculate every time you edit the sheet, and you keep a record of when each number was checked.

The ABN_CHECK formula (best for a live list)

=ABN_CHECK(A2)        the full row for one ABN
=ABN_CHECK(A2:A300)   one row of results for each ABN in the range

ABN_CHECK returns the same seven fields as the menu command and spills them across the cells to the right. Use it when the list changes often and you want new rows checked as they are added.

All of these details come from the Australian Business Register through ABN Lookup. Results are saved for up to 24 hours, so a status can be up to a day old.

How the monthly allowance works

The free plan covers 50 different ABNs a month. Pro covers 10,000 different ABNs a month for A$9 a month or A$79 a year. Checking the same ABN again in the same month does not count twice, and numbers that fail the checksum are not looked up, so they are not counted either.

Related guides

• How to validate ABNs in Google Sheets
• Check GST registration for a list of suppliers
• Check ABNs are active before paying invoices

ABN Formula is not affiliated with, endorsed by, or part of the Australian Government, the Australian Taxation Office or the Australian Business Register. ABN status, entity names and GST registration come from the ABR through the free ABN Lookup web services. This guide is general information, not tax advice.