SolveWithExcel

Difficulty

Early Intermediate

Estimated Time

20–30 minutes

The Problem

Your sales data is growing, and now you need a simple analyst-style output:
Which customers generate the most revenue?

Your task is to calculate total sales per customer and produce a Top 5 leaderboard that updates automatically as new rows are added.

This challenge introduces core reporting patterns used in real dashboards:

• aggregation
• de-duplication
• ranking

Dataset

Create a worksheet named SalesData with these columns:

Date	Customer	Product	Quantity	Unit Price
2025-01-02	Northwind BV	Laptop	1	900
2025-01-02	Atlas NV	Mouse	5	20
2025-01-03	Northwind BV	Laptop	2	900
2025-01-03	Delta Group	Keyboard	3	45
2025-01-05	Atlas NV	Laptop	1	900
2025-01-06	Bluewave Ltd	Mouse	10	20
2025-01-08	Delta Group	Laptop	1	900
2025-01-10	Northwind BV	Mouse	4	20
2025-01-12	Bluewave Ltd	Keyboard	2	45
2025-01-15	Orion SA	Laptop	1	900
2025-01-18	Orion SA	Mouse	8	20
2025-01-20	Atlas NV	Keyboard	2	45

Your Task

Part A — Add a calculated column

1. Add a Sales Amount column to SalesData:

• Sales Amount = Quantity × Unit Price

Part B — Build a Customer Totals table

2. Create a new worksheet named CustomerTotals with this structure:

Customer	Total Sales
(unique customers)	(sum of Sales Amount)

Rules:

• Customer list should be unique (no duplicates)
• Total Sales must be calculated via formulas (no manual sums)

Part C — Create a Top 5 leaderboard

3. On a worksheet named Leaderboard, create:

Rank	Customer	Total Sales
1
2
3
4
5

Rules:

• This must update automatically
• Use formulas to pull the Top 5 customers from CustomerTotals

Requirements

• Formulas only (no Pivot Tables for this challenge)
• Must update automatically if new sales rows are added
• Keep formulas readable and copy-friendly

Expected Results (from the provided dataset)

If implemented correctly, total sales per customer should be:

• Northwind BV: 2,780
• Atlas NV: 1,090
• Delta Group: 1,035
• Orion SA: 1,060
• Bluewave Ltd: 290

So your Top 5 should be:

1. Northwind BV — 2,780
2. Atlas NV — 1,090
3. Orion SA — 1,060
4. Delta Group — 1,035
5. Bluewave Ltd — 290

Hints (Optional)

• To build a unique customer list, consider functions like UNIQUE() (if available) or an alternate approach.
• To calculate totals per customer, you’ll likely use SUMIF or SUMIFS.
• For ranking, consider LARGE() + XLOOKUP() or SORT()/TAKE() (depending on your Excel version).

Learning Objectives

By completing this challenge, you will practice:

• Building calculated columns
• Aggregating totals by entity (customer)
• Producing a dynamic Top-N report
• Structuring data for future dashboards

Next Challenge

Excel Challenge #4: Flagging High-Risk Orders with Conditional Logic

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