SolveWithExcel

Excel Challenge #9: Monthly Sales Dashboard Summary (KPI Cards + Trend)

Difficulty

Intermediate

Estimated Time

30–40 minutes

The Problem

Raw tables are useful—but decision-makers want answers at a glance.

In this challenge, you’ll combine everything you’ve learned so far to build a compact dashboard-style summary with KPI cards and a trend indicator. This is a common stepping stone between analysis sheets and full dashboards.

Dataset

Create a worksheet named MonthlySales with the following data:

MonthSales
2025-0115100
2025-0214750
2025-0315900
2025-0416250
2025-0516800
2025-0617200

(Use real dates for Month: first day of each month.)

Your Task

Part A — Core Metrics

Create a Dashboard worksheet and calculate the following KPIs:

  1. Latest Month Sales
    • Sales value of the most recent month
  2. Previous Month Sales
    • Sales value of the month before the latest
  3. Month-over-Month Change (%)
    (Latest − Previous) / Previous
  4. 6-Month Average Sales

Part B — KPI Status Flags

Add a Status label for the Month-over-Month change:

  • "Up" if change > 0
  • "Down" if change < 0
  • "Flat" if change = 0

(Optional: add conditional formatting later.)

Part C — Mini Trend Indicator

Add a sparkline (line-type) that shows the 6-month sales trend directly inside a cell.

Requirements

  • Use Excel formulas only
  • No Pivot Tables
  • KPIs must update automatically when data changes

Expected Outcome

Your dashboard sheet should clearly answer:

  • How did sales perform this month?
  • Are we up or down versus last month?
  • What does the recent trend look like?

All without scrolling through raw data.

Hints (Optional)

  • Use MAX() or XLOOKUP() for latest values
  • Keep references dynamic (avoid hard-coded row numbers)
  • Place KPIs in clearly labeled “cards”

Learning Objectives

By completing this challenge, you will learn how to:

  • Summarize data into decision-ready KPIs
  • Build Month-over-Month comparisons
  • Introduce dashboard thinking in Excel

Why This Matters

This pattern appears in:

  • Executive summaries
  • Monthly performance reports
  • Management dashboards
  • Power BI and Tableau prep sheets

This challenge bridges analysis → dashboarding.

Next Challenge

Excel Challenge #10: KPI Dashboard with Conditional Formatting

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