Sales Analytics — Advanced Excel & Data Modeling Sample

Total Revenue

$2.84M

▲ 14.2% vs Q4

Units Sold

18,472

▲ 8.7%

Avg Deal Size

$3,245

▲ 5.1%

Win Rate

68%

▲ 3pp

Avg Days to Close

▲ 2 days slower

Customer Retention

91.4%

▲ 1.2pp

🔎 Lookup-Driven Sales Rep Dashboard VLOOKUPINDEX/MATCH

Select any rep → auto-populates their region, quota, attainment, and commission tier using lookup formulas:

// Rep details pulled via VLOOKUP from master roster
=VLOOKUP(B2, Roster!A:F, 3, FALSE)

// Commission tier via INDEX/MATCH (handles non-contiguous columns)
=INDEX(CommTable[Tier], MATCH(D2, CommTable[Threshold], 1))

Rep	Region	Quota	Actual	Attainment	Commission	Tier
Sarah Chen	Northeast	$280,000	$342,100	122%	$34,210	★ Accelerator
Marcus Johnson	Southeast	$250,000	$261,800	105%	$19,635	On-Target
Priya Patel	West	$300,000	$287,400	96%	$17,244	Base
Tom Bradley	Midwest	$220,000	$198,600	90%	$11,916	Below
Lisa Wang	Northeast	$260,000	$312,500	120%	$31,250	★ Accelerator
James Rivera	West	$240,000	$248,200	103%	$18,615	On-Target
Amy Foster	Southeast	$230,000	$276,900	120%	$27,690	★ Accelerator
David Kim	Midwest	$210,000	$189,300	90%	$11,358	Below
TOTAL		$1,990,000	$2,116,800	106%	$171,918

📋 Pivot Table — Revenue by Region × Product Line PIVOT TABLEGETPIVOTDATA

Drag-and-drop pivot summarizing 4,200+ transaction rows → Region (rows) × Product (columns)

Region	Enterprise	Professional	Starter	Add-Ons	Grand Total	% of Total
Northeast	$412,300	$298,100	$124,500	$62,800	$897,700	31.6%
Southeast	$328,600	$245,200	$118,900	$48,200	$740,900	26.1%
West	$356,400	$218,600	$98,700	$55,100	$728,800	25.6%
Midwest	$198,200	$142,800	$86,400	$48,500	$475,900	16.7%
Grand Total	$1,295,500	$904,700	$428,500	$214,600	$2,843,300	100%

// Pull specific pivot values into other sheets
=GETPIVOTDATA("Revenue", PivotSheet!A3, "Region", "Northeast", "Product", "Enterprise")

📊 Revenue by Region

Northeast

32%

$897.7K

Southeast

26%

$740.9K

West

26%

$728.8K

Midwest

17%

$475.9K

🍩 Product Mix (Revenue Share)

$2.84M

Q1 Total

Enterprise — $1.30M (45.6%)

Professional — $905K (31.8%)

Starter — $429K (15.1%)

Add-Ons — $215K (7.5%)

📈 Monthly Revenue Trend (12 Months)

$680K

Apr

$720K

May

$695K

Jun

$750K

Jul

$710K

Aug

$785K

Sep

$830K

Oct

$810K

Nov

$860K

Dec

$910K

Jan

$940K

Feb

$990K

Mar

🎯 Quota Attainment by Rep

S. Chen

122%

A. Foster

120%

L. Wang

120%

M. Johnson

105%

J. Rivera

103%

P. Patel

96%

T. Bradley

90%

D. Kim

90%

🟢 Above quota: 5 reps 🟡 Near quota: 1 rep 🔴 Below quota: 2 reps

🎨 Conditional Formatting — YoY Growth by Product × Region CONDITIONAL FORMATTINGYoY %

Heat map showing year-over-year growth rates. Green = growing, Red = declining.

Region	Enterprise	Professional	Starter	Add-Ons	Overall
Northeast	+18.2%	+12.4%	+3.1%	-2.8%	+11.6%
Southeast	+14.8%	+9.2%	+7.6%	+1.2%	+9.8%
West	+22.1%	+4.8%	-5.2%	+8.9%	+10.4%
Midwest	+2.4%	-1.6%	+6.3%	+11.2%	+3.8%

// YoY growth formula applied to each cell
=(ThisYear - LastYear) / LastYear

// Conditional format rules: Green if > 5%, Yellow if 0-5%, Red if < 0%

🧮 Multi-Criteria Summary SUMIFSCOUNTIFSAVERAGEIFS

Dynamic summary pulling from 4,200+ rows of transaction data using multi-criteria formulas:

Metric	Northeast	Southeast	West	Midwest
Total Deals	312	268	284	198
Deals Won	224	178	186	126
Win Rate	71.8%	66.4%	65.5%	63.6%
Avg Deal Size	$4,008	$3,286	$2,965	$2,841
Enterprise Deals	86	72	78	48
Avg Days to Close	21	24	22	28

// Total revenue for Northeast + Enterprise product only
=SUMIFS(Data[Revenue], Data[Region], "Northeast", Data[Product], "Enterprise")

// Count of won deals in West region
=COUNTIFS(Data[Region], "West", Data[Status], "Won")

// Average deal size for Southeast, Professional tier
=AVERAGEIFS(Data[Amount], Data[Region], "Southeast", Data[Product], "Professional")

Regional Sales Analytics Report

📊 Revenue by Region

🍩 Product Mix (Revenue Share)

📈 Monthly Revenue Trend (12 Months)

🎯 Quota Attainment by Rep