How to Build a Subscription Business Dashboard with Pivot Tables: MRR, Churn, and Expansion

By the end of this post, you’ll be able to take a raw subscription transaction log—hundreds or thousands of rows with customer IDs, plan names, monthly charges, and event dates—and turn it into a working pivot table dashboard showing monthly recurring revenue (MRR), churn rate, and expansion revenue. You’ll know which fields to build, which calculated fields to add, and which pivot table settings break the numbers if you forget them.

The full process takes about 40 minutes if your source data is already clean. Below is the exact sequence I walk through every time I build this dashboard for a client, and it’s ordered so that each step produces a result you can verify before proceeding to the next one. Skip ahead only if you’re certain your data is structured the way Step 1 describes.

Step 1: Restructure the Source Data into an Event Log

A pivot table can’t compute churn or MRR from a flat customer list where each row is one customer with one current status. You need each row to represent one subscription event: a signup, a plan upgrade, a downgrade, a cancellation, or a renewal that includes a price change.

Build your source table with these five columns, in this order:

Two things make this work. First, every customer has a Signup row, even if they later cancel. Second, a cancellation isn’t a row with a $0 amount—it’s a row with Event Type = Cancellation and the plan’s MRR amount as a negative number if you want to make the math explicit. I use negative values because they let the pivot table sum MRR changes over a month directly, without needing extra logic for cancellation events.

If your billing system exports invoices instead of subscription events, you’ll need to convert each invoice into one or more event rows before loading the data. A monthly invoice for the same plan generates a Signup row on the first invoice and Renewal rows on subsequent ones. This conversion takes time once, but it’s the difference between a dashboard that works and one that produces numbers you can’t trust.

Step 2: Create a Calendar Table to Handle Date Grouping

Pivot tables group dates by month automatically, but they group by calendar month based on the date stored in your event log. That works fine if your billing cycle aligns with the calendar—most do. The problem shows up when you have annual plans billed yearly, or when you have customers whose subscription start dates fall mid-month.

Create a separate table with one row per day covering your entire subscription history, from the earliest signup date to 12 months in the future. Include three columns: Date, Month Name, and Year. Use this table as a source for your pivot table along with the event log, and connect them via the Event Date column.

This gives you two advantages. You can group by Month Name and Year without fighting Excel’s automatic date grouping. And if you ever need to pivot by quarter or show a rolling 12-month view, you already have the date dimension built and ready.

Step 3: Build the MRR Pivot Table with a Calculated Field

Now you’re ready for the first pivot table. Place the calendar Year and Month Name fields in the Rows area, in that order. Drag Customer ID into the Values area and set it to Count. Drag MRR Amount into the Values area as well.

Right-click the MRR Amount value, choose Show Values As, then select Running Total In. Set the Base Field to Month Name. This turns the MRR Amount column into a cumulative total that walks through each month in order.

The running total works because your event log sums to the net MRR change for each month. A month with five new Pro signups at $49 each adds $245 to the cumulative line. A month with two cancellations of a $49 plan subtracts $98. By the end of the year, the running total lands on your current MRR. That number should match what your billing system reports as of today—if it doesn’t, you’ve got a data quality problem in the event log, not in the pivot table.

One setting to check before you trust the output: the sort order of the Month Name field. The pivot table will sort alphabetically if you let it, producing February, then January, then March in your rows. Right-click the Month Name field, choose Sort, then More Sort Options, and set it to sort by Month Name in the order it appears in your source data, which should be chronological January through December. Without this, the running total accumulates months in alphabetical order and the final number bears no relation to reality.

Step 4: Measure Churn Rate Using Month-Over-Month Customer Counts

Churn rate is the percentage of customers you lost in a given month, and the pivot table approach requires two counts: the number of customers active at the start of the month, and the number who cancelled during that month.

Add a second pivot table using the same event log. Put Month Name in the Rows area. Drag Event Type into the Columns area—this gives you columns for Signup, Plan Change, Cancellation, and Renewal. Then drag Customer ID into the Values area and set it to Count.

This gives you the raw count of each event type per month. A customer who signs up on May 10 and cancels on August 22 generates four rows across three months: a Signup in May, a Renewal in June and July, and a Cancellation in August. Counting cancellations per month is straightforward.

The trickier piece is the denominator—the number of customers active at the start of the month. You can’t get that directly from a simple event count. Instead, build a third pivot table with Customer ID in the Rows area and Event Date in the Values area, set to Count. Filter that pivot table to show only event dates before the first day of the month you’re measuring. The count of distinct Customer IDs visible in that filtered table is your active customer base at the start of that month.

In practice, I build a helper column in the source data called Active at Month Start that flags each customer as 1 or 0 based on whether their subscription was live on the first of each month. This requires either a formula in your source data or a small amount of manual calculation, but it’s the most reliable method because it doesn’t depend on getting the filter context right inside the pivot table. Once you have that column, drag it into the Values area, set it to Average—since each customer either is or isn’t active, the average of 0s and 1s gives you the active count directly.

Then churn rate per month is simply Cancellations in that month divided by Active at Month Start for that same month. You can compute this with a calculated field, or you can copy the two pivot tables side by side and divide the numbers manually. If you’re doing this quarterly rather than monthly, the same logic applies with the quarter as the grouping period.

Step 5: Track Expansion Revenue with a Filter on Plan Change Events

Expansion revenue comes from customers who increased what they pay you—an upgrade from Basic at $19 to Pro at $49, or an add-on purchase that raises the monthly amount. Your event log captures this as a Plan Change row where the MRR Amount is positive and the plan name changes.

Build a fourth pivot table. Put Month Name in the Rows area. Drag Event Type into the Filters area and select only Plan Change. Drag MRR Amount into the Values area and set it to Sum.

This gives you the total MRR added from all plan changes in each month. But it includes both upgrades and downgrades, since a downgrade is also a Plan Change event with a negative MRR Amount. To separate expansion from contraction, add another column to the source data called MRR Delta. This value equals the difference between the new plan’s MRR and the previous plan’s MRR for that customer. For an upgrade from $19 to $49, the MRR Delta is +$30. For a downgrade, it’s negative.

Then drag MRR Delta into the Values area of that same pivot table, set it to Sum, and filter to show only positive values. You can do this by right-clicking the field, choosing Value Filters, and setting it to Greater Than 0. The result is expansion MRR per month, isolated from downgrades and cancellations.

One common mistake here is including Signup events in the expansion number. A new customer paying $49 shows up as +$49 in MRR Amount, but that’s new business, not expansion. Filter out Signup events before summing the MRR Delta, or your expansion numbers will look inflated every single month.

Step 6: Combine the Four Pivot Tables into a Single Dashboard View

You now have four separate pivot tables, each answering one question. Arrange them on a single worksheet and format them consistently so the dashboard reads as one view.

Here’s the layout that works well in practice:

Leave each pivot table on its own row band, and add blank rows between them. Use a consistent number format across all MRR values—either $0.00 with no decimals or $0.00 with two decimals, but not both. Set the churn table to percentage format with zero decimals.

Add slicers at the top of the worksheet. Connect the Event Type slicer to all four pivot tables so you can filter the entire dashboard down to a specific plan or a specific customer segment without touching individual pivot table filters. One slicer controlling all four tables is the single most useful interaction this dashboard has.

Step 7: Refresh and Validate the Dashboard Before Relying on It

A dashboard that produces wrong numbers confidently is worse than no dashboard at all. Before you present this to anyone, verify the outputs against a source you trust.

Check three things. First, the final MRR running total for the most recent month should match your billing system’s active recurring revenue figure. Second, the churn rate for a single month should roughly track what your finance team reports—if your active customer count at month start is off by more than 2%, your Active at Month Start column has a formula problem. Third, the sum of expansion MRR plus new business MRR minus contraction MRR should equal the difference between this month’s MRR and last month’s MRR. If that equation doesn’t balance, one of your event rows has an incorrect MRR Delta.

In testing, the most frequent cause of imbalance is a Plan Change row where the MRR Delta was computed against the wrong previous plan. Double-check a few customers who upgraded in the last quarter and confirm their delta matches the published price difference between their old and new plans.

Quick Reference: Pivot Table Setup by Metric

The dashboard workflow above has a concrete, repeatable shape: convert invoices to events, build a calendar table, calculate MRR as a running total, compute churn from a helper column, isolate expansion by filtering event type and delta, then combine all four views on one sheet.

If your data doesn’t match the event log structure described in Step 1, fix that before doing anything else. The pivot table settings in Steps 3 through 6 will produce plausible-looking outputs even from poorly structured input, and you’ll waste more time debugging a wrong number than you spent converting data in the first place.

Start with Step 1. Pull your last 12 months of subscription invoices, convert them into the five-column event log, and build the MRR pivot table from Step 3 first—once that running total matches your billing system, you’ll know the foundation is solid enough to continue.