Excel Coffee Sales Dashboard Project

Overview

I recently developed an interactive coffee sales dashboard using Microsoft Excel. The objective was to transform raw sales data into a visually engaging and user-friendly analytical tool. The dashboard enables filtering and exploration of coffee sales by various dimensions such as coffee type, customer demographics, country, and more.

---

Data Structure

Tables Included

• Orders: Order ID, Order Date, Customer ID, Product ID, Quantity
• Customers: Customer ID, Name, Email, Country, Loyalty Card Status
• Products: Product ID, Coffee Type, Roast Type, Size, Unit Price

---

Project Workflow

1. Data Cleaning & Preparation

• Removed duplicates and inconsistencies
• Standardized formats for dates and currency fields

2. Data Enrichment Using Lookup Functions

• Used XLOOKUP to pull customer details into the Orders table (Name, Email, Country)
• Used INDEX and MATCH to retrieve product attributes like Coffee Type, Roast Type, Size, and Unit Price

3. Sales Calculation

• Calculated total sales per order using =Quantity * Unit Price

4. Structured Data into Excel Tables

• Converted all ranges into structured Excel Tables for auto-expansion and easy referencing

5. Data Analysis with Pivot Tables and Charts

• Created pivot tables to summarize data by:

  • Coffee Type
  • Customer
  • Country
  • Sales Timeline

• Developed interactive visualizations:

  • Line Chart: Sales trend over time grouped by Coffee Type
  • Bar Charts: Sales by Country and Top Customers

6. Interactivity Integration

• Slicers: Added for Roast Type, Package Size, and Loyalty Card Status
• Timeline Filter: Enabled time-based data exploration

7. Dashboard Assembly

• Designed a clean, user-centric dashboard layout on a dedicated Excel sheet
• Ensured all visuals and slicers are aligned and logically grouped

---

Key Features

• Fully Interactive Charts and Filters
• Real-time Filtering by Multiple Criteria
• Auto-updating Dashboards with New Data
• Simple, Intuitive User Interface

---

Lessons Learned

This project significantly improved my proficiency in:

• Data cleaning and preparation techniques
• Excel lookup functions and conditional logic
• Dynamic reporting using pivot tables and slicers
• Designing dashboards with user experience in mind

Key Excel Methods & Formulas Used

🔹 XLOOKUP

Used to fetch customer information based on Customer ID.

=XLOOKUP(C2, Customers!A:A, Customers!B:B, "", 0)

🔹 INDEX & MATCH

Used to dynamically retrieve product data.

=INDEX(Products!$A$1:$G$1000, MATCH($D2, Products!$A$1:$A$1000, 0), MATCH(I$1, Products!$A$1:$G$1, 0))

🔹 IF Statements

Translated coded data into human-readable labels.

=IF(I2="Rob", "Robusta", IF(I2="Exc", "Excelsa", IF(I2="Ara", "Arabica", IF(I2="Lib", "Liberica", ""))))

=IF(J2="L", "Light", IF(J2="M", "Medium", IF(J2="D", "Dark", "")))

🔹 Sales Calculation

=Quantity * Unit_Price

🔹 Pivot Tables & Interactive Filters

• Summarized sales data by relevant dimensions
• Enabled filters using slicers and timeline

---

Final Thoughts

This dashboard project is a practical demonstration of how Excel can be leveraged to create meaningful business insights. It combines strong data modeling practices with an intuitive interface for end-users. I look forward to applying these techniques in future analytics and business intelligence projects.