Loan Collection Dashboard in Excel

Build an interactive MIS dashboard with Power Query, PivotCharts, and slicers.

Introduction

30 Second Summary

Every month, loan collection teams export payment data from one system, loan details from another, and spend hours copying between spreadsheets to figure out who actually paid. One wrong VLOOKUP and the whole report falls apart.

In this project, you will build an interactive Excel dashboard that merges raw loan and collection data into a single source of truth. You will present it with charts, slicers, and a timeline that anyone can filter with a click.

What You'll Build

When you finish, you will have a dashboard where clicking "Personal Loan" in a slicer instantly updates every chart to show only personal loan metrics, and dragging a timeline to a specific month filters the entire report to that period.

By the end of this project, you'll have:

  • An interactive dashboard with PivotCharts and slicers that filter every visual simultaneously when you click a single product or status.
  • A one-click-refresh Power Query pipeline that merges two data sources, fixes types, and calculates recovery percentages automatically.
  • A PIVOTBY formula that spills a full cross-tab summary of collections by product and month without building a PivotTable.
  • Secret Mission: An aging bucket analysis with color-coded severity bands and a KPI summary card showing total portfolio at risk.

Are there any prerequisites?

This project requires Microsoft 365 Excel on Windows and assumes you are comfortable with VLOOKUP/XLOOKUP, PivotTables, and basic formulas. No additional installations or accounts are needed.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters.

Finance teams spend hours every month copying data between spreadsheets, rebuilding pivot tables, and manually formatting reports. In this project, you'll build a dashboard that does all of that with a single click.

Create Sample Loan and Collection Data

Every dashboard starts with data. Your collection performance dashboard needs two connected datasets: a master list of loans and a record of monthly payments against those loans.

In this step, you will create both datasets and format them as structured Excel Tables. Structured Tables give your data superpowers. They auto-expand when you add rows, they use named references instead of fragile cell ranges, and Power Query can reference them cleanly by name.

In this step, get ready to:
  • Create a loan master dataset with 15 records on a dedicated sheet.
  • Create a collections dataset with 41 payment records on a separate sheet.
  • Format both datasets as named Excel Tables so they are ready for Power Query.
Create the LoanMaster data on a new sheet

The loan master table holds static information about each loan: who borrowed, what product, how much, and when. This is the reference table that every payment record will link back to.

  • Open Microsoft Excel and create a new blank workbook.
  • Right-click the default sheet tab at the bottom and select Rename.
  • Type LoanData and press Enter.
  • In cell A1, type the first column header LoanID and fill in the remaining headers across row 1 as shown in the data below.
  • Enter the following 15 rows of loan data starting from row 2:
LoanID,CustomerName,Product,DisbursementDate,LoanAmount,Tenure
L001,Rahul Sharma,Personal Loan,01/15/2025,500000,36
L002,Priya Patel,Home Loan,01/20/2025,3500000,240
L003,Amit Kumar,Vehicle Loan,02/01/2025,800000,60
L004,Sneha Reddy,Personal Loan,02/10/2025,300000,24
L005,Vikram Singh,Home Loan,02/15/2025,4200000,180
L006,Neha Gupta,Vehicle Loan,03/01/2025,600000,48
L007,Rajesh Iyer,Personal Loan,03/10/2025,450000,36
L008,Anita Das,Home Loan,03/15/2025,2800000,240
L009,Suresh Menon,Vehicle Loan,04/01/2025,900000,60
L010,Kavita Joshi,Personal Loan,04/10/2025,250000,24
L011,Deepak Nair,Home Loan,04/20/2025,3800000,180
L012,Meera Rao,Vehicle Loan,05/01/2025,700000,48
L013,Arjun Kapoor,Personal Loan,05/15/2025,600000,36
L014,Lakshmi Bhat,Home Loan,05/20/2025,3200000,240
L015,Sanjay Verma,Vehicle Loan,06/01/2025,1000000,60

What do these columns represent?

  • LoanID is the unique identifier that links each loan to its payment records.
  • Product categorizes loans into three types: Personal Loan, Home Loan, and Vehicle Loan. These become your slicer filters later.
  • Tenure is the loan duration in months. Home Loans have the longest tenure (180-240 months) while Personal Loans are shortest (24-36 months).
  • Verify your sheet has headers in row 1 and data in rows 2 through 16 (15 records total).
  • Confirm you have 5 Personal Loans, 5 Home Loans, and 5 Vehicle Loans by scanning the Product column.

Data looks misaligned or columns are merged?

Make sure each value goes in its own cell. The commas in the reference above separate columns. Type each value into the next cell to the right, not into the same cell with commas.

For dates, type them as 01/15/2025 (month/day/year). Excel should recognize them as dates and right-align them automatically.

Help me fix my LoanData sheet layout in Excel.

Create the Collections data on a second sheet

The collections table records what actually happened each month: how much was due, how much was paid, and whether the payment was on time. This is the transactional data that drives your dashboard metrics.

  • Click the + icon next to the LoanData tab at the bottom to create a new sheet.
  • Right-click the new sheet tab and select Rename.
  • Type CollectionData and press Enter.
  • In cell A1, type LoanID and fill in the remaining headers: PaymentDate, AmountDue, AmountPaid, Status.
  • Enter the first 25 rows of collection data starting from row 2:
LoanID,PaymentDate,AmountDue,AmountPaid,Status
L001,02/05/2025,13889,13889,Paid
L001,03/05/2025,13889,13889,Paid
L001,04/05/2025,13889,13889,Paid
L001,05/05/2025,13889,10000,Partial
L001,06/05/2025,13889,0,Overdue
L002,02/05/2025,14583,14583,Paid
L002,03/05/2025,14583,14583,Paid
L002,04/05/2025,14583,14583,Paid
L002,05/05/2025,14583,14583,Paid
L002,06/05/2025,14583,8000,Partial
L003,03/05/2025,13333,13333,Paid
L003,04/05/2025,13333,13333,Paid
L003,05/05/2025,13333,7000,Partial
L003,06/05/2025,13333,0,Overdue
L004,03/05/2025,12500,12500,Paid
L004,04/05/2025,12500,12500,Paid
L004,05/05/2025,12500,12500,Paid
L004,06/05/2025,12500,0,Overdue
L005,03/05/2025,23333,23333,Paid
L005,04/05/2025,23333,23333,Paid
L005,05/05/2025,23333,23333,Paid
L005,06/05/2025,23333,15000,Partial
L006,04/05/2025,12500,12500,Paid
L006,05/05/2025,12500,12500,Paid
L006,06/05/2025,12500,12500,Paid

What do the Status values mean?

  • Paid means AmountPaid equals AmountDue. The borrower paid in full.
  • Partial means AmountPaid is greater than zero but less than AmountDue.
  • Overdue means AmountPaid is zero. The borrower missed the payment entirely.
  • Continue entering the remaining 16 rows immediately below row 26:
L007,04/05/2025,12500,12500,Paid
L007,05/05/2025,12500,12500,Paid
L007,06/05/2025,12500,0,Overdue
L008,04/05/2025,11667,11667,Paid
L008,05/05/2025,11667,11667,Paid
L008,06/05/2025,11667,5000,Partial
L009,05/05/2025,15000,15000,Paid
L009,06/05/2025,15000,15000,Paid
L010,05/05/2025,10417,10417,Paid
L010,06/05/2025,10417,0,Overdue
L011,05/05/2025,21111,21111,Paid
L011,06/05/2025,21111,21111,Paid
L012,06/05/2025,14583,14583,Paid
L013,06/05/2025,16667,9000,Partial
L014,06/05/2025,13333,13333,Paid
L015,06/05/2025,16667,0,Overdue
  • Verify your CollectionData sheet has headers in row 1 and data in rows 2 through 42 (41 records total).
  • Spot-check a few rows: L001 should have 5 payment records (Feb through Jun), and L015 should have 1 record (Jun only, Overdue).

Row count doesn't match 41?

Press Ctrl+End to jump to the last cell with data. The row number should be 42 (header + 41 data rows). If it is higher, check for accidental blank rows or duplicates.

Help me verify my CollectionData row count in Excel.

Format both datasets as named Excel Tables

Raw data in cells is fragile. If you add a row at the bottom, formulas and queries will not pick it up automatically. Converting to a structured Excel Table solves this by making the data self-aware of its own boundaries.

  • Click on the LoanData sheet tab to switch to it.
  • Click any cell inside your data (for example, cell A1).
  • Press Ctrl+T to open the Create Table dialog.
  • Confirm My table has headers is checked, then click OK.

Your data now has filter arrows on each header and alternating row colors. The Table Design tab appears in the ribbon.

  • In the Table Design tab, find the Table Name field on the far left of the ribbon.
  • Clear the default name and type tblLoanMaster, then press Enter.

Why name the table?

Power Query references tables by name. When you later use From Table/Range in the Data tab, it picks up the table by its name. A clear name like tblLoanMaster makes your queries self-documenting.

  • Now click the CollectionData sheet tab.
  • Click any cell inside the data.
  • Press Ctrl+T to open the Create Table dialog.
  • Confirm My table has headers is checked, then click OK.
  • In the Table Design tab, clear the default name and type tblCollections, then press Enter.

Before you verify, what do you expect to see in the Name Box (the small field to the left of the formula bar) when you click inside each table?

  • Click any cell inside the LoanData table and look at the Name Box (the field to the left of the formula bar). It should display tblLoanMaster when you select the full table.
  • Click inside the CollectionData table. The Table Design tab should appear in the ribbon, and the Table Name field should show tblCollections.

Table Design tab not appearing?

The Table Design tab only appears when your cursor is inside a formatted Table. If you do not see it, your data may still be a plain range. Click inside the data area and press Ctrl+T again to convert it.

If the Name Box shows a cell reference like A1 instead of the table name, click the Table Name field in the Table Design tab and re-enter the name.

Help me format my data as a named Excel Table.

✔️ Awesome, I've got everything!

Great. Make sure you have saved your workbook before moving on.

ⓧ I'd like to double check the full data

Scroll up and confirm each data block is present in your workbook:

  • LoanData sheet: 6 column headers (LoanID, CustomerName, Product, DisbursementDate, LoanAmount, Tenure) with 15 rows. Table named tblLoanMaster.
  • CollectionData sheet: 5 column headers (LoanID, PaymentDate, AmountDue, AmountPaid, Status) with 41 rows. Table named tblCollections.
  • Both sheets show the Table Design tab when you click inside the data. Filter arrows appear on every header.

Your two source tables are locked and loaded. Next up, you will connect them with Power Query to build a single unified dataset with calculated columns for Recovery% and DaysOverdue.

Transform and Merge Data with Power Query

In the previous step you created two separate tables: one with loan details and one with collection records. Right now these tables sit in isolation. To analyze collection performance by product or loan amount, you need the data from both tables in one place.

Power Query lets you build a repeatable pipeline that merges these tables, fixes data types, and adds calculated columns. Next month when you paste in fresh data, you just click Refresh All and the entire pipeline reruns.

In this step, get ready to:
  • Load tblCollections into Power Query and add calculated columns (Recovery% and DaysOverdue).
  • Merge tblLoanMaster to bring in loan details like Product and LoanAmount.
  • Load the merged result to a new MergedData sheet and the Data Model.
Load tblCollections into Power Query and fix data types

Before you can merge tables or add calculations, you need to bring your collections data into the Power Query Editor and ensure columns have the correct types.

  • Switch to the CollectionData sheet and click any cell inside the tblCollections table.
  • Click the Data tab on the ribbon.
  • Click From Table/Range in the Get & Transform Data group.

The Power Query Editor opens with a preview of your collections data.

  • Select the AmountDue column header.
  • Hold Ctrl and click the AmountPaid column header to select both columns.
  • Click Home tab, then click the Data Type dropdown and select Decimal Number.
  • Now select the PaymentDate column header.
  • Click the Data Type dropdown again and select Date.

You should see the column icons update: a "1.2" icon for the decimal columns and a calendar icon for PaymentDate.

Why fix data types manually?

Power Query may auto-detect types, but it is not always correct. Explicitly setting AmountDue and AmountPaid to Decimal Number ensures division produces decimal results in your calculated columns. Setting PaymentDate to Date ensures date arithmetic works correctly.

Now you will add two calculated columns that do not exist in the source data.

  • Click the Add Column tab in the Power Query Editor ribbon.
  • Click Custom Column. The Custom Column dialog appears.
  • In the New column name field, type Recovery%.
  • In the Custom column formula box, enter the following formula:
[AmountPaid]/[AmountDue]
  • Click OK.

A new Recovery% column appears on the right side of the preview. Values should be between 0 and 1.

  • Click Custom Column again (still on the Add Column tab).
  • In the New column name field, type DaysOverdue.
  • In the Custom column formula box, enter the following formula:
if [Status] = "Overdue" then Duration.Days(DateTime.LocalNow() - [PaymentDate]) else 0
  • Click OK.

A DaysOverdue column now appears. Rows with Status "Overdue" show the number of days since their PaymentDate. All other rows show 0.

What do these formulas do?

The Recovery% formula divides amount paid by amount due to get a ratio. A value of 1.0 means the full amount was collected. Anything below 1.0 means a shortfall.

The DaysOverdue formula uses an if/then/else pattern. For overdue records, it calculates how many days have passed since the payment date. For all other records, it returns 0.

Seeing an error in the formula box?

Make sure you typed the column names exactly as they appear in the table, including capitalisation. Column references in Power Query M language use square brackets: [AmountPaid] not AmountPaid.

Check the bottom of the Custom Column dialog for a green checkmark. If you see a red error icon, there is a typo in your formula.

Help me fix my Power Query custom column formula.

Merge loan master data into the query

Your query now has collection records with Recovery% and DaysOverdue. To analyze these metrics by loan product, you need to pull in columns from tblLoanMaster.

  • Click the Home tab in the Power Query Editor.
  • Click Merge Queries (the button itself, not the dropdown arrow).

The Merge dialog appears with your current query (tblCollections) already set as the primary table at the top.

  • In the primary table (top), click the LoanID column header to select it as the join column.
  • In the second dropdown at the bottom, select tblLoanMaster as the related table.
  • In the tblLoanMaster preview, click the LoanID column header to match it.
  • In the Join Kind dropdown, select Left Outer (all from first, matching from second).
  • Click OK.

A new column named tblLoanMaster appears on the far right of the preview. It shows "Table" in every row because the merged data is nested.

  • Click the expand icon (double-arrow) in the tblLoanMaster column header.
  • Uncheck Select All Columns, then check only Product, LoanAmount, and DisbursementDate.
  • Uncheck Use original column name as prefix so the columns are named cleanly.
  • Click OK.

Three new columns (Product, LoanAmount, DisbursementDate) now appear in the preview. If you see a duplicate LoanID column from the merge, right-click it and select Remove.

What is a Left Outer join?

A Left Outer join keeps every row from the primary table (tblCollections) and brings in matching data from the related table (tblLoanMaster). If a collection record has no matching loan, the loan columns show null. This ensures you never lose a collection record during the merge.

Columns not expanding correctly?

If you accidentally expanded all columns, you can undo by clicking the X next to the last step in the Applied Steps pane on the right. Then try the expand again, selecting only Product, LoanAmount, and DisbursementDate.

Help me expand merged columns in Power Query.

Load the merged data to a new sheet and the Data Model

Your query is fully transformed. Now you need to load it into Excel so you can use it for formulas and PivotTables in later steps.

  • Click the Home tab in the Power Query Editor.
  • Click the dropdown arrow below Close & Load, then select Close & Load To.
  • In the Import Data dialog, select Table as the output format.
  • Select New worksheet.
  • Check the box for Add this data to the Data Model.
  • Click OK.

Excel creates a new sheet with the merged table. Rename this sheet tab to MergedData by double-clicking the tab name at the bottom.

Why load to both a Table and the Data Model?

Loading as a Table gives you a physical sheet you can reference with dynamic array formulas like PIVOTBY and FILTER in the next step. Adding to the Data Model lets PivotTables access the data without duplicating it in memory.

Before you move on, take a moment to predict what you expect to see in the Queries & Connections pane.

  • Click the Data tab on the ribbon.
  • Click Queries & Connections to open the side pane (if it is not already visible).

You should see your query listed with a row count matching the number of records in your original tblCollections (40-50 rows). The MergedData sheet shows a single table with columns from both source tables plus Recovery% and DaysOverdue.

Row count doesn't match?

If the row count is lower than expected, you may have used an Inner join instead of a Left Outer join. The Inner join drops collection records that don't match a LoanID in tblLoanMaster. To fix this, double-click the query in the Queries & Connections pane to reopen it in Power Query Editor. In the Applied Steps pane, click the merge step and change the Join Kind to Left Outer.

My merged query has fewer rows than expected.

Your data pipeline is in place. Next up, you will use dynamic array formulas to summarize this merged data and see where static numbers alone fall short for stakeholder reporting.

Build Dynamic Array Summaries with PIVOTBY and FILTER

Your Power Query pipeline gives you a single, clean MergedData table with everything in one place. But right now it is just a flat list of 40-50 rows with no structure.

PIVOTBY lets you collapse that flat list into a cross-tab summary with a single formula. You will also use FILTER to pull out problem accounts automatically. These summaries auto-update whenever the underlying data changes.

In this step, get ready to:
  • Create a PIVOTBY cross-tab showing collections by product and month.
  • Use FILTER to extract a live list of overdue accounts.
  • Evaluate whether these number-only summaries are enough for stakeholder reporting.
Create a new Analysis sheet

Dynamic array formulas like PIVOTBY and FILTER "spill" their results across multiple cells. They cannot spill inside an Excel Table, so you need a plain worksheet grid.

  • Right-click any sheet tab at the bottom of your workbook.
  • Click Insert, then select Worksheet and click OK.
  • Double-click the new sheet tab and rename it to Analysis.

Why a separate sheet?

Spilled array formulas are not supported inside Excel Tables. The MergedData sheet contains a structured Table, so placing PIVOTBY or FILTER there would produce an error. A plain worksheet grid lets the results spill freely across as many cells as needed.

Enter the PIVOTBY formula

PIVOTBY creates a two-dimensional summary from flat data. It takes row fields, column fields, values, and an aggregation function. One formula replaces what would otherwise require a full PivotTable.

  • Click cell A1 on the Analysis sheet.
  • Type the following formula and press Enter:
=PIVOTBY(MergedData[Product],TEXT(MergedData[PaymentDate],"MMM-YY"),MergedData[AmountPaid],SUM)

What does this formula do?

  • MergedData[Product] becomes the row labels (Personal Loan, Home Loan, Vehicle Loan).
  • TEXT(MergedData[PaymentDate],"MMM-YY") converts dates into month-year abbreviations (e.g. Jan-25) for column headers.
  • MergedData[AmountPaid] supplies the values to aggregate.
  • SUM tells Excel to add up all matching payments in each cell of the grid.

The formula spills immediately. You should see a grid with product names down the left column, month abbreviations across the top row, and summed payment amounts filling each cell.

Seeing a #SPILL! error?

Make sure there is no data or formatting in the cells where the formula needs to spill. Clear any content to the right of and below cell A1.

If you see a #NAME? error instead, check that your workbook has a table named MergedData on the MergedData sheet. The table name must match exactly.

Still stuck? Help me troubleshoot my PIVOTBY formula error.

Enter the FILTER formula for overdue records

The PIVOTBY output gives you totals, but you also need a live watch list of overdue accounts. The FILTER function extracts only the rows that match a condition.

  • Look at where the PIVOTBY output ends. Click a cell two rows below the last spilled row (leave a gap so the two outputs stay visually separate).
  • Type the following formula and press Enter:
=FILTER(MergedData,MergedData[Status]="Overdue","No overdue records")

What does this formula do?

  • MergedData is the full table to search through.
  • MergedData[Status]="Overdue" is the filter condition. Only rows where Status equals "Overdue" pass through.
  • "No overdue records" is the fallback message if no rows match the condition.

The formula spills all overdue rows from MergedData. You should see rows with Status "Overdue" along with all their columns (LoanID, PaymentDate, AmountDue, AmountPaid, Recovery%, DaysOverdue, Product, etc.).

Seeing the fallback text instead of overdue rows?

If you see "No overdue records" instead of data rows, your tblCollections table may not have any rows with Status set to exactly Overdue. Switch to the CollectionData sheet and confirm at least a few rows have the Status value Overdue (case-sensitive match).

Need help? Help me figure out why my FILTER formula is not returning overdue records.

Review what your summaries can (and cannot) do

Both formulas are working. Before moving on, imagine handing this Analysis sheet to your manager as the monthly collection report.

  • Look at the PIVOTBY output at the top of the sheet.
  • Ask yourself: can you quickly tell which product has the best collection performance this quarter? Can a stakeholder filter this to just one month?

The answer is no. The numbers are correct, but they are walls of digits. There is no way to spot trends at a glance, compare products visually, or filter to a specific time period without scrolling and mental arithmetic.

Why formulas alone are not enough

PIVOTBY and FILTER prove that your data pipeline works and the numbers are correct. That is valuable for you as the analyst.

But stakeholders need to see patterns in seconds, not minutes. They need charts for visual comparison and interactive controls (slicers, timelines) to drill into the data themselves. That is the gap the next step closes.

✔️ Awesome, I've got everything!

Great. Make sure you have saved your workbook before moving on.

ⓧ I'd like to double check the full code

Your Analysis sheet should have exactly two formulas:

Cell A1 (PIVOTBY formula):

=PIVOTBY(MergedData[Product],TEXT(MergedData[PaymentDate],"MMM-YY"),MergedData[AmountPaid],SUM)

A cell two rows below the PIVOTBY spill range (FILTER formula):

=FILTER(MergedData,MergedData[Status]="Overdue","No overdue records")

The PIVOTBY output should show product names as rows, month abbreviations (e.g. Jan-25, Feb-25) as columns, and summed AmountPaid values in each cell. The FILTER output should show only rows where Status equals Overdue.

Your data is clean, your formulas are working, and you have proven the numbers add up. Next up, you will turn these raw numbers into PivotCharts that make the same data tell its story visually.

Create PivotCharts and an Interactive Dashboard

Your PIVOTBY and FILTER formulas proved the merged data works perfectly. The numbers are correct, but they are just rows and columns of figures that a stakeholder would struggle to interpret at a glance.

In this step, you will turn that flat data into visual charts that make trends and comparisons obvious. PivotCharts are charts powered by PivotTables. They let you summarize and visualize data in one step, ready for the interactive filters you will add later.

In this step, get ready to:
  • Create a PivotTable and Combo PivotChart showing collection trends by month.
  • Create a second PivotTable and Clustered Bar PivotChart comparing collected vs due amounts by product.
  • Arrange both charts into a clean dashboard layout with a title.
Create the first PivotTable and Combo chart

A PivotTable summarizes large datasets into compact tables you can rearrange by dragging fields. You will build one that breaks down collection amounts by month and product, then attach a chart to it.

  • Switch to the MergedData sheet.
  • Click any cell inside the MergedData table.
  • Click the Insert tab on the ribbon.
  • Click PivotTable in the Tables group.
  • In the Create PivotTable dialog, confirm the Table/Range shows your MergedData table.
  • Select New Worksheet and click OK.
  • Right-click the new sheet tab at the bottom and select Rename. Type Dashboard and press Enter.

You now have an empty PivotTable on the Dashboard sheet with the PivotTable Fields pane open on the right.

  • In the PivotTable Fields pane, drag PaymentDate to the Rows area.
  • Excel automatically groups dates into Years, Quarters, and Months. In the Rows area, uncheck Years and Quarters so only Months remains.
  • Drag Product to the Columns area.
  • Drag AmountPaid to the Values area. Confirm it reads Sum of AmountPaid.

The PivotTable now shows months as rows, product names as columns, and total collected amounts in the cells. This is the data that will power your first chart.

What is a PivotTable doing differently from PIVOTBY?

PIVOTBY gives you a formula-based cross-tab that lives directly on the worksheet. A PivotTable gives you an interactive object that can attach to charts, slicers, and timelines.

Both summarize data, but only PivotTables can drive PivotCharts and connect to filtering controls. That is why you need both in this project.

Now attach a chart to this PivotTable.

  • Click inside the PivotTable you just created.
  • Go to the PivotTable Analyze tab on the ribbon.
  • Click PivotChart in the Tools group.
  • In the Insert Chart dialog, select Combo from the chart types on the left.
  • Ensure AmountPaid is set as Clustered Column.
  • Click OK.

A Combo PivotChart appears on the Dashboard sheet showing collection amounts as clustered columns, broken out by month and product.

Don't see the PivotTable Analyze tab?

This tab only appears when you click inside a PivotTable. Click any cell within the PivotTable data area and the tab will appear on the ribbon.

If you accidentally clicked outside the PivotTable, the tab hides. Click back inside the PivotTable to bring it back.

Why can't I see the PivotTable Analyze tab on my ribbon?

Create the second PivotTable and Clustered Bar chart

The first chart shows trends over time. This second chart compares how much was collected vs how much was due for each loan product. Together they answer two different questions from the same data.

  • Click a blank cell on the Dashboard sheet well below the first PivotTable (for example, cell A20 or lower).
  • Click the Insert tab.
  • Click PivotTable.
  • In the dialog, click Use this workbook's Data Model or manually set the Table/Range to the MergedData table.
  • Select Existing Worksheet and confirm the location points to the blank cell you selected.
  • Click OK.

Why place it on the same sheet?

Both PivotTables live on the Dashboard sheet so their charts sit together. PivotTables cannot overlap each other, so placing the second one well below the first keeps them separate.

  • In the PivotTable Fields pane for the new PivotTable, drag Product to the Rows area.
  • Drag AmountPaid to the Values area.
  • Drag AmountDue to the Values area as well. You should see both Sum of AmountPaid and Sum of AmountDue listed.

The second PivotTable now shows each product with its total paid and total due amounts side by side. Time to name it and add a chart.

  • Right-click anywhere inside the second PivotTable.
  • Select PivotTable Options.
  • At the top of the dialog, change the name field to PT_ByProduct and click OK.

Why name the PivotTable?

When you connect slicers to multiple PivotTables later, meaningful names like PT_ByProduct make it obvious which table is which. Default names like PivotTable2 tell you nothing.

  • Click inside the PT_ByProduct PivotTable.
  • Go to the PivotTable Analyze tab.
  • Click PivotChart in the Tools group.
  • Select Bar from the chart types, then choose Clustered Bar.
  • Click OK.

A Clustered Bar chart now shows two bars per product. One bar represents total collected. The other represents total due.

Chart showing unexpected values?

If the chart displays counts instead of sums, click the drop-down arrow on the value field in the Values area, choose Value Field Settings, and change the summarization to Sum.

My PivotChart is showing Count instead of Sum. How do I fix this?

Arrange the dashboard layout

Both charts exist, but they are scattered wherever Excel dropped them. A clean layout makes this look like a professional report.

  • Click and drag the first chart (Combo) to the upper-left area of the Dashboard sheet, roughly starting around row 4.
  • Click and drag the second chart (Clustered Bar) to sit next to the first chart on the right, aligned at the same row.
  • Resize both charts by dragging their corner handles so they are roughly equal in width.

Now clean up the sheet appearance.

  • Click the View tab on the ribbon.
  • Uncheck Gridlines to hide the cell grid. The sheet background becomes a clean white canvas.
  • Click the Insert tab.
  • Click Text Box (in the Text group).
  • Draw a text box at the top of the sheet, above both charts.
  • Type Loan Collection Performance Dashboard inside the text box.
  • Select the text, increase the font size to around 16-18pt, and apply Bold formatting.

Before you look at the final result, what do you expect to see on the Dashboard sheet right now?

Look at your Dashboard sheet. You should see a bold title at the top, two charts arranged side by side on a clean white background with no gridlines. The Combo chart on the left shows monthly collection columns. The Clustered Bar chart on the right compares paid vs due amounts for each product.

What do the charts tell you?

The Combo chart reveals collection trends over time. You can spot which months had higher or lower collections. The Clustered Bar chart shows the gap between what was due and what was actually collected for each product type.

Right now, clicking a bar highlights related data, but there is no way to filter to a specific product or time period. The next step adds slicers and a timeline to make that possible.

Charts overlapping or misaligned?

Click a chart to select it, then use the arrow keys on your keyboard for fine positioning. Hold Alt while dragging to snap charts to cell boundaries, which helps align them evenly.

How do I align and evenly space two charts on my Excel Dashboard sheet?

Your dashboard now has two powerful visualizations. But they are still static. In the next step, you will add slicers and a timeline that let anyone filter the entire dashboard with a single click.

Add Slicers, Timeline, and Conditional Formatting

Your charts look professional, but right now they are completely static. A stakeholder who wants to see only Personal Loan data has no way to filter without editing the PivotTable fields directly.

Slicers and a Timeline turn this dashboard into a self-service tool. One click on a slicer button filters every chart simultaneously. Conditional formatting adds a data-layer signal so overdue severity is visible at a glance without reading individual numbers.

In this step, get ready to:
  • Insert Product and Status slicers connected to both PivotTables via Report Connections.
  • Add a PaymentDate Timeline that filters both charts by time period.
  • Apply conditional formatting (data bars and icon sets) to the MergedData sheet.
Insert slicers and connect them to both PivotTables

A Slicer is a visual filter button panel that sits on the sheet. When you connect one slicer to multiple PivotTables through Report Connections, a single click filters every connected chart at once.

  • Switch to the Dashboard sheet.
  • Click inside the first PivotTable (the one showing collection amounts by month).
  • Go to the PivotTable Analyze tab in the ribbon.
  • In the Filter group, click Insert Slicer.
  • In the dialog, check Product and Status.
  • Click OK.

Two slicer panels appear on the Dashboard. Resize them so they are compact and drag them to the top-right area of the sheet, above or beside your charts.

Right now, these slicers only control the first PivotTable. You need to connect them to the second one as well.

  • Right-click the Product slicer.
  • Select Report Connections.
  • In the dialog, check both PivotTables (the monthly one and PT_ByProduct).
  • Click OK.
  • Repeat for the Status slicer: right-click it, select Report Connections, check both PivotTables, click OK.

Test the connection by clicking Personal Loan in the Product slicer. Both charts should instantly filter to show only Personal Loan data.

What do Report Connections do?

By default, a slicer only controls the PivotTable it was created from. Report Connections let you link one slicer to multiple PivotTables so every chart responds to the same filter.

This is what makes the dashboard feel like one unified tool instead of two independent charts.

Only one chart filtering?

Make sure you checked both PivotTables in the Report Connections dialog for each slicer. If you only see one PivotTable listed, confirm that both PivotTables are sourced from the same data range (the MergedData table).

help me connect my slicer to both PivotTables

  • Click the funnel icon with an X on the Product slicer to clear the filter before continuing.
Add a Timeline and connect it to both PivotTables

A Timeline is a date-specific slicer. It lets stakeholders drag across months, quarters, or years to filter all connected visuals by time period.

  • Click inside either PivotTable on the Dashboard sheet.
  • Go to the PivotTable Analyze tab.
  • In the Filter group, click Insert Timeline.
  • Check PaymentDate and click OK.
  • Position the Timeline below the slicers at the top of the dashboard.

Just like with the slicers, the Timeline only controls the PivotTable it was created from. Connect it to both.

  • Right-click the Timeline.
  • Select Report Connections.
  • Check both PivotTables in the dialog.
  • Click OK.

Test the Timeline by clicking a single month (for example, Mar 2025). Both charts should update to show only that month's data.

Drag to select a range

You can click and drag across multiple months in the Timeline to filter a date range. Use the dropdown at the top-right of the Timeline bar to switch between Months, Quarters, and Years.

  • Clear the Timeline filter by clicking the funnel icon with an X on the Timeline control before continuing.
Apply conditional formatting on MergedData

The dashboard charts handle the big picture. Conditional formatting adds a visual signal directly in the data cells so anyone scanning the raw table can immediately spot overdue severity without reading numbers.

  • Switch to the MergedData sheet.
  • Select the entire Recovery% column (click the column header inside the table).
  • Go to the Home tab.
  • Click Conditional Formatting.
  • Select Data Bars.
  • Choose a green gradient fill.

The cells now show green bars whose length represents the recovery percentage. A full bar means 100% collected. A short bar means the borrower paid much less than owed.

Next, apply traffic light icons to the DaysOverdue column.

  • Select the entire DaysOverdue column.
  • Click Conditional Formatting on the Home tab.
  • Select Icon Sets.
  • Choose the 3 Traffic Lights set (the one with red, yellow, and green circles).

The default thresholds divide values into thirds. You need to set custom thresholds that match overdue severity levels.

  • With the DaysOverdue column still selected, click Conditional Formatting again.
  • Select Manage Rules.
  • Select the icon set rule and click Edit Rule.
  • Set the red icon threshold to >=60 (Type: Number, Value: 60).
  • Set the yellow icon threshold to >=30 (Type: Number, Value: 30).
  • Click OK to close the Edit Rule dialog, then OK again to close Manage Rules.

What do these thresholds mean?

Green (0 days overdue) means the account is current. Yellow (30+ days) is an early warning. Red (60+ days) signals a seriously delinquent account that needs immediate attention.

These thresholds mirror industry-standard aging buckets used in collections reporting.

All icons showing the same color?

Make sure you changed the Type dropdown from "Percent" to "Number" for both the red and yellow thresholds. The default uses percentile-based ranges which will not match your DaysOverdue values correctly.

help me fix my icon set thresholds

Before you test the full dashboard, think about what you expect to happen. If you click Home Loan in the Product slicer, what should change?

  • Switch back to the Dashboard sheet.
  • Click Home Loan in the Product slicer.

Both charts filter to show only Home Loan data. The monthly trend chart shows just one product line and the bar chart shows only Home Loan amounts.

  • Clear the slicer by clicking the funnel icon with an X in the slicer header.
  • Drag the Timeline to show only March 2025.

Both charts update to reflect only March data. Every visual on the dashboard responds to a single filter control.

  • Clear the Timeline filter.
  • Switch to the MergedData sheet to confirm the Recovery% column shows green data bars and the DaysOverdue column shows traffic light icons.

Your dashboard is fully interactive. Stakeholders can filter by loan product, payment status, or time period and every chart responds instantly. In the Secret Mission, you will take this further with an aging bucket analysis and KPI summary cards.

Secret mission

Aging Bucket Analysis with KPI Summary

Senior management wants a one-glance view of portfolio risk. Build an aging bucket analysis that categorizes overdue loans by severity (0-30, 31-60, 61-90, 90+ days overdue), color-code each band, and add a KPI summary card showing total portfolio at risk directly on your dashboard.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally in a single Excel workbook, so there are no ongoing costs or cloud resources to worry about.

Resources you used:

  • Your Excel workbook containing all sheets (LoanData, CollectionData, MergedData, Analysis, Dashboard, AgingAnalysis).
  • Power Query connections stored within the workbook.

Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Keep everything running

No action needed. Choose this if you plan to use the workbook as a template for your real collection data.

  • Replace the sample data in the tblLoanMaster and tblCollections tables with actual loan exports from your system.
  • Go to the Data tab and click Refresh All to rerun the entire Power Query pipeline with your new data.
  • All PivotCharts, slicers, timelines, and conditional formatting update automatically.
  • Save the workbook to OneDrive or SharePoint if you want colleagues to filter the dashboard using slicers without editing the source data.

Pause - I'll come back to this later

Save and close the workbook. Excel does not run background processes, so closing the file is all you need to do.

  • Press Ctrl+S to save your workbook.
  • Close Excel. When you reopen the file later, all queries, PivotTables, slicers, and formatting will be exactly as you left them.
  • If you want to update the data when you return, go to the Data tab and click Refresh All.

Delete - I don't want to use this again

Remove the workbook file from your computer entirely.

  • Close Excel if the workbook is still open.
  • Navigate to the folder where you saved the workbook in File Explorer.
  • Right-click the workbook file and select Delete.
  • Empty your Recycle Bin if you want to permanently remove the file.

Nice Work!

Nice Work!

You just built a professional loan collection performance dashboard from scratch. Starting with raw disbursement and repayment data, you transformed it into an interactive MIS report that any stakeholder can filter and explore without touching a formula.

Here's what you learned:

  • Built a Power Query pipeline that merges multi-table financial data, fixes data types, and adds calculated columns with one-click refresh capability.
  • Created an interactive dashboard with PivotCharts, Slicers, and a Timeline connected to multiple PivotTables via Report Connections so one filter controls every visual on the page.
  • Used PIVOTBY and FILTER dynamic array formulas to generate auto-updating cross-tab summaries and a live overdue watch list, plus applied conditional formatting with data bars and icon sets to add visual signals to raw data.
  • Secret Mission: Created an aging bucket analysis with color-coded severity bands and a KPI summary card showing total portfolio at risk.

Ready to quiz yourself?