The 15-Minute Win: Create a Simple Expense Tracking Template in Google Sheets with AI

At tax time, most small business owners are digging through emails, bank statements, and receipts stuffed in a folder. The shoebox method works right up until you need to lodge your BAS. A proper tracker stops the scramble before BAS time turns into a week-long nightmare.

In 15 minutes, you’ll build a Google Sheets expense tracker designed around your actual business. You’ll use ChatGPT or Gemini (both free) to generate the structure: columns that match how you spend, ATO-aligned categories, a GST column, and a summary tab that adds everything up automatically. Everything runs in the browser, free.

Step 1: Open ChatGPT or Gemini

Go to chat.openai.com or gemini.google.com. Free accounts work fine for this.

Step 2: Paste this prompt (customise the bracketed parts)

I run a [type of business, e.g. “freelance graphic design business” or “small café” or “mobile dog grooming service”]. I’m building a Google Sheets expense tracker for Australian tax purposes. Create a table structure with these columns: Date, Supplier, Description, Category (using ATO business expense categories), Amount (ex-GST), GST Amount, Total (inc-GST), Payment Method, Notes. Also suggest 10–12 expense categories relevant to my business type that align with what the ATO accepts as deductions. Format the categories as a dropdown list I can use in Google Sheets.

ChatGPT will return the column headers, a suggested category list, things like “Office Supplies,” “Motor Vehicle Expenses,” “Advertising,” “Professional Development”, and often some Google Sheets formula suggestions for the GST column.

Step 3: Open Google Sheets and build the structure

1. Go to sheets.google.com and open a blank sheet.
2. Paste the column headers ChatGPT gave you across Row 1.
3. In the GST Amount column, add this formula for Row 2: =E2/11, this calculates the GST component from the GST-inclusive total, useful if you’re recording the full invoice amount.
4. For the Total column: =E2+F2
5. Select the Category column, then go to Data → Data validation → List of items, and paste in your AI-generated categories.

Step 4: Add a summary tab

Ask ChatGPT:

Now create a summary tab structure for this same spreadsheet. It should show total spending by category for the current month and year-to-date, using SUMIF formulas from the main data tab (which I’ll call “Expenses”).

Copy the formula structure it gives you. It’ll look something like =SUMIF(Expenses!C:C,"Office Supplies",Expenses!E:E), which pulls all rows from the Expenses tab where the category matches and totals the amounts.

Step 5: Prompt for your specific situation

If you have unusual business expenses, home office, vehicle use, a lot of software subscriptions, add one more prompt:

I also want to track [home office expenses / vehicle kilometres / software subscriptions]. What additional columns or a separate tab would you recommend for ATO compliance?

The ATO has a full list of deductible business expenses at ato.gov.au. Cross-check your category list against that page before your first BAS lodgement.

What you end up with

A working Google Sheets expense tracker with:

• Columns set up for GST-inclusive Australian invoices
• A dropdown category list tied to ATO deduction types
• SUMIF formulas that total spending by category automatically
• A summary tab ready for BAS prep

It won’t replace your accountant for complex situations. But you’ll stop spending the first week of every BAS period hunting for receipts, and you’ll start seeing where your money actually goes.

This post is about building the tracker from scratch. If you want to use AI to analyse spending you’ve already recorded, that’s a different workflow covered elsewhere in this series.

This post is part of The 15-Minute Win series, quick AI tasks every Australian small business owner can action today.

More practical guides: How-To Hub →

📊 Compare AI tools side by side | 💼 Free resources & AI prompt packs