Excel Sales Dashboard - Data Analyst PortFolio Project
Data Analysis of Coffee Sales Data in Excel
Download Excel Data with the below link:
https://github.com/raghuveertechzone/...
Excel Dashboard Tutorial: Analyze Coffee Sales Data
In this video, I demonstrate how to create a professional and interactive Excel dashboard for analyzing coffee sales data. Using the excel workbook, we build a dashboard from three sheets: Orders, Customers, and Products. You’ll learn advanced formula techniques and data visualization tips to take your Excel skills to the next level.
What’s Covered in This Video:
1. Data Preparation:
• Use XLOOKUP and INDEX MATCH to pull customer and product details into the Orders sheet for enriched data analysis.
• Combine multiple data sources to create a unified dataset.
2. Dashboard Features:
• Interactive slicers for filtering data by Order Date, Roast Type, Size, and Loyalty Card.
• Visualizations like:
• Total Sales Over Time (line chart).
• Sales by Customer (bar chart).
• Top 5 Customers (bar chart).
3. Key Insights:
• Identify top-performing customers and regions.
• Analyze product performance based on sales and profit metrics.
• Visualize trends in coffee sales over multiple years.
4. Advanced Techniques:
• Efficiently structure data for dashboards using formulas and table formatting.
• Apply conditional formatting and dynamic chart filters for better interactivity.
Time Stamps:
0:00 Intro
0:48 Using XLOOKUP() function to pull data from customers "sheet" into "orders" sheet
1:13 Excel Keyboard Shortcuts used in the video
4:28 Using INDEX MATCH () to pull data from "products" sheet into "orders" sheet
7:03 Using Conditional IF statement for "Coffe_Type" column
7:58 7:03 Using Conditional IF statement for "Roast_Type" column
9:18 Custom Formatting "Size" Column
9:43 Custom Formatting "Sales" and "Unit Price" columns
9:58 Checking for duplicates and removing them
10:18 Converting data to an excel table
10:33 Insert a Pivot table
11:48 Insert a Pivot Chart (Line chart)
15:03 Insert a Timeline for the Line Chart
18:08 Insert a slicer
18:28 Adding a new column to the orders table and refreshing the pivot chart
19:23 Insert 3 slicers
22:58 Slicer and Timeline Formatting
23:53 Insert "Sale by Country" Pivot Chart(Horizontal Bar Chart)
26:08 Insert "Top 5 Customers" Pivot Chart (Horizontal Bar Chart)
26:38 Creating the Dashboard
27:58 Copy all pivot charts to the dashboard
31:58 Advanced Settings to hide sheets and show only the dashboard
32:28 Navigating the Final Dashboard
Why Watch This Video?
• Learn how to create professional-grade Excel dashboards for sales data analysis.
• Master the use of XLOOKUP and INDEX MATCH for pulling data from multiple sheets.
• Improve your skills in Excel formulas, charting, and dashboard design.