SolveWithExcel

Excel Challenge #11: Top Products by Category (Dynamic Ranking)

Difficulty

Intermediate

Estimated Time

30–40 minutes

The Problem

Managers rarely want full lists—they want the best performers.

In this challenge, you’ll build a dynamic ranking model that identifies the top-performing product in each category, automatically updating when data changes.

This pattern is commonly used in sales reporting, assortment analysis, and executive summaries.

Dataset

Create a worksheet named ProductSales with the following data:

CategoryProductSales
ElectronicsLaptop Pro18200
ElectronicsTablet Air12400
ElectronicsPhone Max19650
FurnitureOffice Desk8700
FurnitureErgonomic Chair11350
FurnitureStorage Cabinet6400
AppliancesSmart Fridge15400
AppliancesWashing Machine9800
AppliancesMicrowave Plus7200

Your Task

Part A — Rank Products Within Each Category

Add a column named Category Rank that:

  • Ranks products within their own category
  • Assigns 1 to the highest-selling product per category

Part B — Identify Top Product

Add a column named Top Product Flag:

  • "Yes" if Category Rank = 1
  • "No" otherwise

Part C — Category Summary

Create a small summary table showing:

  • Category
  • Top Product
  • Top Product Sales

The summary must update automatically.

Requirements

  • Use Excel formulas only
  • No manual sorting or filtering
  • Rankings must update dynamically

Hints (Optional)

  • Use RANK.EQ or RANK with conditional logic
  • Structured formulas are encouraged
  • Keep formulas copy-friendly

Learning Objectives

By completing this challenge, you will learn how to:

  • Perform group-based ranking in Excel
  • Build dynamic top-N logic
  • Extract category-level insights

Why This Matters

Top-N analysis is used in:

  • Product performance reviews
  • Category management
  • Executive dashboards

This challenge introduces selective insight, not just totals.

Next Challenge

Excel Challenge #12: Region Performance Matrix (SUMIFS + Share of Total)

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