How to Create a QuickBooks Dashboard in Google Sheets

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 entirely. With live QuickBooks data flowing into a spreadsheet, you can build custom dashboards using charts, formulas, conditional formatting, and all the tools you already know. The dashboard updates itself, it's instantly shareable, and anyone with a browser can view it.

In this tutorial, you'll build a complete financial dashboard that tracks revenue, outstanding invoices, top customers, and cash flow trends -- all powered by real-time QuickBooks data synced through SheetsSync.

What You'll Build

By the end of this guide, you'll have a dashboard that displays:

• Total Revenue -- sum of all paid invoices
• Outstanding Accounts Receivable -- what customers still owe you
• Invoices by Status -- paid, unpaid, overdue at a glance
• Top 10 Customers by Revenue -- who's driving your business
• Monthly Revenue Trend -- revenue plotted over time
• Payment Collection Rate -- percentage of invoiced amounts that have been paid

Everything refreshes automatically, so your dashboard always shows current data without any manual work.

Prerequisites

Before you start, you'll need three things:

1. QuickBooks Online -- any plan (Simple Start, Essentials, Plus, or Advanced) with some invoice and payment history
2. Google Sheets -- a free Google account is all you need
3. 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 about two minutes.

1Sync Your QuickBooks Data

The foundation of your dashboard is live data. You'll need three QuickBooks data types synced to separate sheets in your spreadsheet:

1. Open your Google Sheet and launch SheetsSync from Extensions > SheetsSync > Open Sidebar
2. Create a sync for Invoices -- this pulls all your invoice records including status, amounts, balances, due dates, and customer references
3. Create a sync for Customers -- this gives you customer names, contact details, and balance information
4. Create a sync for Payments -- this tracks payment amounts, dates, and which invoices they apply to

Run each sync once so the data populates. You should now have three sheets -- Invoices, Customers, and Payments -- each with column headers in row 1 and data starting in row 2.

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.

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-8 -- Key metric cards (labels in column A, formulas in column B)
• Rows 10-22 -- Top 10 Customers table
• Rows 24+ -- Monthly revenue breakdown for charts

3Key Metrics with Formulas

Now for the core of your dashboard. Each metric uses standard Google Sheets formulas that reference your synced data sheets. Here are the formulas you'll need.

Total Revenue (Paid Invoices)

This sums the total amount of all invoices marked as "Paid" in QuickBooks:

Outstanding Accounts Receivable

This calculates the remaining balance on all unpaid invoices -- the money customers still owe you:

Average Invoice Amount

Knowing your typical invoice size helps with forecasting and spotting anomalies:

Invoice Count by Status

Track how many invoices fall into each category. Place these in adjacent cells:

Payment Collection Rate

This shows what percentage of your invoiced amounts have been collected:

Format this cell as a percentage. A healthy collection rate is typically above 85%.

Top 10 Customers by Revenue

This is slightly more advanced. You'll use a combination of QUERY or SORT/UNIQUE to aggregate revenue by customer and rank them:

Adjust the column numbers in the QUERY to match your Invoices sheet. Col3 should be the customer name column, Col5 the total amount, and Col6 the status.

Alternatively, if you prefer a formula-based approach without QUERY:

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 Revenue Trend (Line Chart)

First, create a helper table that aggregates revenue by month. In a section of your Dashboard sheet, use this formula to generate monthly totals:

Create one row per month, adjusting the date range in each. Then select your month labels and totals, go to Insert > Chart, and choose Line chart. This gives you a clean revenue 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 instantly shows the health of your receivables. If the "Overdue" slice is growing, that's a red flag worth investigating.

Top Customers by Revenue (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.

5Set Up Auto-Refresh

A dashboard is only useful if the data is current. Instead of manually running syncs, configure SheetsSync to refresh your data on a schedule.

1. Open the SheetsSync sidebar in your spreadsheet
2. For each sync (Invoices, Customers, Payments), click the schedule option
3. Choose a frequency that matches your needs:
   • Hourly -- best for active businesses that want near-real-time dashboards
   • Every 6 hours -- good for daily monitoring without excessive API calls
   • Daily -- sufficient for weekly review dashboards

With scheduled sync enabled, your Google Sheet pulls fresh data from QuickBooks automatically. Open the dashboard in the morning and the numbers are already updated. No clicking, no exporting, no reformatting.

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 as a live financial display
• 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 entire data range, and add a conditional formatting rule: if the due date is before today AND the status is not "Paid," highlight the row in red. This makes overdue items jump out visually when you're reviewing raw data.

In Google Sheets, set a custom formula rule like:

Adjust $F2 and $G2 to reference your DueDate and Status columns respectively.

SPARKLINE Formulas for Inline Charts

SPARKLINE creates tiny charts inside a cell -- perfect for showing trends without taking up space. Add a mini revenue trend right next to your Total Revenue metric:

Where D24:D35 references your monthly revenue totals. The sparkline renders as a small line chart within the cell, giving an instant visual of the trend direction.

Advanced Filtering with QUERY

The QUERY function is the secret weapon for dashboard builders. It lets you write SQL-like statements against your data. Some powerful examples:

Named Ranges for Cleaner Formulas

Instead of referencing Invoices!E:E everywhere, create named ranges. Go to Data > Named ranges and create names like InvoiceAmounts, InvoiceStatus, and InvoiceDates. Your formulas become much more readable:

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 fully functional QuickBooks dashboard in Google Sheets that tracks the metrics that matter most to your business. The data syncs automatically, the formulas calculate in real time, 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.

If you haven't already, the next steps are to export your QuickBooks data to Google Sheets and set up automatic invoice syncing. Once the data pipeline is in place, building dashboards on top of it is straightforward.