QuickBooks Online is excellent for managing your books, but its built-in reporting tools leave a lot to be desired. Reports are rigid, hard to customize, and sharing them with stakeholders who don't have QuickBooks access is an exercise in frustration. You end up exporting CSVs, reformatting data, and manually updating numbers every time someone asks for the latest figures.
Google Sheets changes that equation. Once your QuickBooks data is in a spreadsheet, you can build custom dashboards using charts, formulas, conditional formatting, and all the tools you already know. Every formula recalculates when the synced data changes, the sheet is easy to share, and anyone with a browser can view it.
In this tutorial, you'll build a financial dashboard that tracks revenue, outstanding invoices, top customers, and monthly trends, all powered by QuickBooks data synced into Sheets with SheetsSync.
What You'll Build
By the end of this guide, you'll have a dashboard that displays metrics like these (example figures):
- Total Revenue: the total of invoices that are fully paid
- Outstanding Accounts Receivable: what customers still owe you
- Invoices by Status: paid, unpaid and overdue at a glance
- Top 10 Customers: who you invoice the most
- Monthly Invoiced Trend: invoiced amounts plotted over time
- Collection Rate: the share of invoiced amounts that has been collected
Every metric below is built from a single synced Invoices sheet, so it works on the SheetsSync Free plan with manual sync. On Pro, a schedule keeps the data underneath refreshed for you.
Prerequisites
Before you start, you'll need three things:
- QuickBooks Online: any plan (Simple Start, Essentials, Plus, or Advanced) with some invoice history. QuickBooks Desktop is not supported.
- Google Sheets: a free Google account is all you need
- SheetsSync: install the add-on from the Google Workspace Marketplace and connect it to your QuickBooks account
If you haven't connected SheetsSync to QuickBooks yet, check out the complete setup guide first. It takes a few minutes.
1Sync Your Invoice Data
The foundation of your dashboard is a sheet of QuickBooks invoices:
- Add a sheet tab named Invoices, then open Extensions > SheetsSync > Open SheetsSync
- Choose Invoice as the data type and check these fields: Invoice Number, Date, Customer, Total Amount, Balance, A/R Paid, Due Date (the field list keeps this order unless you drag fields to reorder them)
- Set the date range to Last Year or All Time. The default is Last 30 Days, which is too short for trends and will hide older unpaid invoices.
- Click Sync
The sheet now has headers in row 1 and one invoice per row from row 2. With the field order above, the columns are: A Invoice Number, B Date, C Customer, D Total Amount, E Balance, F A/R Paid (Paid or Unpaid), G Due Date. The formulas below use that layout.
If you picked different fields or a different order, check your headers and swap in your own column letters. Optional extras such as Customers or Payments sheets need Pro, because the Free plan covers one data type (one entity or one report).
2Create the Dashboard Sheet
Click the + button at the bottom of your spreadsheet to add a new sheet. Rename it to Dashboard. This is where all your metrics, charts, and summaries will live. Keep it separate from the Invoices sheet, because SheetsSync rewrites the synced sheet on every sync.
Start by setting up a clean layout. In cell A1, type a title like "Financial Dashboard" and make it bold. Leave row 2 blank as a spacer. You'll use rows 3 onward for your key metrics, and the lower rows for charts and data tables.
A good structure looks like this:
- Rows 3-9: key metric cards (labels in column A, formulas in column B)
- Rows 11-21: Top 10 Customers table
- Rows 25-36: monthly breakdown for charts
3Key Metrics with Formulas
Now for the core of your dashboard. Each metric uses standard Google Sheets formulas that reference the synced Invoices sheet.
Total Revenue (Paid Invoices)
This sums the total amount of every invoice whose A/R Paid value is "Paid" (balance of zero):
=SUMIF(Invoices!F2:F, "Paid", Invoices!D2:D)
Outstanding Accounts Receivable
The Balance column already holds what is left to pay on each invoice, so outstanding AR is simply its total:
=SUM(Invoices!E2:E)
Average Invoice Amount
Knowing your typical invoice size helps with forecasting and spotting anomalies:
=AVERAGE(Invoices!D2:D)
Invoice Count by Status
Track how many invoices fall into each category. Place these in adjacent cells:
=COUNTIF(Invoices!F2:F, "Paid")
=COUNTIF(Invoices!F2:F, "Unpaid")
=COUNTIFS(Invoices!E2:E, ">0", Invoices!G2:G, "<"&TODAY())
Collection Rate
This shows what share of everything you invoiced has been collected:
=1 - SUM(Invoices!E2:E) / SUM(Invoices!D2:D)
Format this cell as a percentage. Because it depends on the date range you synced, compare it period to period rather than against a fixed target.
Top 10 Customers by Amount Invoiced
This is slightly more advanced. QUERY can group invoices by customer and rank them:
=QUERY(Invoices!A1:G, "select C, sum(D) where C is not null group by C order by sum(D) desc limit 10 label sum(D) 'Invoiced'", 1)
Inside QUERY, C is the Customer column and D is Total Amount. Change the letters if your layout differs.
Alternatively, if you prefer a formula-based approach without QUERY:
=LET(names, UNIQUE(FILTER(Invoices!C2:C, Invoices!C2:C<>"")), totals, BYROW(names, LAMBDA(n, SUMIF(Invoices!C2:C, n, Invoices!D2:D))), SORTN(HSTACK(names, totals), 10, 0, 2, FALSE))
4Add Charts
Numbers tell the story, but charts make it visual and immediately understandable. Google Sheets has a built-in chart editor that works well for dashboard visualizations. Here are the three charts you should create.
Monthly Invoiced Trend (Line Chart)
First, create a helper table that totals invoices by month. Put the first day of each month in A25:A36 (for example 2026-01-01, 2026-02-01, and so on), then enter this in B25 and fill it down to B36:
=SUMIFS(Invoices!$D$2:$D, Invoices!$B$2:$B, ">="&A25, Invoices!$B$2:$B, "<"&EDATE(A25, 1))
Then select A25:B36, go to Insert > Chart, and choose Line chart. This gives you a clean trend over time. Enable the "Smooth" option for a polished look.
Invoice Status Breakdown (Pie Chart)
Use your Paid, Unpaid, and Overdue counts from Step 3. Select those cells (labels and values), then Insert > Chart and pick Pie chart. This shows the health of your receivables at a glance. If the "Overdue" slice is growing, that's a red flag worth investigating.
Top Customers (Bar Chart)
Select the Top 10 Customers table you built in Step 3. Insert a Horizontal bar chart. This visualization makes it easy to see revenue concentration -- if one or two customers dominate, that's a business risk to be aware of.
To make your charts look cohesive, double-click any chart to open the editor, go to Customize, and set the background to transparent or a dark color. Change the text color to a light gray. This gives the charts a clean, dashboard-like appearance rather than the default white background.
5Set Up Auto-Refresh
A dashboard is only useful if the data is current. On the Free plan, open the sidebar and click Sync whenever you want fresh numbers. On Pro, configure SheetsSync to refresh the Invoices sheet on a schedule:
- Open the sheet's settings (Extensions > SheetsSync > Configure This Sheet) and turn on auto-sync
- Choose a frequency that matches your needs:
- Hourly: the fastest schedule, for active businesses that check the dashboard during the day
- Every 6 hours: good for daily monitoring
- Daily, at an hour you choose: fresh numbers every morning
- Weekly, on a day you choose: enough for a weekly review
With scheduled sync enabled, your Google Sheet pulls fresh data from QuickBooks without you opening it. Open the dashboard in the morning and the numbers are already updated. Pro also offers webhook sync: QuickBooks notifies SheetsSync when an invoice changes, and the sheet is refreshed on the next hourly check.
Scheduled auto-sync and webhook sync are available on the SheetsSync Pro plan ($19.99/month or $159.99/year, with a 14-day free trial and no credit card required). The free plan supports manual sync, which still works well for dashboards you update on demand.
6Share with Your Team
One of the biggest advantages of building your dashboard in Google Sheets is how easy it is to share. No QuickBooks login required for viewers.
- Share with specific people -- click Share in the top right, add email addresses, and set permissions to "Viewer" so they can see the dashboard without accidentally changing formulas
- Create a shareable link -- generate a view-only link for stakeholders. Anyone with the link can see the dashboard but can't edit it
- Embed in other tools -- Google Sheets can be embedded in Notion, internal wikis, or even projected on office TVs
- Export as PDF -- for board meetings or reports, use File > Download > PDF to create a snapshot
The key is setting the right permissions. Give "Editor" access only to people who maintain the dashboard. Everyone else should be a "Viewer" to keep the formulas and structure intact.
Pro Tips for a Better Dashboard
Once you have the basics working, these techniques will take your dashboard from functional to exceptional.
Conditional Formatting for Overdue Invoices
Go to your Invoices sheet, select the data range (for example A2:G), and add a conditional formatting rule: if the due date is before today and there is still a balance, highlight the row in red. This makes overdue items jump out when you're reviewing raw data.
In Google Sheets, choose Format > Conditional formatting, pick "Custom formula is", and enter:
=AND($G2<TODAY(), $E2>0)
Here $G2 is Due Date and $E2 is Balance. Adjust them if your columns differ.
SPARKLINE Formulas for Inline Charts
SPARKLINE creates tiny charts inside a cell, which is perfect for showing trends without taking up space. Add a mini trend right next to your Total Revenue metric:
=SPARKLINE(B25:B36, {"charttype","line"; "color","#34d399"; "linewidth",2})
Where B25:B36 holds your monthly totals from Step 4. The sparkline renders as a small line chart within the cell.
Advanced Filtering with QUERY
The QUERY function lets you write SQL-like statements against your data. Two useful examples:
=SUMIFS(Invoices!D2:D, Invoices!B2:B, ">="&DATE(YEAR(TODAY()), FLOOR(MONTH(TODAY())-1, 3)+1, 1))
=QUERY(QUERY(Invoices!A1:G, "select C, sum(E) where E > 0 group by C label sum(E) 'Balance'", 1), "select Col1, Col2 where Col2 > 5000 order by Col2 desc", 1)
Google Sheets QUERY has no HAVING clause, so the outer QUERY filters the grouped totals.
Named Ranges for Cleaner Formulas
Instead of referencing Invoices!D2:D everywhere, create named ranges. Go to Data > Named ranges and create names like InvoiceAmounts (D2:D), InvoicePaid (F2:F) and InvoiceDates (B2:B). Your formulas become much more readable:
=SUMIF(InvoicePaid, "Paid", InvoiceAmounts)
Freeze the Header Row
On your Dashboard sheet, freeze the first two rows (title and spacer) so they stay visible as you scroll. Go to View > Freeze > 2 rows. This keeps context visible as stakeholders explore the dashboard.
Bringing It All Together
You now have a working QuickBooks dashboard in Google Sheets that tracks the metrics that matter most to your business. Sync the Invoices sheet by hand or on a Pro schedule, the formulas recalculate as soon as the new data lands, and you can share it with anyone, no QuickBooks login required.
The real power of this approach is flexibility. Unlike rigid QuickBooks reports, you can add new metrics, rearrange the layout, combine data from multiple sources, and build exactly the view your team needs. As your business evolves, your dashboard evolves with it.
For deeper receivables work, such as AR aging buckets and an overdue list, see the guide to syncing QuickBooks invoices to Google Sheets. To compare other ways of getting the data in, read how to export QuickBooks to Google Sheets. If you are weighing reporting tools, see SheetsSync vs LiveFlow, and if something doesn't sync as expected, the help center has troubleshooting steps.