How to Create an Accounts Receivable Dashboard in Excel with AI

Chasing down unpaid invoices is a frustrating but critical part of running any business. A well-organized accounts receivable (AR) dashboard in Excel can turn that manual chaos into a clear, actionable overview of your cash flow. This article will walk you through how to build one - first with traditional Excel tools, and then we'll show you how AI can automate the heavy lifting.

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 You Need an Accounts Receivable Dashboard

An AR dashboard goes beyond a simple list of unpaid invoices. It’s a visual control panel for the financial health of your business, helping you see who owes you money, how overdue they are, and where you should focus your collection efforts. A good AR dashboard provides immediate answers to crucial questions:

• What is our total outstanding revenue?
• Which customers are consistently late payers?
• Is our average collection time improving or getting worse?
• How much cash can we expect to collect in the next 30 days?

By visualizing this information, you can stop reacting to payment issues and start proactively managing your cash flow, reducing bad debt, and improving customer payment behavior.

Key Metrics for Your Accounts Receivable Dashboard

Before you start building, you need to know what you want to measure. A great AR dashboard doesn't need dozens of metrics, it just needs the right ones. Here are the essentials that provide a comprehensive view of your receivables.

1. Accounts Receivable Aging Report

This is the cornerstone of any AR dashboard. An AR aging report buckets your outstanding invoices by how long they’ve been overdue. It immediately shows you how much of your money is tied up in recent, manageable debt versus old, high-risk debt.

Typical aging buckets include:

• Current: Invoices not yet due.
• 1-30 Days Past Due: Recently overdue invoices requiring a gentle nudge.
• 31-60 Days Past Due: Getting serious. These accounts need follow-up.
• 61-90 Days Past Due: A sign of potential payment problems.
• 91+ Days Past Due: High-risk accounts that might become bad debt.

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

2. Days Sales Outstanding (DSO)

DSO measures the average number of days it takes for your company to collect payment after a sale has been made. A lower DSO is better, as it indicates you’re getting paid faster, which improves your cash flow. Monitoring this metric over time shows whether your collection efforts are becoming more or less effective.

The general formula is:

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

For example, if you have $50,000 in AR for a month where you had $100,000 in credit sales, your DSO for that 30-day period would be 15 days.

3. Total Outstanding Receivables

This is a straightforward but vital metric: the total sum of all money owed to your business at a given time. Displaying this number prominently on your dashboard gives you an instant snapshot of your current receivables position. You can track this on a timeline (e.g., a weekly line chart) to see how it trends over time.

4. Top 10 Customers by Outstanding Balance

Risk is often concentrated. A simple list showing which customers have the largest outstanding balances helps you identify which relationships are most critical to your cash flow. If a single customer represents a huge portion of your outstanding AR, their failure to pay on time poses a significant risk to your business.

Setting Up Your Data in Excel

Your dashboard is only as good as the data powering it. Before you can build anything, you need a clean, organized data source. Create a new sheet in Excel named "AR Data" and structure it like a simple database table. Each row should represent a single invoice.

Your data table should include these columns at a minimum:

• CustomerID: A unique identifier for each customer.
• CustomerName: The name of the customer.
• InvoiceNumber: The unique invoice number.
• InvoiceDate: The date the invoice was issued.
• DueDate: The date payment is due.
• InvoiceAmount: The total amount of the invoice.
• AmountPaid: The current amount paid toward the invoice.
• Status: The current status of the invoice (e.g., "Paid," "Open," "Overdue").

For more advanced analysis, you can add calculated columns directly in this table:

• BalanceDue: =[InvoiceAmount] - [AmountPaid]
• DaysPastDue: If the Status is "Open" or "Overdue", calculate the difference between today's date and the DueDate. Use a formula like: =IF([Status]="Paid", 0, TODAY() - [DueDate])
• AgingBucket: Use a nested IF function to categorize each invoice based on its DaysPastDue: =IF(F2<=0, "Current", IF(F2<=30, "1-30 Days", IF(F2<=60, "31-60 Days", IF(F2<=90, "61-90 Days", "91+ Days"))))

Once your data is clean and structured, format it as an Excel Table (select the data and press Ctrl+T). This makes it easier to reference in your PivotTables and ensures that new data you add is automatically included in your dashboard refresh.

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

Building the AR Dashboard (The Traditional Way)

Now for the fun part. The traditional method for building an Excel dashboard uses PivotTables and PivotCharts to summarize your data, combined with Slicers for interactivity.

Step 1: Create Your PivotTables

A PivotTable is the best way to summarize your raw data into useful reports. For our AR Aging report, the steps are:

1. Click anywhere inside your "AR Data" table.
2. Go to the Insert tab and click PivotTable. Choose to place it in a new worksheet and rename that sheet "Dashboard."
3. From the PivotTable Fields pane, drag and drop the fields:

This creates a simple summary showing the total balance due for each aging category. You can repeat this process to create other PivotTables, such as one showing Total Outstanding Balance by CustomerName.

Step 2: Visualize with PivotCharts

Numbers are great, but charts make the insights impossible to ignore.

1. Click on the AR Aging PivotTable you just created.
2. Go to the PivotTable Analyze tab and click PivotChart.
3. Choose a Bar Chart or Pie Chart to visualize the aging buckets. The Bar Chart is an industry standard and is usually easier to read.
4. Clean up the chart: remove unnecessary field buttons, add a clear title ("AR Aging Summary"), and format the colors for clarity.

You can then create other charts, like a line chart to track Total Outstanding AR over time or a treemap to show your largest overdue customers.

Step 3: Add Slicers for Interactivity

Slicers are user-friendly filters that allow anyone to drill down into the data without having to understand PivotTables.

1. Select any of your PivotCharts.
2. Go to the PivotTable Analyze tab and click Insert Slicer.
3. Check the boxes for the fields you want to filter by, like CustomerName or Status.
4. To connect one slicer to all the charts on your dashboard, right-click the slicer, select Report Connections, and check the boxes for all the PivotTables you've created.

Now, when you click on a customer's name in the slicer, every chart on your dashboard will update to show data for just that customer.

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

Enter AI: A Faster Way to Build Your Dashboard

Building that dashboard manually gives you a lot of control, but let's be honest - it’s time-consuming, requires a deep understanding of PivotTables, and can feel clunky if you need to make changes.

This is where AI changes the game entirely. Modern AI tools, including features within Excel itself (like Copilot) and connected platforms, allow you to bypass the manual setup of PivotTables and chart design. Instead of navigating menus and dragging fields, you just describe what you want to see in plain English.

Imagine your clean "AR Data" table is ready. Instead of the multi-step process above, you could simply type a prompt, such as:

“Create a bar chart showing the total outstanding balance for each aging bucket.”

The AI analyzes your data table, interprets your request, figures out the necessary summarization (just like a PivotTable would), and generates the chart for you instantly. The real advantage here is speed and accessibility. You don’t need to be an Excel power user to get powerful insights.

You can get more specific and request complex summaries in seconds:

• "Show me a pie chart of our AR Aging split."
• "Generate a table showing the top 5 customers with overdue balances greater than 60 days."
• "What is our average days past due for invoices in Q4?"

This conversational approach lowers the barrier to data analysis. Team members who aren't comfortable with complex formulas or PivotTable configurations can now explore data and get answers independently. The drudgery of building reports is automated, freeing you up to focus on the insights - like figuring out why certain invoices are chronically late and what you can do about it.

Final Thoughts

Creating an accounts receivable dashboard empowers you to take control of your company's cash flow. While the traditional process of building one in Excel with PivotTables is highly effective, it requires manual effort and technical skill. AI tools radically simplify this by letting you generate charts and summaries by just describing what you need.

While AI in Excel is a great leap forward for creating reports from static spreadsheets, we built Graphed for businesses that need to go one step further. Instead of manually exporting data from your accounting software, you can connect tools like QuickBooks, Stripe, and others directly. We sync your financial data in real time, so you can build live dashboards automatically. Just ask questions in simple English - like "create a dashboard comparing this month's revenue vs. last month's" - and get answers instantly, without ever exporting another CSV file.