LARGE and SMALL in Excel - Dashboard Guide

Dashboard Mode - LARGE and SMALL

Dashboard Goal

Find the top 3 highest and bottom 3 lowest sales amounts from a list of sales data to quickly understand best and worst sales performances.

Sample Data

Salesperson	Sales Amount
Alice	500
Bob	300
Charlie	700
Diana	200
Edward	450
Fiona	600
George	350

Dashboard Components

• Top 3 Sales Amounts (KPI cards):
  Formulas:
  =LARGE(B2:B8,1) returns 700
  =LARGE(B2:B8,2) returns 600
  =LARGE(B2:B8,3) returns 500
• Bottom 3 Sales Amounts (KPI cards):
  Formulas:
  =SMALL(B2:B8,1) returns 200
  =SMALL(B2:B8,2) returns 300
  =SMALL(B2:B8,3) returns 350
• Sales Data Table: Shows all salespersons and their sales amounts for reference.

Dashboard Layout

+----------------------+----------------------+
| Top 1 Sale: 700      | Bottom 1 Sale: 200   |
+----------------------+----------------------+
| Top 2 Sale: 600      | Bottom 2 Sale: 300   |
+----------------------+----------------------+
| Top 3 Sale: 500      | Bottom 3 Sale: 350   |
+-----------------------------------------+
| Sales Data Table                        |
| (All salespersons and amounts)         |
+-----------------------------------------+

Interactivity

Add a filter to select specific salespersons or a sales range. When you filter, the top and bottom sales amounts update automatically to reflect only the visible data.

Self Check

If you add a filter to show only sales amounts greater than 400, which components update?

• The Top 3 Sales Amounts cards update to show the highest sales above 400.
• The Bottom 3 Sales Amounts cards update to show the lowest sales above 400.
• The Sales Data Table shows only salespersons with sales above 400.

Key Result

Dashboard shows top 3 highest and bottom 3 lowest sales amounts from sales data.