Lesson 8.5 – Final Dashboard Project & Practice

Lesson Overview

This lesson is a hands-on project where students will create a complete interactive dashboard using all techniques learned in previous lessons. It consolidates Pivot Tables, Pivot Charts, slicers, timelines, KPIs, and conditional formatting into a single professional dashboard.

1. Project Objective

• Build a Sales & Performance Dashboard

• Include:

  • Monthly sales trends

  • Product category distribution

  • Region-wise performance

  • KPIs for sales targets

  • Interactive slicers and timelines

2. Step 1 – Prepare the Data

1. Clean data using Power Query (remove blanks, trim spaces, correct formats)

2. Create a structured table in Excel with columns:

   • Date, Region, Product, Quantity, Unit Price, Sales, Target

3. Add helper columns if required for KPIs

3. Step 2 – Create Pivot Tables & Charts

1. Insert Pivot Tables for key metrics:

   • Monthly Sales by Region

   • Sales by Product Category

   • Total Sales vs Target

2. Insert Pivot Charts for each Pivot Table:

   • Column Chart for monthly sales

   • Pie/Donut Chart for category distribution

   • Line Chart for trend analysis

4. Step 3 – Add Interactivity

1. Insert Slicers for:

   • Region

   • Product Category

2. Insert Timeline for Date column

3. Link slicers and timeline to all relevant Pivot Tables and Charts

5. Step 4 – Add KPIs & Conditional Formatting

1. Create KPIs to show Sales vs Target

2. Apply conditional formatting on sales numbers:

   • Green if ≥ target

   • Yellow if 80–99%

   • Red if < 80%

3. Add icons or symbols to highlight top/bottom performers

6. Step 5 – Arrange Layout

• Use grid layout for all charts, tables, and KPIs

• Place most important metrics at top-left

• Align charts for a clean, professional look

• Leave white space for readability

7. Step 6 – Test & Validate Dashboard

1. Test slicers and timeline interactivity

2. Check KPI and conditional formatting accuracy

3. Refresh data and verify charts update correctly

4. Make adjustments for clarity and visual appeal

8. Best Practices for the Final Project

1. Keep dashboard simple, clear, and focused

2. Ensure all interactivity works correctly

3. Maintain consistent colors and fonts

4. Label charts and KPIs clearly

5. Save a backup copy before sharing

9. Practice Questions / Assignments

1. Create a full dashboard using the given dataset with at least 3 charts and 2 KPIs.

2. Add slicers for Region and Product Category and test interactivity.

3. Add a timeline for Date and link it to all Pivot Charts.

4. Apply conditional formatting to highlight sales targets.

5. Submit a screenshot of the completed dashboard for review.