The ONLY EXCEL PORTFOLIO PROJECT YOU NEED

Introduction to the Coffee Sales Dashboard Project

Overview of the Project

• Mochan introduces himself as a data and analytics analyst in the financial services industry, outlining the goal of creating an interactive coffee sales dashboard using Excel.

• The project will cover data gathering, transformation, and visualization techniques including pivot tables and charts. Resources for recreating the project are provided in the video description.

Final Dashboard Features

• The final dashboard will feature a line chart displaying total sales over time segmented by four coffee types: Arabica, Excelsa, Liberica, and Robusta.

• A bar chart will illustrate sales by country (U.S., Ireland, UK) along with another bar chart showing top five customers. A timeline slicer allows users to filter visuals based on selected periods.

Data Gathering Process

Understanding Data Structure

• The dataset includes an orders tab with columns for order ID, date, customer ID, product ID, and quantity; some columns require lookups for additional information.

• Two supplementary tables exist: one for customer details (with primary key as customer ID) and another for product information (with product ID as primary key). Each contains relevant attributes like email addresses and pricing details.

Using XLOOKUP and INDEX MATCH

• Customer data is gathered using XLOOKUP while product data utilizes INDEX MATCH due to its dynamic nature allowing single formula application across multiple rows/columns. This method enhances efficiency in populating related fields such as customer names and emails from their respective tables.

Populating Product Information

Implementing Dynamic Formulas

• For product information retrieval from the products table, a dynamic formula using INDEX MATCH is introduced which allows automatic population of multiple columns based on a single input formula applied across rows/columns efficiently.

• The process involves selecting arrays dynamically through match functions that reference both row numbers (product IDs) and column numbers (coffee type attributes), ensuring accurate data retrieval without manual adjustments each time new entries are added or modified.

Formatting Data for Clarity

Enhancing Readability

• After populating necessary fields like unit price and sales figures through simple multiplication formulas, further enhancements include converting abbreviations into full names for better understanding (e.g., "Rob" becomes "Robusta").

• Custom formatting is applied to date formats to ensure clarity between European and American styles; size columns are also formatted to indicate metric units clearly alongside monetary values displayed in US dollars for consistency throughout the dataset.

Creating Pivot Tables & Charts

Setting Up Pivot Tables

• Transitioning into analysis phase involves converting ranges into structured tables which facilitate easier updates when adding or removing data points; this step is crucial before creating pivot tables/charts that visualize insights effectively from gathered datasets.

• Various methods exist to insert pivot tables quickly; shortcuts enhance efficiency significantly during this stage of dashboard creation where visual representation of total sales over time becomes essential through line charts derived from pivot table analyses based on grouped order dates by month/year combinations chosen earlier in setup process.

This markdown file summarizes key aspects of Mochan's Excel project focused on building an interactive coffee sales dashboard while detailing methodologies employed throughout various stages including data gathering techniques utilizing advanced Excel functions like XLOOKUP & INDEX MATCH alongside effective formatting practices aimed at enhancing overall readability within datasets prior to visualizations via pivot tables/charts being created subsequently thereafter!

How to Create a Coffee Sales Dashboard in Excel

Formatting the Chart

• The speaker begins by hiding filled buttons on the chart for clarity, enhancing visual appeal.

• A solid purple fill is applied to the chart using RGB values (60, 20, 100), creating a lighter shade for better aesthetics.

• Axis titles are added, specifically labeling the primary vertical axis as "USD" and giving the chart a title of "Total Sales Over Time."

• Different colors are assigned to various coffee types: yellow for Liberica, brown for Excelsa, bright blue for Arabica, and red for Robusta.

Inserting and Customizing Timeline

• The speaker demonstrates how to insert a timeline linked to order dates in Excel pivot charts.

• A custom style named "Purple Timeline Style" is created with specific font settings and border colors matching previous design choices.

• Adjustments are made to ensure all elements within the timeline have consistent color schemes and formatting.

Adding Slicers

• Three slicers are planned: roast type name, size, and loyalty card. The process of inserting these slicers into the pivot table is outlined.

• The speaker explains how to add a new column titled "Loyalty Card" in the orders data table using XLOOKUP for auto-population based on customer ID.

Refreshing Pivot Table Data

• After adding new data columns, refreshing the pivot table allows it to recognize updates automatically due to its connection with an Excel table format.

• The importance of maintaining connections between slicers and pivot tables across different worksheets is emphasized when duplicating sheets.

Creating Bar Charts

• A duplicate worksheet is created for country sales bar charts; only relevant fields like country are retained in this new pivot table setup.

• Sorting options are utilized to arrange countries by sales volume effectively before finalizing bar chart designs with distinct colors per country.

Finalizing Dashboard Elements

• The dashboard layout begins with resizing columns and rows appropriately while incorporating visuals from other worksheets into one cohesive view.

• Report connections are established so that slicers affect all visuals simultaneously; testing confirms functionality across different filters.