Assignment -32 | Customer Satisfaction Dashboard

Excepted timing : 10 minutes

Sample Data

| Customer ID | Satisfaction Score | Feedback | Region   |

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

| 1           | 4                  | Good     | North    |

| 2           | 5                  | Excellent| South    |

| 3           | 3                  | Average  | East     |

| 1           | 4                  | Good     | North    |

| 2           | 5                  | Excellent| South    |

| 3           | 4                  | Average  | East     |

| 4           | 4                  | Good     | North    |

| 5           | 5                  | Excellent| North    |

| 6           | 5                  | Good     | East     |


Task :
  1. Create a PivotTable to summarize average satisfaction scores by Region.
  2. Insert a PivotChart to visualize satisfaction scores using a clustered column chart.
  3. Use conditional formatting to highlight feedback comments containing "Excellent".
  4. Create a donut chart to show the percentage distribution of satisfaction scores (1-5).
  5. Insert a timeline slicer to analyze satisfaction scores over different time periods.

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

Steps to Perform:

  1. Create a PivotTable to Summarize Average Satisfaction Scores by Region:
    • Select the sample data including Customer ID, Satisfaction Score, Feedback, and Region.
    • Go to Insert > PivotTable > PivotTable.
    • Drag Region to Rows and Satisfaction Score to Values, then set it to average.
  2. Insert a PivotChart to Visualize Satisfaction Scores Using Clustered Column Chart:
    • With the PivotTable selected, go to Insert > PivotChart > Clustered Column Chart to create the chart.
    • Customize the chart with Region on the x-axis and Average of Satisfaction Score on the y-axis.
  3. Use Conditional Formatting to Highlight Feedback Comments Containing "Excellent":
    • Select the Feedback column.
    • Go to Home > Conditional Formatting > New Rule > Format cells that contain > Specific Text > containing "Excellent".
    • Choose a formatting style to highlight cells with "Excellent" feedback.
  4. Create a Donut Chart to Show Percentage Distribution of Satisfaction Scores:
    • Calculate the percentage distribution of Satisfaction Scores (1-5) using formulas or PivotTable.
    • Go to Insert > Pie Chart > Doughnut to create the chart.
    • Customize the chart to show the percentage distribution of satisfaction scores.
  5. Insert a Timeline Slicer to Analyze Satisfaction Scores Over Time:
    • Ensure your data includes a date or period column (not provided in sample data).
    • Select any cell within your data range.
    • Go to Insert > Slicer and choose the date or period field to create the timeline slicer.
    • Use the timeline slicer to filter and analyze satisfaction scores over different time periods.

Detailed Steps: