Creating a Sales Dashboard Report in Microsoft Excel

Overview

• Divided into two parts:
  1. Explanation of source data and dashboard features
  2. Step-by-step design of the dashboard report

Source Data

• Columns:
  • Date (First Column)
  • Product Name (Second Column)
  • Category (Third Column)
  • Sales Representative (Fourth Column)
  • City (Fifth Column)
  • Number of Units Sold
  • Unit Price
  • Amount (Last Column)
• Time Frame: First week of January 2021 to the last week of December 2021

Dashboard Features

• Charts Included:
  • Bar Chart: Amount of sales by month
  • Bar Chart: Amount of sales by sales representative
  • Line Chart: Amount of sales by city
  • Column Chart: Product by sales
  • Pie Chart: Category by sales
• Slicer:
  • Filters data in charts by selected months
  • Allows multiple selections using the Ctrl key

Step-by-Step Process

Preparation of Data

1. **Initial Data Overview: 1560 Records
2. Pivot Table Preparation:

• Click inside table -> Insert -> Pivot Table -> New Worksheet

Creating Pivot Tables

1. Sales by City:

• Drag & drop 'City' to Rows
• Drag & drop 'Amount' to Values
• Format number: Right-click -> Number Format -> Use 1000 separator, no decimal

2. Product-wise Sales:

• Copy previous pivot table
• Replace 'City' with 'Product'

3. Sales by Sales Rep:

• Copy previous pivot table
• Replace 'Product' with 'Sales Rep'

4. Category-wise Sales:

• Copy previous pivot table
• Replace 'Sales Rep' with 'Category'

5. Monthly Sales in 2021:

• Copy previous pivot table
• Replace 'Category' with 'Date'
• Remove 'Date' and 'Day'

Naming Pivot Tables

• Use Pivot Table Analyze tab to rename:
  • 'City', 'Product', 'Sales Rep', 'Category', 'Months'

Creating Charts

1. Line Chart (Sales by City):

• Click inside City pivot table -> Insert -> Line Chart
• Hide field buttons, remove vertical axis labels, grid lines, legend
• Add and format data labels, chart title -> Move to 'Dashboard' worksheet

2. Column Chart (Product-wise Sales):

• Click inside Product pivot table -> Insert -> Column Chart
• Similar formatting adjustments as Line Chart
• Move to 'Dashboard' worksheet

3. Bar Chart (Sales by Sales Rep):

• Click inside Sales Rep pivot table -> Insert -> Bar Chart
• Similar formatting adjustments as Line Chart
• Move to 'Dashboard' worksheet

4. Pie Chart (Category-wise Sales):

• Click inside Category pivot table -> Insert -> Pie Chart
• Add category names to data labels
• Move to 'Dashboard' worksheet

Adding Slicer

• Insert Slicer for 'Months'
• Format slicer to show buttons in multiple columns
• Connect slicer to all pivot tables except 'Months'
• Demonstration of updating charts using slicer

Additional Formatting

• Change color schemes for charts
• Format data labels and axis labels
• Use Format Painter for consistency
• Optionally, format the slicer

Final Touch-ups

• Hide grid lines in 'Dashboard' worksheet
• Smooth line in Line Chart
• Reverse category order for months axis
• Video tutorial available for same report in Power BI

Conclusion

• Dashboard report helps in understanding and improving business metrics like sales performance, top products, etc.