ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
E-commerce Customer & Order Analytics Dashboard
2
An end-to-end Advanced Excel Analytics Project
3
4
Business Problem
5
An e-commerce retailer selling across 4 channels (Website, Mobile App, Marketplace, Social
6
Media) and 5 product categories needs a single workbook to track order volume, revenue,
7
profitability, customer repeat behavior and delivery/return performance without relying
8
on a BI tool using native Excel capabilities only.
9
10
Objective
11
Build a fully formula-driven analytics workbook with an interactive dashboard that lets a
12
business user filter by Sales Channel, monitor KPIs, identify top customers by revenue, and
13
refresh/export the report in one click via VBA automation.
14
15
Dataset
16
700 synthetic order-level transactions (Jan 2024 - Jun 2026) across Customer, Channel,
17
Product Category, Segment (New/Returning) and delivery/return status modeled on a
18
typical D2C e-commerce order schema.
19
20
Tools & Skills Demonstrated
21
- Advanced formulas: SUMIFS, COUNTIFS, AVERAGEIFS, INDEX-MATCH, IFERROR, SUMPRODUCT,
22
 LARGE, nested IF, TEXT, MAXIFS/MINIFS
23
- Data validation & dynamic dropdown filters
24
- Conditional formatting (data bars, color scales, icon-style rules)
25
- PivotTable-ready structured data + pre-built summary/pivot-style tables
26
- Chart-based dashboard (bar, line, pie) with KPI scorecards
27
- VBA macros for one-click refresh, filtering, highlighting and PDF export
28
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107