Graphed LogoGraphed
Login

How to Create an Accounts Receivable Dashboard in Excel with ChatGPT

Cody Schneider•

Tracking your accounts receivable can feel like a full-time job of exporting data, wrestling with spreadsheets, and manually updating reports. Creating an interactive dashboard in Excel makes this process much easier, and with a little help from ChatGPT, you don't even need to be a formula expert to get it done. This article will walk you through setting up your data, using ChatGPT to generate the formulas you need, and building a dynamic AR dashboard to keep a clear eye on your cash flow.

First, What Metrics Belong on an AR Dashboard?

Before you start building, it's important to know what you want to track. A good accounts receivable dashboard gives you a snapshot of your financial health at a glance. Focus on metrics that are easy to understand and act upon.

Here are the essentials to include:

  • Total Outstanding A/R: The most basic metric - the total amount of money your customers owe you.

  • A/R Aging: This buckets outstanding invoices into time-based categories (e.g., 0-30 days, 31-60 days, 61-90 days, 90+ days). It helps you quickly identify which invoices are a priority to follow up on.

  • Days Sales Outstanding (DSO): This calculation shows the average number of days it takes for you to collect payment after a sale. A lower DSO is generally better, as it indicates a shorter time to get paid.

  • Overdue Invoices: A simple count or total dollar amount of all invoices that are past their due date.

  • Top Customers by A/R Balance: A list of customers who owe you the most money, helping you focus your collection efforts where they matter most.

Step 1: Get Your Data Ready in Excel

A great dashboard starts with clean, well-structured data. Your goal is to have a single table that contains all the raw information you need, with each row representing one invoice.

You can usually export this data directly from your accounting software (like QuickBooks, Xero, or Stripe). Your Excel sheet or CSV file should include the following columns at a minimum:

  • Invoice ID: A unique identifier for each invoice.

  • Customer Name: The name of the client.

  • Invoice Date: The day the invoice was issued.

  • Due Date: The date payment is due.

  • Invoice Amount: The total amount of the invoice.

  • Amount Paid: How much of the invoice has already been paid.

  • Status: A simple status like "Paid," "Open," or "Overdue."

Once you have your data in an Excel sheet, convert it into a formal Excel Table. This makes formulas and PivotTables much easier to manage. Just click anywhere in your data range and press Ctrl + T (or go to Insert > Table).

Step 2: Use ChatGPT to Create Formulas

Now comes the part where you put your AI assistant to work. Instead of trying to write complex nested IF statements or VLOOKUPs from memory, you can simply ask ChatGPT to do it for you. This saves a massive amount of time and lowers the barrier for people who aren't Excel power users.

Let's create the necessary calculated columns for our dashboard.

Add an "Outstanding Balance" Column

First, we need a column to show the balance still due for each invoice. This is a simple subtraction, but let's see how you'd ask the AI.

Your prompt for ChatGPT:

"I have an Excel table named Table1 with columns for [Invoice Amount] and [Amount Paid]. Write me an Excel formula to calculate the outstanding balance."

ChatGPT will likely give you this formula:

=[@[Invoice Amount]]-[@[Amount Paid]]

Add a new column to your Excel table called "Outstanding Balance" and paste this formula in the first cell. Excel will automatically fill it down for every row.

Add a "Days Overdue" Column

This column will tell us how many days past the due date an invoice is. This is crucial for our aging report.

Your prompt for ChatGPT:

"I need an Excel formula that calculates the number of days an invoice is overdue. My table has a [Due Date] column. The formula should compare the due date to today's date. If the invoice is not yet due, it should show 0."

ChatGPT should provide a formula like this:

=IF(TODAY()>[@[Due Date]], TODAY()-[@[Due Date]], 0)

This formula checks if today's date is past the due date. If it is, it calculates the difference in days. If not, it returns 0. Add this to a new column named "Days Overdue."

Add an "A/R Aging Bucket" Column

This is where ChatGPT really shines. Creating nested IF statements to categorize invoices into aging buckets (e.g., 0-30, 31-60, 61-90, 91+) can be tricky. Asking the AI is far easier.

Your prompt for ChatGPT:

"Write an Excel formula that creates aging buckets based on my [Days Overdue] column. The buckets should be: 'Current' for 0 days, '1-30 Days' for 1-30 days, '31-60 Days' for 31-60 days, '61-90 Days' for 61-90 days, and '91+ Days' for everything over 90."

You'll get back a beautiful nested IF statement:

=IF([@[Days Overdue]]=0, "Current", IF([@[Days Overdue]]<=30, "1-30 Days", IF([@[Days Overdue]]<=60, "31-60 Days", IF([@[Days Overdue]]<=90, "61-90 Days", "91+ Days"))))

Just add a new "A/R Aging" column, paste this in, and you’ve categorized all your outstanding invoices without breaking a sweat.

Step 3: Build the Dashboard with PivotTables and Charts

With all your data and calculated columns in place, you can now build the visual part of your dashboard. We'll use PivotTables as the engine for our charts, as they are the easiest way to summarize large amounts of data.

Create a new sheet in your workbook and name it "Dashboard." This is where we'll assemble all the pieces.

Create an A/R Aging Chart

  1. Click anywhere in your main data table, then go to Insert > PivotTable. Choose to place it in an existing worksheet and select a cell on your new "Dashboard" sheet.

  2. In the PivotTable Fields pane, drag "A/R Aging" to the Rows area.

  3. Drag "Outstanding Balance" to the Values area. It should default to "Sum of Outstanding Balance."

  4. Now, with the PivotTable selected, go to PivotTable Analyze > PivotChart. Choose a Bar Chart.

You now have a clean visual showing your outstanding receivables by aging bucket. You can customize the chart's colors and remove unnecessary elements like the field buttons and legend for a tidier look.

Create Key Metric Cards

Dashboards often feature "cards" at the top showing key numbers. We can create these with PivotTables as well.

  1. Create another PivotTable next to your aging one.

  2. This time, just drag "Outstanding Balance" into the Values area. This single cell now shows your Total Outstanding A/R.

  3. To make it look like a card, select a cell outside the PivotTable and type = then click on the PivotTable cell with the total. This links the value. You can now format this cell with a large font and a bit of color.

  4. Repeat this process to create cards for other metrics, like the distinct count of overdue invoices.

Create a Table for Top Customers by Debt

  1. Create a new PivotTable.

  2. Drag "Customer Name" to the Rows area.

  3. Drag "Outstanding Balance" to the Values area.

  4. Click the dropdown arrow on "Row Labels" and go to Value Filters > Top 10.... You can now filter to show the top 5 or 10 customers by the sum of their outstanding balance.

Step 4: Make it Interactive with Slicers

Slicers are friendly filters that make dashboards dynamic and easy to use for anyone, even those unfamiliar with Excel.

  1. Click on any of your PivotTables.

  2. Go to the PivotTable Analyze tab and click Insert Slicer.

  3. A box will appear with all your column headers. Check boxes for fields you want to filter by, such as "Customer Name" or "A/R Aging."

  4. Now, right-click the slicer and go to Report Connections. Check the boxes for all the PivotTables on your dashboard.

Now, when you click a button on the slicer (e.g., selecting a specific customer), all of your charts and tables will update instantly to show data for just that customer. This unlocks new ways to explore your data without creating dozens of separate reports.

Final Thoughts

Building an accounts receivable dashboard in Excel doesn't have to be a difficult task reserved for spreadsheet pros. By structuring your data correctly and leveraging ChatGPT to write your formulas, you can quickly move on to the fun part of visualizing your A/R with PivotTables, charts, and slicers.

While this approach is powerful, keeping your spreadsheet up-to-date still requires you to manually export and refresh your data. To eliminate that step entirely, we built Graphed to connect directly to your data sources like QuickBooks or Stripe. Instead of just writing formulas for you, you can use plain language to build the entire live-updating dashboard in seconds, allowing you to ask questions and get insights without any manual work.