General information only — not tax advice. Confirm current rules on the ATO website or with a registered tax or BAS agent. Rules change and your situation may differ.
If you are GST-registered in Australia, each quarter you need a clear view of GST collected, GST credits, and the net amount the activity statement will ask for. A single spreadsheet tab can do that without accounting software. It takes about fifteen minutes to build in Google Sheets or Excel.
The idea is simple: record every taxable sale and every business purchase as it happens, let formulas split out the GST, then filter to the quarter you are about to lodge.
The sheet layout
One register (or two identical tabs, Sales and Purchases). Labels in row 1, one transaction per row from row 2:
| Col | Field | Example |
|---|---|---|
| A | Date | 2026-08-14 |
| B | Invoice or receipt id | INV-1042 |
| C | Customer or supplier | Acme Co |
| D | Type | SALE (or PURCHASE) |
| E | Gross amount, incl. GST (AUD) | 1100 |
| F | GST treatment | TAXABLE (or GST-FREE) |
| G | Quarter tag | (formula) |
| H | Net (ex GST) | (formula) |
| I | GST amount | (formula) |
| J | Tax invoice on file? | YES or NO |
Gross means the amount on the invoice or receipt, including GST when the sale or purchase is taxable. Use GST-FREE for sales the ATO treats as GST-free, and do not invent a GST amount on those rows.
Core formulas (GST is 1/11 of a GST-inclusive price)
Once you are registered, you generally include GST in the price of taxable sales and claim credits for the GST in the price of things you buy for the business. The ATO explains those basics in How GST works. When a price already includes the 10% GST, the GST part is 1/11 of the gross. On a A$110 sale, GST is A$10 and the net is A$100.
In row 2:
H2 Net (ex GST) =IF(F2="GST-FREE",E2,E2*10/11)
I2 GST amount =IF(F2="GST-FREE",0,E2/11)
Fill those down the sheet. If you sometimes enter a net (ex-GST) amount instead, do not use /11 on that cell — GST is then E2*0.1 and gross is E2*1.1. Pick one convention per row and stick to it. Receipts and tax invoices are usually GST-inclusive, so /11 is the default here.
Tag the Australian tax quarter automatically
BAS quarters follow the Australian financial year (July to June), not the calendar year:
G2 Quarter tag =IF(A2="","",IF(AND(MONTH(A2)>=7,MONTH(A2)<=9),"Q1",IF(MONTH(A2)>=10,"Q2",IF(MONTH(A2)<=3,"Q3","Q4"))))
- Q1 July–September
- Q2 October–December
- Q3 January–March
- Q4 April–June
Filter by the quarter tag so you can close Q1 without mixing Q2 rows.
The quarter summary
Park a small summary block to the right, with the quarter you are lodging in L1:
L1 Quarter to close Q1
L2 GST on sales =SUMIFS(I:I,D:D,"SALE",G:G,L1)
L3 GST on purchases =SUMIFS(I:I,D:D,"PURCHASE",G:G,L1)
L4 Net GST =L2-L3
L5 Taxable sales (gross) =SUMIFS(E:E,D:D,"SALE",G:G,L1,F:F,"TAXABLE")
GST on sales is the amount you have collected and need to report. GST on purchases is your credits (only where the purchase is for the business, and only the business-use share if something is mixed). Net GST is what you expect to pay — or, if negative, the credit you expect. These are the GST figures the BAS is asking for; check them against your records before you lodge.
If L3 has credits, sanity-check column J: a missing tax invoice is a common reason a credit gets disallowed. Keep the invoice with the row.
A worked example
In the July–September quarter, a freelance designer registered for GST:
- Invoices totalling A$16,500 including GST → GST on sales =
16500/11= A$1,500 - Business software and equipment of A$2,200 including GST → GST credits =
2200/11= A$200 - Net GST =
1500-200= A$1,300
For a quarterly lodger, that July–September statement is due 28 October (or later if you are eligible for an extension). The ATO publishes the current dates on Due dates for lodging and paying your BAS. If the due date falls on a weekend or public holiday, you have until the next business day. If you lodge online you may get extra time, except that the October–December due date already includes a one-month extension.
A GST pot next to the numbers
Add one more pair of cells so the GST does not get spent:
L7 Already in the GST account 800
L8 Still to move this quarter =MAX(0,L4-L7)
A separate bank account nicknamed GST or BAS, kept roughly equal to L4, means the net amount is already sitting there when the due date arrives. It was never operating cash.
Habits that keep the sheet honest
- Enter every taxable sale the day you invoice (or the day you are paid, if you account for GST on a cash basis — use the basis you have told the ATO).
- Enter purchases when the receipt lands, not in BAS week, and tick column J when the tax invoice is on file.
- Transfer L8 into the GST account weekly, or whenever L4 jumps.
- Do not claim GST on private spending. If a cost is part-business, put only the business percentage in column E, or add a business-use % column and multiply the GST by that percentage.
- When the quarter is lodged, freeze or archive those rows so later edits do not silently change a lodged figure.
The one-line version
GST on a GST-inclusive price = gross / 11
Net GST for the quarter = SUMIFS(GST, type, "SALE", quarter, Q)
− SUMIFS(GST, type, "PURCHASE", quarter, Q)
Recalculate when you add rows. Before you lodge, the useful checks are: does L2 match the GST on this quarter's taxable sales? Does every credit in L3 have a tax invoice? Is the GST account at least L4?
Want the longer walkthrough?
I wrote the full Australian guide here: GST and BAS for Sole Traders in Australia: A Plain-English Guide to Your Quarter.
(Disclosure: I write these guides and sell related spreadsheet templates.)
Not tax advice. Figures are examples only. Confirm current rules with the ATO or a registered tax or BAS agent.
Top comments (0)