Excel for Data Analytics (Short & Focused)
Back to EXCEL DASHBOARDS

SUBMISSION: Dashboard

Build a complete Excel Dashboard on a single sheet that combines everything from this course: 1) Source your data from a Pivot Table on a separate sheet. 2) Include at least 3 charts (e.g., bar, line, pie) connected to your data. 3) Add Slicers or dropdown controls for interactivity. 4) Use conditional formatting to highlight key metrics. 5) Make it visually clean with consistent colors and alignment. Submit your completed .xlsx file.

33 minutesProject submission
🎯 Free Guest Mode: You are learning for free. Sign in to save your completion progress and quiz answers.

Assignment Instructions

📥 Starter File

⬇ Download Starter File (.xlsx)

The file contains 1 year of retail data (2,600+ rows) across 4 regions, 8 stores, and 3 product categories. A pre-built Dashboard template sheet and Instructions sheet are included.

📋 Assignment Instructions

Build a complete interactive Excel dashboard using everything you've learned in this course. The Dashboard sheet has a pre-built template with placeholders — your job is to fill it in with real data, charts, and interactivity.

Step 1: Create Pivot Tables (on a new "Calculations" sheet)

  1. Monthly Summary — Pivot Table with months as rows, Revenue and Profit as values. This powers the trend line chart.
  2. Revenue by Region — Pivot Table with Region as rows and Revenue as values. This powers the bar chart.
  3. Revenue by Category — Pivot Table with Product Category as rows and Revenue as values. This powers the pie/donut chart.

Step 2: Build KPI Cards (Dashboard sheet, row 4)

  1. Replace each [Formula Here] placeholder with a formula:
    • Total Revenue — =SUM(Data[Revenue]) or reference the Pivot Table
    • Total Profit — =SUM(Data[Profit])
    • Profit Margin % — =Total Profit / Total Revenue, formatted as percentage
    • Total Units Sold — =SUM(Data[Units])

Step 3: Create Charts (replace placeholders on Dashboard)

  1. Chart 1: Monthly Revenue Trend — Line chart from the monthly Pivot Table. Place it in rows 8–19.
  2. Chart 2: Revenue by Region — Bar chart from the region Pivot Table. Place it in rows 23–34.
  3. Chart 3: Revenue by Category — Pie or Donut chart from the category Pivot Table. Place it in rows 38–49.

Step 4: Add Interactivity

  1. Region Slicer — place in the Filters area (columns H–L, around row 8).
  2. Category Slicer — place below the Region Slicer (around row 14).
  3. Date Timeline — place below the Category Slicer (around row 20).
  4. Connect all Slicers and Timeline to all three Pivot Tables.

Step 5: Polish

  1. Conditional Formatting — add green/red formatting to the KPI cards (e.g., green if Profit Margin > 50%, red if < 30%).
  2. Chart Formatting — add titles, remove unnecessary gridlines, use a consistent color scheme across all charts.
  3. Hide helper sheets — right-click the "Data" and "Calculations" tab → Hide. The dashboard should stand alone.
  4. Add your name — replace "Your Name Here" in the dashboard title.

✅ Submission Checklist

  • 4 KPI cards with working formulas
  • 3 charts (Line, Bar, Pie/Donut) sourced from Pivot Tables
  • Region Slicer, Category Slicer, and Date Timeline — all connected
  • Conditional formatting on KPI cards
  • Charts formatted with titles and clean design
  • Data and Calculations sheets hidden
  • Your name in the dashboard title

Upload your completed .xlsx file using the form below.

Checking your access…
← Previous: Building the Dashboard