How to Create an Accounts Receivable Dashboard in Google Sheets

Tracking your accounts receivable can feel like a full-time job, but ignoring it is a quick way to torpedo your cash flow. If you need a clear, at-a-glance view of who owes you money, how much, and when it’s due, building a dashboard is the answer. This guide will walk you through creating a simple but powerful accounts receivable (AR) dashboard right inside Google Sheets, giving you the clarity you need to stay on top of your finances.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why an AR Dashboard Matters (and Why Google Sheets is Perfect for It)

An accounts receivable dashboard is a visual summary of your outstanding invoices. Instead of combing through endless rows of data, a dashboard translates that information into easy-to-understand charts and key metrics. This helps you instantly spot potential issues, like which clients are consistently late or how much money is tied up in overdue payments.

So, why use Google Sheets? It’s free, cloud-based for easy access and collaboration, and flexible enough to build exactly what you need. While it requires some initial setup, it’s a cost-effective solution for small businesses, freelancers, and startups that don’t need complex accounting software.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 1: Get Your Data Organized

Before you can build visualizations, your data needs to be clean and structured correctly. Create a new Google Sheet and dedicate the first tab to your raw AR data. Rename this tab something like "AR_Data".

Your data sheet should have a clear header row with the following columns at a minimum:

• Invoice #: A unique identifier for each invoice.
• Customer Name: The name of the client.
• Invoice Date: The date the invoice was sent.
• Tender: The type of invoice (Commercial or Residential)
• Due Date: The date payment is contractually due.
• Invoice Amount: The total amount of the invoice.
• Amount Paid: The amount the customer has paid so far. Make sure this is 0 if no payment has been made.
• Balance Due: The remaining balance. This will be a calculated column.

Enter your invoice information row by row. Consistency is your best friend here. Use the same format for dates (e.g., MM/DD/YYYY) and make sure customer names are spelled identically every time to avoid issues later.

Step 2: Use Formulas to Add Intelligence to Your Data

This is where your static list starts becoming a dynamic tool. We’ll add a few new columns to your "AR_Data" sheet that calculate important information automatically.

Calculate Balance Due

In the first empty column (let’s assume it’s column H), title it Balance Due. In the first cell below the header (H2), enter this simple formula:

=F2-G2

This subtracts the Amount Paid (G2) from the Invoice Amount (F2). Drag the small blue square at the bottom-right of the cell down to apply this formula to all your rows.

Calculate Days Overdue

Next, create a column called Days Overdue. This tells you exactly how late a payment is. In cell I2, use the following formula:

=IF(H2>0, IF(E2<TODAY(), TODAY()-E2, 0), 0)

Let's break that down:

• IF(H2>0, ...): This first checks if there's still a balance due. If the balance is zero, it won't perform the rest of the calculation and will just output 0.
• IF(E2<TODAY(), TODAY()-E2, 0): If there is a balance, this part checks if the Due Date (E2) is in the past. If it is, it calculates the number of days between today and the due date. If the due date is in the future, it returns 0.

Drag this formula down for all your rows.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Create AR Aging Buckets

AR "aging" is the process of grouping unpaid invoices by how long they've been outstanding. This is one of the most important metrics for financial health. Add a new column called Aging Bucket. In cell J2, enter this IFS formula:

=IF(H2>0, IFS(I2=0, "Current", I2<=30, "1-30 Days", I2<=60, "31-60 Days", I2<=90, "61-90 Days", I2>90, "90+ Days"), "Paid")

This formula checks the "Days Overdue" column (I2) and assigns a category:

• If the balance due (H2) is zero, it's marked as "Paid".
• If it's not overdue, it's "Current".
• Otherwise, it falls into one of the overdue buckets (1-30 Days, 31-60 Days, etc.).

Drag the formula down. Your data sheet is now fully automated!

Step 3: Build Your Dashboard View

Now for the fun part. Create a new tab and name it "Dashboard". This is where your charts and key numbers will live. Keep it clean and simple - the goal is to see everything you need in a single glance.

Display High-Level KPIs

KPIs (Key Performance Indicators) are the big numbers that give you a helicopter view of your financial situation. Let’s calculate a few essential ones.

In your Dashboard tab, create some labels like this:

• Total Outstanding AR in Cell A2: =SUM(AR_Data!H:H) This formula adds up every value in the "Balance Due" column from your data tab. Format this cell as Currency.
• To find the outstanding accounts receivable balances that were residential vs commercial: Outstanding Balance For Commercial Invoices in Cell D2: =SUMIFS(AR_Data!$H:$H, AR_Data!$D:$D,"Commercial") Outstanding Balance For Residential Invoices in Cell E2: =SUMIFS(AR_Data!$H:$H, AR_Data!$D:$D,"Residential")

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Build the AR Aging Report Chart

This chart will show you how your outstanding money is distributed across the aging buckets.

To get an overview you'll create a table comparing each age of open invoices to highlight any late payments:

1. In your Dashboard sheet, create a small summary section called Payment Due in X Days You can create categories such as "Paid," "Current," "1-30 days overdue," "31-60 days overdue," "61-90 days overdue," and a large catch-all group "Over 90 Days overdue."
2. For your first cell, type: Total Paid: =SUMIF(AR_Data!J:J,"Paid",AR_Data!H:H)
3. Next you will automate another key insight: “How long has each invoice gone Unpaid?” This next set of formulas will auto-populate so you and your team have a visual chart outlining the time and amount - side by side.
4. 90+ Days Overdue: You can calculate the remaining balance as follows:

Once your summary table is ready, highlight the data (both labels and values), then go to Insert > Chart > under ‘type’ select "Chart Types" Select the ‘Pie Chart’ to best display a summary of your aged accounts.

Final Thoughts

Building an accounts receivable dashboard in Google Sheets puts you in control of your company’s financial health. It transforms a simple list of invoices into a dynamic tool that helps you manage cash flow, identify payment patterns, and follow up on overdue balances proactively.

While setting up a spreadsheet gives you great customization, it can still require a good deal of manual data entry and formula wrangling, especially as you start pulling financials from different sources like Stripe, Shopify, or your CRM. At Graphed, we created a way to skip all that manual work. You can connect your marketing and sales data sources in seconds and ask questions in plain English to build real-time, interactive dashboards - turning a multi-hour process into a 30-second conversation.