Assignment -35 | Sales Performance Dashboard

Excepted timing : 10 minutes

Sample Data

| Salesperson   | Jan Sales | Feb Sales | Mar Sales |

|---------------|-----------|-----------|-----------|

| John Doe      | 1000      | 1200      | 800       |

| Jane Smith    | 1500      | 900       | 1100      |

| David Brown   | 8100      | 700       | 600       |

| John Doe      | 1000      | 1200      | 800       |

| Jane Smith    | 1500      | 900       | 1100      |

| Davd Brown    | 8100      | 700       | 600       |

| John Doe      | 1000      | 1200      | 800       |

| Jane Smith    | 1500      | 900       | 1100      |

| David Brown   | 800       | 700       | 600       |

| John Doe      | 1000      | 1200      | 800       |

| Jay   Smith   | 2500      | 900       | 1100      |

| Davd   Brown  | 8020      | 700       | 600       |

 


Task :
  1. Create a PivotTable to summarize total sales by Salesperson and month.
  2. Use conditional formatting to highlight top sales performers each month.
  3. Create a clustered column chart to compare sales performance across all three months.
  4. Insert a slicer to filter data by Salesperson and update all charts accordingly.
  5. Use data bars in the PivotTable to visually represent the magnitude of sales for each Salesperson.

To perform the tasks outlined in Assignment  using Excel, follow these step-by-step instructions:

Steps to Perform:

  1. Create a PivotTable to Summarize Total Sales by Salesperson and Month:
    • Select the sample data including Salesperson, Jan Sales, Feb Sales, and Mar Sales.
    • Go to Insert > PivotTable > PivotTable to create the PivotTable.
    • Drag Salesperson to Rows and Jan Sales, Feb Sales, Mar Sales to Values area, summarizing as Sum.
  2. Use Conditional Formatting to Highlight Top Sales Performers Each Month:
    • Select the range of Jan Sales, Feb Sales, and Mar Sales columns.
    • Go to Home > Conditional Formatting > Top/Bottom Rules > Top 1 Items.
    • Choose a formatting style to highlight the top sales performer (or adjust the number as needed).
  3. Create a Clustered Column Chart to Compare Sales Performance Across Months:
    • Select the data range including Salesperson and the sales data for each month.
    • Go to Insert > Column Chart > Clustered Column Chart to create the chart.
    • Customize the chart with Salesperson on the x-axis and sales values on the y-axis, clustered by month.
  4. Insert a Slicer to Filter Data by Salesperson and Update All Charts:
    • Ensure your PivotTable and charts are selected.
    • Go to Insert > Slicer > Salesperson to create the slicer.
    • Use the slicer to filter data by Salesperson and observe how all linked charts update accordingly.
  5. Use Data Bars in the PivotTable to Visually Represent Sales Magnitude:
    • Click on any cell within the Values area of the PivotTable.
    • Go to Home > Conditional Formatting > Data Bars > choose a color/style.
    • Data bars will visually represent the magnitude of sales for each Salesperson across the months.

Detailed Steps: