How to Create an Accounts Receivable Dashboard in Google Sheets with AI

Manually tracking your accounts receivable in a spreadsheet can feel like a constant battle. You spend hours sifting through rows of data just to figure out who has paid, who is late, and how your overall cash flow is looking. This guide will walk you through how to build a dynamic, automated accounts receivable (AR) dashboard right in Google Sheets, so you can stop chasing invoices and start making strategic decisions.

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 Your Business Needs an Accounts Receivable Dashboard

Before jumping into the formulas, it’s important to understand what a dashboard actually does for you. It’s more than just a collection of charts, it’s a command center for your company's financial health. A well-built AR dashboard turns a messy spreadsheet into a clear, at-a-glance view of your incoming cash.

Here’s what you gain:

• Instant Cash Flow Clarity: See exactly how much money is owed to you at any given moment. This helps you forecast cash flow, plan expenses, and avoid unpleasant financial surprises.
• Proactive Collections: Easily identify overdue invoices and spot customers who are habitually late. Instead of reacting to collection problems, you can anticipate them.
• Reduced Manual Work: Once set up, the dashboard updates automatically as you add new invoices and payments. This frees you from the tedious weekly ritual of manually calculating overdue balances.
• Data-Driven Decisions: Is a specific customer type consistently problematic? Are your payment terms too long? Your dashboard helps you answer these questions with data, not just gut feelings.

Step 1: Structure Your Raw Data in Google Sheets

Every great dashboard starts with a clean, well-organized data source. Garbage in, garbage out. Create a new Google Sheet and dedicate one tab to your raw "AR Data." This will be the engine that powers your entire dashboard.

The Essential Data Columns

In your "AR Data" tab, create the following columns. Sticking to this structure is essential for the formulas we'll use later.

• Invoice Number: A unique identifier for each invoice.
• Customer Name: The name of the client.
• Invoice Date: The date the invoice was issued.
• Due Date: The date payment is due.
• Invoice Amount: The total amount of the invoice.
• Amount Paid: The amount the customer has paid so far. Start this at 0.
• Balance Due: The remaining amount owed.

You can also add columns like "Invoice Status" (e.g., Sent, Paid, Overdue) for more detail, but the ones listed above are the core essentials.

Keeping Your Data Clean

To avoid errors, follow a few simple rules:

• Use Formulas for Calculations: In the "Balance Due" column, use the formula =E2-F2 (assuming Invoice Amount is in column E and Amount Paid is in column F). This ensures your balances are always accurate without manual math.
• Consistent Formatting: Make sure all your dates are formatted as dates (Format > Number > Date) and your amounts are formatted as currency.
• One Row Per Invoice: Don't merge cells or add extra summary rows in this tab. Keep it clean and simple.

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 2: Build an Accounts Receivable Aging Report

The heart of any AR analysis is the "aging report." This report groups outstanding invoices into buckets based on how long they have been overdue. It’s the fastest way to see who your problem accounts are. Return to your "AR Data" tab to add a couple of new columns.

Calculating Days Overdue

To create the aging buckets, we first need to know how many days each invoice is overdue. Create a new column called "Days Overdue."

In the first cell of this column (e.g., H2), enter this formula:

=IF(G2>0, TODAY()-D2, "")

Let's break this down:

• G2>0 checks if there is still a balance due.
• If there is, TODAY()-D2 calculates the difference between today's date and the invoice's due date.
• If the balance is zero, it leaves the cell blank ("").

Drag this formula down for all your rows. Now you have a live look at how overdue each unpaid invoice is.

Creating the Aging Buckets

Next, create another column called "Aging Bucket." This is where we’ll categorize each invoice. In the first cell (e.g., I2), enter this nested IF formula:

=IF(H2="","",IF(H2<1,"Current",IF(H2<31,"1-30 Days",IF(H2<61,"31-60 Days",IF(H2<91,"61-90 Days","91+ Days")))))

This looks intimidating, but it’s just a series of simple checks:

• It first checks if the "Days Overdue" cell is blank. If it is, the bucket is also blank.
• If "Days Overdue" is less than 1 (meaning it's not due yet or due today), it labels it as "Current."
• If not, it checks if it’s less than 31 days overdue and labels it "1-30 Days."
• And so on for "31-60 Days," "61-90 Days," and anything over 90 days.

Now, every single invoice that has an outstanding balance is neatly categorized. This is the foundation for our dashboard visuals.

Step 3: Calculating Key AR Metrics

Before we start creating charts, we need to calculate a few high-level metrics. Create a new tab and name it "Dashboard." This is where our visuals will live.

In this tab, set up a small area for your Key Performance Indicators (KPIs). You'll want to calculate:

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

1. Total Accounts Receivable

This is the total amount of money owed to your business. It is your single most important AR number. Use the SUM function pointing to your raw data:

=SUM('AR Data'!G:G)

This formula sums up the entire "Balance Due" column from your data tab.

2. Amount Overdue

How much of your AR is actually late? Use the SUMIF function to only add up overdue amounts.

=SUMIF('AR Data'!H:H,">0",'AR Data'!G:G)

This formula looks at the "Days Overdue" column (H), and if a value is greater than 0, it adds the corresponding "Balance Due" (G) to the total.

3. Days Sales Outstanding (DSO)

DSO tells you the average number of days it takes for you to collect payment after a sale. A lower DSO is better. The formula is:

(Total Accounts Receivable / Total Credit Sales) * Number of Days in Period

For example, to calculate DSO for the last 30 days:

=(B1 / [Total Credit Sales for last 30 days]) * 30

Note: You'll need a way to track your total credit sales over the period you are measuring. You can either manually input this or pull it from another data source.

Step 4: Creating Your Dashboard Visuals

Now for the fun part. Using your "AR Data" tab and the pivot table feature, you can quickly create charts for your "Dashboard" tab.

Chart 1: AR Balance by Aging Bucket (Pie Chart)

This chart shows you what percentage of your outstanding receivables falls into each aging category. It's the best way to see how much of your AR is at risk.

1. Select all your data in the "AR Data" tab.
2. Go to Insert > Pivot Table and create it in a new sheet.
3. In the Pivot Table editor:
4. Filter out any blank or "Current" buckets so you're only looking at overdue money.
5. Select your Pivot Table data and go to Insert > Chart. Choose a Pie Chart.
6. Cut and paste this chart into your "Dashboard" tab.

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

Chart 2: Top 10 Overdue Customers (Bar Chart)

The 80/20 rule often applies here: a small number of customers account for a large portion of your overdue balance. This chart helps you focus your collection efforts on the accounts that will have the biggest impact.

1. Create another pivot table from your raw "AR Data."
2. In the Pivot Table editor:
3. Now, in the Row section for "Customer Name," sort by "SUM of Balance Due" in descending order. Then, under "Filter by values," uncheck everything except the top 10 customers.
4. Select your Pivot Table data and go to Insert > Chart. Choose a Bar Chart this time.
5. Cut and paste it into your "Dashboard" tab.

Arrange your KPIs and charts neatly in the dashboard tab. Use bold fonts and colors to make the key information pop.

The AI Advantage: How AI Supercharges Your Google Sheets Dashboard

Building this dashboard in Google Sheets is a massive step forward, but you can go even further by integrating AI tools. This eliminates the last bit of manual work and helps you find insights you might have missed.

Here’s how AI can enhance this process:

• Natural Language Queries: Instead of building pivot tables, what if you could just ask, "Show me overdue receivables by customer for the last quarter"? AI-powered analytics tools connect directly to your Google Sheet and let you ask questions in plain English, creating charts and reports for you instantly.
• Automated Trend Spotting: An AI tool can analyze your AR data history to spot patterns. It might alert you that a specific customer's payment times have been slipping by a few days each month, a subtle trend that's hard to catch manually but indicates a potential future payment problem.
• Predictive Analytics: By looking at historical payment behavior, invoice amounts, and other factors, AI can even help predict which invoices are at high risk of being paid late. This allows you to follow up proactively before an invoice is even due, securing your cash flow.
• Beyond Static CSVs: You may have seen AI tools like ChatGPT that analyze CSV files. The trouble is, that's just a static snapshot. True AI analytics tools connect live to your Google Sheets data. So when you update a payment status, your dashboard built by the AI reflects that change in real-time.

Final Thoughts

Building an accounts receivable dashboard in Google Sheets empowers you to move from being a reactive bill collector to a proactive cash flow manager. By structuring your data correctly and using formulas and pivot tables, you can create a single source of truth that gives you crucial financial insights in seconds.

While creating this dashboard yourself is a powerful exercise, we know that keeping it updated and digging for deeper insights can still be time-consuming. We built Graphed for exactly this reason. Instead of wrestling with nested formulas, you can connect your Google Sheet or accounting software directly and ask in plain English, "Create an AR aging dashboard" or "What's my DSO trend over the past six months?" We instantly build the live, interactive dashboards for you, so you get the answers you need and can spend your time acting on them - not pulling them.