Scribd
Upload
7 views2 pages

Excel

Students will create an interactive sales performance dashboard in Excel using advanced tools such as PivotTables, Charts, and Conditional Formatting. The project involves setting up sales data, adding calculated columns, applying conditional formatting, creating PivotTables and PivotCharts, designing a dashboard with slicers, and incorporating interactivity through dropdown lists. The expected outputs include a structured SalesData table, meaningful PivotTables and Charts, and a professional dashboard with insights on sales performance.

Uploaded by

James Macalalad
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
7 views2 pages

Excel

Students will create an interactive sales performance dashboard in Excel using advanced tools such as PivotTables, Charts, and Conditional Formatting. The project involves setting up sales data, adding calculated columns, applying conditional formatting, creating PivotTables and PivotCharts, designing a dashboard with slicers, and incorporating interactivity through dropdown lists. The expected outputs include a structured SalesData table, meaningful PivotTables and Charts, and a professional dashboard with insights on sales performance.

Uploaded by

James Macalalad
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

1.

MS EXCEL ACTIVITY: BUILD A SALES PERFORMANCE DASHBOARD

Objective:
Students will create a fully interactive dashboard to analyze and visualize sales performance
data using Excel’s advanced tools: PivotTables, Charts, Conditional Formatting, Named Ranges, and
Data Validation.

INSTRUCTIONS

Step 1: Data Setup


1. Input sales data with the following columns:
o Date
o Salesperson
o Region
o Product
o Units Sold
o Unit Price
o Total Revenue
o Target
2. Format the data as an Excel Table (Ctrl + T) and name it SalesData.

Step 2: Add Calculated Columns

1. Add a column: Total Revenue = Units Sold * Unit Price


2. Add a column: Performance = IF(Total Revenue >= Target, "Met", "Not Met")

Step 3: Use Conditional Formatting

 Highlight cells in the Performance column:


o Green if “Met”
o Red if “Not Met”
 Use Data Bars or Color Scales on Total Revenue to show performance visually.

Step 4: Create PivotTables

1. Insert a PivotTable summarizing:


o Total Revenue by Salesperson
o Total Revenue by Region
o Monthly Sales Trends
2. Insert a PivotChart for each.

Step 5: Dashboard Design

1. On a new sheet named Dashboard:


o Add slicers (for Product, Region, and Salesperson).
o Insert PivotCharts and format them with consistent colors and titles.
o Add KPIs (Key Performance Indicators) using formulas like:
 =AVERAGEIFS(), =MAXIFS(), =SUMIFS()
2. Add dynamic titles using TEXT() and & functions, e.g.:
o "Sales Summary for " & B1 (linked to a slicer cell)

Step 6: Add Interactivity


 Use Data Validation to create dropdown lists for:
o Selecting a product to view stats
 Use INDEX/MATCH or XLOOKUP to pull related data dynamically based on selections.

Expected Outputs for Students


 A well-structured SalesData table with formulas and formatting
 At least 3 meaningful PivotTables and Charts
 A professional-looking Dashboard sheet with slicers and interactivity
 Insights on who performed best, which region exceeded targets, monthly trends, etc.

You might also like