Categories: Excel
Tags:

Excel – Canada Sales Dashboard Tutorial – Easy

This dataset contains detailed order-level sales information for a retail business operating across different customer segments and product categories. It captures each transaction with key details such as order date, priority, quantity, sales value, discount, profit, and shipping cost. Customer information like name, province, region, and segment helps analyze buying behavior geographically and demographically. Product-related columns, including category, sub-category, product name, and container type, enable product performance analysis. Shipping details such as ship mode and ship date support logistics evaluation. Overall, this dataset is ideal for practicing Excel Pivot Tables, dashboards, and sales performance analysis.

Dataset Column Explanation (Short Meanings)

  • Order ID
    A unique identifier assigned to each customer order.
  • Order Date
    The date on which the customer placed the order.
  • Order Priority
    The urgency level of the order (Low, Medium, High, Critical, or Not Specified).
  • Order Quantity
    The total number of units ordered for a particular product.
  • Sales
    The total revenue generated from the order before profit calculation.
  • Discount
    The percentage discount applied to the product price.
  • Ship Mode
    The shipping method used to deliver the order (e.g., Regular Air, Express Air, Delivery Truck).
  • Profit
    The net gain or loss from the order after all costs are deducted.
  • Unit Price
    The selling price of a single unit of the product.
  • Shipping Cost
    The cost incurred to ship the order to the customer.
  • Customer Name
    The full name of the customer who placed the order.
  • Province
    The province or state where the customer is located.
  • Region
    The broader geographical region associated with the order.
  • Customer Segment
    The category of customer such as Consumer, Corporate, or Small Business.
  • Product Category
    The main classification of the product (e.g., Furniture, Technology, Office Supplies).
  • Product Sub-Category
    A more detailed classification within the product category.
  • Product Name
    The specific name or model of the product sold.
  • Product Container
    The type of packaging used for shipping the product.
  • Ship Date
    The date on which the order was shipped to the customer.

Download the dataset here: https://www.kaggle.com/datasets/ameeravaqar25/excel-dashboard?select=Excel+Sales+Dashboard.xlsx

Process of Creating a Pivot Table in the Next Sheet (Excel)

Step 1: Select the Dataset

First, click on any single cell inside the dataset.
Excel automatically detects the entire table, so you don’t need to select all columns manually.

Step 2: Go to the Insert Tab

Now, move to the top menu and click on the Insert tab.
In the middle of the ribbon, locate and click on PivotTable.

Step 3: Confirm Data Selection

A dialog box will appear showing the Table/Range.
Check that the selected range covers the entire dataset.
If it looks correct, do not change anything.

Step 4: Choose Pivot Table Location

In the same dialog box, select New Worksheet.
This ensures the Pivot Table is created on a separate sheet, keeping your raw data clean and untouched.

Step 5: Click OK

Click OK, and Excel will instantly create a new sheet with an empty Pivot Table layout.

Step 6: Understand the Pivot Table Fields Panel

On the right side, you will see the PivotTable Fields panel.
This panel contains all dataset columns and four main areas:

  • Rows
  • Columns
  • Values
  • Filters

Step 7: Add Fields to Rows

Drag a categorical field like Region, Customer Segment, or Product Category into the Rows area.
This creates grouped labels vertically.

Step 8: Add Fields to Values

Now drag a numeric field like Sales, Profit, or Order Quantity into the Values area.
Excel automatically summarizes it using Sum.

Step 9: Change Value Calculation (Optional)

If needed, click the dropdown next to the value field, choose Value Field Settings,
and switch between Sum, Average, Count, or Max.

Step 10: Add Filters (Optional)

Drag a field like Order Date, Ship Mode, or Order Priority into the Filters area.
This allows you to filter the Pivot Table dynamically.

Step 11: Rename the Pivot Table Sheet

Finally, rename the new sheet to something meaningful like:
Sales Summary, Profit Analysis, or Region-wise Performance.

Final Tip (For Viewers)

A Pivot Table allows you to summarize thousands of rows in seconds,
without writing a single formula.

Step-by-Step Process to Build This Excel Dashboard

Step 1: Prepare the Data Sheet

  • Keep the raw dataset in Sheet1 (Data)
  • Ensure:
    • No blank rows or columns
    • Proper column headers
    • Dates formatted as Date

👉 Never build charts directly from raw data.

Step 2: Create a Pivot Table Sheet

  • Insert a new worksheet and rename it Pivot Tables
  • All Pivot Tables will be created on this sheet (one below another)

Step 3: Pivot Table for “Total Sales with Discount” (Line Chart)

Pivot Setup

  • Rows → Discount
  • Values → Sales (Sum)

Steps

  • Select Pivot → Insert → Line Chart
  • Sort Discount from smallest to largest
  • Remove gridlines and field buttons

This Pivot explains how sales change as discount increases.

Step 4: Pivot Table for “Total Sales by Region and Province” (Column Chart)

Pivot Setup

  • Rows → Region
  • Rows (below Region) → Province
  • Values → Sales (Sum)

Steps

  • Insert → Clustered Column Chart
  • Format axis labels for clarity

This shows regional and province-wise sales performance.

Step 5: Pivot Table for “Sales by Product Category & Sub-Category” (Pie Chart)

Pivot Setup

  • Rows → Product Category
  • Rows (below) → Product Sub-Category
  • Values → Sales (Sum)

Steps

  • Insert → Pie Chart
  • Convert to 3D Pie (optional, for visuals)
  • Show percentage labels

This explains which product types contribute most to sales.

Step 6: Pivot Table for “Sales by Customer Segment” (Doughnut Chart)

Pivot Setup

  • Rows → Customer Segment
  • Values → Sales (Sum)

Steps

  • Insert → Doughnut Chart
  • Adjust hole size for clean look
  • Display percentage labels

This highlights which customer segment generates maximum revenue.

Step 7: Create Dashboard Sheet

  • Insert a new worksheet
  • Rename it Dashboard
  • Apply background color and header title

Step 8: Move Charts to Dashboard

  • Cut each chart from Pivot sheet
  • Paste into Dashboard sheet
  • Resize and align properly

Step 9: Formatting & Styling

  • Remove chart borders
  • Use consistent color theme
  • Add chart titles
  • Hide gridlines

👉 Dashboard should be clean, readable, and visual-first.

Step 10 (Optional): Add Interactivity

  • Insert Slicers for:
    • Region
    • Customer Segment
    • Order Priority
  • Connect slicers to all Pivot Tables

This makes the dashboard fully interactive.