How to Create an Accounts Receivable Dashboard in Excel

A clear view of who has paid you and who hasn’t is fundamental to managing your business's cash flow. An accounts receivable (AR) dashboard gives you that clarity instantly, transforming lines of invoice data into a high-level overview of your financial health. This post provides a step-by-step guide on how to build a dynamic and interactive AR dashboard in Microsoft Excel.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

What is an Accounts Receivable Dashboard?

An accounts receivable dashboard is a one-page report that visually summarizes your outstanding invoices. Instead of combing through dense spreadsheets, you get a clean, at-a-glance view of your most important AR metrics. Think of it as a financial control panel for your business, helping you answer critical questions quickly:

• How much money is currently owed to us?
• Which customers have been overdue for the longest?
• How are our collections efforts performing over time?
• Are we in a healthy cash flow position?

A well-built dashboard saves time, highlights potential issues before they become major problems, and makes financial reporting much less of a chore.

Step 1: Get Your AR Data Organized

Every great dashboard starts with well-structured data. Before we can build any charts, we need to gather our AR information into a clean, flat table in Excel. Many accounting systems can export this data as a CSV or Excel file.

Create a new sheet in Excel for your raw data, naming it "AR Data." Your table should include these essential columns:

• Invoice Number: A unique ID for each invoice.
• Customer Name: The name of the client.
• Invoice Date: The day the invoice was sent.
• Due Date: The day payment is expected.
• Invoice Amount: The total amount of the invoice.
• Amount Paid: How much of the invoice has already been paid.
• Outstanding Amount: The remaining balance.

Once your data is in place, select any cell within your data range and press Ctrl + T (or Cmd + T on Mac) to format it as an Excel Table. Give your table a descriptive name (like "AR_Data") in the "Table Design" tab that appears. Using an Excel Table is a game-changer, it makes your formulas easier to read and automatically expands to include new data you add later.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Calculating Key Metrics for Our Dashboard

With our data formatted as a table, we can add a few calculated columns that will power our dashboard visuals. These formulas will automatically apply to every row in the table.

1. Days Overdue

This metric tells us how many days have passed since an invoice's due date. In a new column titled "Days Overdue," enter the following formula:

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

Let's break that down:

• IF([@[Outstanding Amount]]>0, ...): This first part checks if there's still a balance on the invoice. We don't care about overdue days for paid invoices.
• TODAY()-[@[Due Date]]: If there is an outstanding amount, this subtracts the invoice's due date from today's date to find the number of days it's overdue. The TODAY() function always gives the current date, making the calculation dynamic.
• ... , 0): If the outstanding amount is zero, it just returns 0.

2. AR Aging Buckets

Aging buckets categorize overdue invoices into groups (e.g., 1-30 days, 31-60 days). This is arguably the most important part of an AR dashboard. In a new column called "Aging Bucket," use a nested IF formula:

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

This formula looks intimidating, but it's just a series of checks. It looks at the "Days Overdue" column and assigns a category. For example, if "Days Overdue" is 45, it categorizes it as "31-60 Days." This automation saves you from having to manually classify hundreds of invoices and is the engine behind our aging report.

Step 2: Building Pivots to Summarize the Data

This is where the magic happens. PivotTables do the heavy lifting of summarizing thousands of rows of data into concise tables that we can then turn into charts. We’ll create a new sheet for our PivotTables, calling it "PivotSheet."

Total Outstanding AR

First, let's get our headline number: the total amount of money owed to the business.

1. Click anywhere in your "AR_Data" table.
2. Go to the Insert tab and click PivotTable. Choose to place it in your "PivotSheet."
3. In the PivotTable Fields pane, drag Outstanding Amount into the Values box.

That's it! Excel instantly calculates the sum of all outstanding invoices. You now have a single, definitive number for your Total AR.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

AR Aging Analysis

Next, let's create a summary of our aging buckets.

1. Create a new PivotTable next to the first one.
2. Drag Aging Bucket into the Rows box.
3. Drag Outstanding Amount into the Values box.

You now have a clean table showing the total outstanding amount for each category (Current, 1-30 Days, 31-60 Days, etc.).

Top Customers by Outstanding Balance

It's always useful to know which customers owe you the most money. Let's create another PivotTable for this.

1. Create a third PivotTable on your "PivotSheet."
2. Drag Customer Name into the Rows box.
3. Drag Outstanding Amount into the Values box.
4. Right-click any value in the "Sum of Outstanding Amount" column and select Sort > Sort Largest to Smallest.

This gives you a ranked list of customers based on their outstanding balance, instantly highlighting your most significant accounts.

Step 3: Creating and Designing Your Dashboard

Now we get to translate our findings into a visually appealing and easy-to-understand dashboard. Create a final, clean sheet and name it "Dashboard." This is where we'll arrange our charts and KPIs.

Creating Your Visuals

1. KPI Card for Total Outstanding AR

The simplest way to display total AR is with a big, bold number. In your "Dashboard" sheet, choose a prominent cell and type the equals sign (=), then navigate to your "PivotSheet" and click the cell that contains the grand total for your first PivotTable. Hit enter. Now you can format this cell to have a large font and a clear label like "Total A/R Outstanding."

2. AR Aging Bar Chart

Bar charts are perfect for comparing aging buckets.

1. Click on your AR Aging PivotTable in the "PivotSheet."
2. Go to the Insert tab and select a 2D Bar Chart.
3. Cut (Ctrl + X) and paste (Ctrl + V) this chart into your "Dashboard" sheet.
4. Clean up the chart: Right-click the gray field buttons (like "Sum of Outstanding Amount") and select Hide all field buttons on chart. This gives it a cleaner look. Add a title like "Amount Receivable by Aging Bucket."

3. Top Debtors Table or Chart

For your top customers, you can either create a bar chart (like we did for aging) showing the top 5 or 10, or you can simply link the data from the PivotTable directly into a small table on your dashboard for a clean list view. Formatting this table with clear borders and bold headers makes it easy to read.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Make it Interactive with Slicers

Slicers are interactive filters that make your dashboard truly dynamic. We can add a slicer to filter the entire dashboard by a specific customer.

1. Click on any of your PivotTables.
2. Go to the PivotTable Analyze tab and click Insert Slicer.
3. Check the box for Customer Name and click OK.
4. Move the slicer to your "Dashboard" sheet.
5. Here's the most important step: Right-click the slicer and select Report Connections. Check the boxes for all of your PivotTables.

Now, when you click on a customer's name in the slicer, every chart and number on your dashboard will update instantly to show data for only that customer. This transforms your static report into an analytical tool that can answer specific questions on the fly.

Step 4: Keeping Your AR Dashboard Up to Date

The best part of this setup is how easy it is to maintain. When you have new invoice or payment data, simply paste it into the bottom of your "AR_Data" table. Because you used an Excel Table, it will automatically expand.

Then, navigate to the Data tab and click the Refresh All button. Excel will rerun all your pivot table calculations and update every chart on your dashboard. Your weekly reporting just went from an hour of work to a single click.

Final Thoughts

Building an accounts receivable dashboard in Excel transforms raw invoice data into a powerful tool for managing your business's financial health. With dynamic PivotTables and charts, you can get a clear, actionable overview of who owes you money, enabling better decision-making and improved cash flow management.

While Excel is great, the setup and manual refreshing process can become cumbersome as your business grows and you start pulling data from different systems like QuickBooks, Salesforce, and Stripe. We built Graphed to make this kind of analysis effortless. Instead of building PivotTables, you just connect your data platforms and describe a dashboard as you would to a colleague: "Build an executive dashboard showing total outstanding AR broken down by aging periods this quarter." Graphed instantly creates a live report that updates automatically, saving you from the spreadsheet treadmill and helping you find answers in seconds.