You have spent 29 lessons building individual skills. Now it is time to use them all together. This capstone project mirrors what a real analyst delivers at the end of a sprint — a clean, functional dashboard from raw data to executive-ready output.

Project overview

You will build a monthly sales performance dashboard from a raw CSV file. The final output includes a KPI summary row, a regional breakdown pivot table, a trend line chart, conditional formatting on performance, and dropdown filters for region and month.

💡
Analyst tip: Before touching Excel, sketch the dashboard on paper. Decide what the reader needs to see in the first 5 seconds. Build that first, then add supporting detail.

Dataset setup

Download the sample-sales-data.csv from the Practice Lab. Load it into Excel using Data → From Text/CSV. Format the Revenue and Cost columns as Currency, OrderDate as Date.

Building the summary pivot

Insert → PivotTable. Rows: Region. Values: Sum of Revenue, Sum of Profit. Sort by Revenue descending. This becomes the core of your dashboard.

Adding KPI cards

In a separate area, use SUMIF and COUNTIF formulas to create headline numbers: Total Revenue, Total Orders, Average Order Value, Total Profit Margin %. Format each with a bold large font in a bordered cell.

Chart layer

Insert a Line Chart from the monthly revenue data. Add a data label for the final point. Title it clearly. Remove gridlines. This is your trend visual.

⚠️
Common mistake: Spending 80% of your time formatting and 20% on structure. Get the data right first. A well-structured ugly dashboard beats a beautiful broken one every time.

Conditional formatting layer

Apply a Green-Yellow-Red colour scale to the regional revenue column. Add an icon set (arrow up/down) to the month-over-month change column.

Data validation controls

Add a dropdown list for Region using Data Validation → List. Link it to your pivot table's Report Filter so selecting a region updates the entire dashboard.

🎯 Capstone Challenge

Complete the full dashboard using only the sample CSV. Share a screenshot in the community. Review checklist: KPI row ✓ Pivot table ✓ Trend chart ✓ Conditional formatting ✓ Dropdown filter ✓

FAQ

2-4 hours for a clean first pass. Do not aim for perfection on the first attempt — build it completely first, then refine.
Yes. It is a strong portfolio project because it demonstrates data wrangling, formula writing, visualisation, and dashboard design in one deliverable.