Difficulty
Intermediate–Advanced
Estimated Time
55–80 minutes
The Problem
Executives often want to see progress over time, not just totals:
- “How has revenue accumulated month by month?”
- “Are we ahead or behind the expected pace?”
- “Which region’s cumulative trend is strongest?”
In this challenge, you’ll build a running total (cumulative) view and a simple cumulative trend chart—using formulas (and optionally a chart).
Dataset
Use SalesData:
| Date | Region | Product | Sales |
(You can reuse the SalesData from Challenge #17.)
Your Task
Part A — Create a Monthly Summary Table
On a Dashboard sheet (or a new sheet called Monthly), build a table:
| Month | Monthly Sales | Cumulative Sales |
|---|---|---|
| Jan | ||
| Feb | ||
| Mar | ||
| … |
Rules:
- Month should be actual month start dates (e.g., 2025-01-01, 2025-02-01) formatted as
mmm. - Monthly Sales should sum all SalesData rows within that month.
- Cumulative Sales should be a running total.
Part B — Add a Region Filter (Optional but Recommended)
Add a dropdown:
- Region: All, North, South, East, West
If Region = All, include all regions.
If a region is selected, the monthly and cumulative numbers must only reflect that region.
(You can reuse the “All + Data Validation” pattern from Challenge #14.)
Part C — Add a Cumulative Line Chart
Create a line chart based on:
- X-axis: Month
- Y-axis: Cumulative Sales
If you implement the Region filter, the chart should update when the selection changes.
Part D — Insight Callouts
Add:
- Latest Month Sales
- Cumulative Total
- Best Month (highest Monthly Sales)
Requirements
- Formulas only for calculations (
SUMIFS/EOMONTH/DATE) - Chart is optional but recommended
- If using a Region filter, must support “All”
- Must update automatically when data changes
Learning Objectives
By completing this challenge, you will learn how to:
- Build monthly aggregation tables from transaction data
- Create running totals (cumulative performance)
- Combine filters with time series reporting
- Turn raw data into an executive trend view
Next Challenge
Excel Challenge #19: Month-over-Month Growth & Variance Analysis
🔗 View reference solution on GitHub
(After you’ve tried the challenge)
Want more practical Excel challenges?
Subscribe to the Solve With Excel newsletter and get new problems delivered to your inbox.
SolveWithExcel 