SolveWithExcel

Excel Challenge #16: Rolling 30-Day Sales (Dynamic Window + Trend)

Difficulty

Intermediate–Advanced

Estimated Time

50–70 minutes

The Problem

Stakeholders rarely want “all-time totals.” They want rolling performance:

  • “How are we doing over the last 30 days?”
  • “Is the trend improving or declining?”
  • “Which region is strongest in the most recent window?”

In this challenge, you’ll build a rolling 30-day sales dashboard using formulas—no Pivot Tables.

Dataset

Use a worksheet named SalesData with:

| Date | Region | Product | Sales |

(You can reuse the data from Challenge #15.)

Your Task

Part A — Define a Rolling Window

On a Dashboard sheet, create:

  • As-Of Date (cell B2)
    • Default it to the latest date in SalesData (formula-based)
  • Window (Days) (cell B3)
    • Set to 30

Then calculate:

  • Start Date = As-Of Date − Window (Days) + 1
  • End Date = As-Of Date

(These must update automatically when As-Of Date changes.)

Part B — Rolling 30-Day Total Sales

Calculate total sales in the rolling window using SUMIFS:

  • Date >= Start Date
  • Date <= End Date

Part C — Rolling Sales by Region

Create a table:

RegionRolling Sales (Last 30 Days)
North
South
East
West

Formula-driven and linked to the rolling window dates.

Part D — Compare to Prior 30 Days

Calculate:

  • Prior Window Total (the 30 days immediately before the current window)
  • Delta = Current Window − Prior Window
  • Delta % = Delta / Prior Window

Part E — Trend Callouts

Add:

  • Trend: “Up” if Delta > 0, “Down” if Delta < 0, “Flat” if Delta = 0
  • Top Region (Rolling): highest rolling sales by region

Requirements

  • Formulas only (SUMIFS, MAX, IF, XLOOKUP, etc.)
  • No Pivot Tables
  • Rolling logic must update if the As-Of date or data changes
  • Use inclusive date comparisons

Learning Objectives

By completing this challenge, you will learn how to:

  • Build rolling-window metrics in Excel
  • Compare current vs prior periods
  • Create trend indicators and KPI deltas
  • Build time-aware dashboards used in real business reporting

Next Challenge

Excel Challenge #17: Year-to-Date Sales Dashboard (YTD Total + YTD by Region + YTD Trend)

🔗 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.