09 — Copilot in Excel: Prompts

Use these prompts to analyse, transform, and visualise your AI Usage Guide quiz response data.

Session guide: All exercises use the same workbook. Open AI_Guideline_Survey_Responses.xlsx in Excel desktop, enable Allow editing in the Copilot panel, and work through Exercises 1 to 4 in order. Switch to Claude Opus for best results on analytical tasks.

Before you start: Click on the Quiz Responses sheet and make sure the data is formatted as an Excel Table. If it is not, select any cell in the data and press Ctrl+T to convert it.


Exercise 1 — Generate insights and an insights dashboard

**Mode: Allow editing Recommended model: Claude Opus**

This is the main exercise. You are asking Copilot to analyse the entire dataset and produce a summary dashboard in a new sheet.

Part 1A: Generate the insights dashboard

Analyse this workbook to uncover meaningful and interesting 
data insights. Include the 3 most important insights, 
findings, or takeaways, including a visualisation to help 
illustrate or clarify a finding. Next, ask me questions to 
clarify what I am interested in learning or suggest steps 
for deeper analysis. Insert the summary in a new sheet.

This is the same prompt shown in the sample dashboard screenshot. Copilot will reason through the data and build a new sheet with KPIs, tables, and a chart. It may take 30 to 60 seconds.

Part 1B: Follow-up questions after the dashboard is created

What is the overall pass rate and average score across 
all 100 respondents? Show the results as KPI cells at 
the top of the dashboard.
Which department has the highest pass rate and which 
has the lowest? What is the gap between them?
Which 3 questions had the lowest accuracy rates across 
all respondents? What do these questions have in common?
Add a bar chart comparing average score by department. 
Sort the bars from highest to lowest average score.

Part 1C: Improve the dashboard

The dashboard needs a title. Add a clear title row at 
the top that says "AI Usage Guide Quiz — Key Insights 
Dashboard" and a subtitle that says "Analysis of 100 
respondents across 8 departments — 20 questions on AI 
policy awareness".
Colour-code the pass rate column in the department table: 
green for 75% and above, amber for 60% to 74%, and red 
for below 60%.

Exercise 2 — Data transformation and cleaning

**Mode: Allow editing Sheet: Quiz Responses**

This exercise adds calculated columns to the raw data so it is easier to analyse.

Part 2A: Add a Score column

Add a new column called "Score" after the Department 
column. For each row, count how many of the 20 question 
columns contain the correct answer.

The correct answers are in the Answer Key sheet. 
Use a formula that counts matching answers across 
columns G to Z for each row.

If Copilot needs guidance on the correct answers:

The correct answers for each question are:
Q1: To enable safe and consistent adoption of AI tools
Q2: Microsoft 365 Copilot
Q3: Personal data, IC numbers, and confidential business information
Q4: The employee who used the AI tool
Q5: Review and verify it before use
Q6: Entering customer personal data into the tool
Q7: Stop and seek guidance from your manager or IT
Q8: Review, test, and get approval before use
Q9: AI output must always be reviewed before use
Q10: Annually
Q11: IT Governance department
Q12: Report to IT Governance and line manager
Q13: Entering personal data of employees into AI tools
Q14: Verify them against the original source
Q15: All permanent, contract, and temporary staff
Q16: Ensure it has been reviewed and approved by a responsible person
Q17: Data privacy and security risks
Q18: Discard the output and redo the task manually or with guidance
Q19: Automatically making decisions based on AI recommendations
Q20: Use AI to assist, then verify findings before acting

Add a Score column that counts how many of these correct 
answers each respondent gave.

Part 2B: Add a Pass/Fail column

Add a column called "Pass/Fail" next to the Score column. 
Mark each respondent as "Pass" if their score is 14 or 
above (70%), and "Fail" if it is below 14.

Part 2C: Add a Percentage column

Add a column called "Score %" next to Pass/Fail. 
Calculate each respondent's score as a percentage of 20. 
Format the column as a percentage with one decimal place.

Part 2D: Add a Completion Time column

Add a column called "Duration (mins)" that calculates 
how long each respondent took to complete the quiz. 
Use the Start time and Completion time columns. 
Round to one decimal place.

Part 2E: Check for data issues

Review the data in this sheet for any issues: 
missing values, duplicate entries, inconsistent 
department names, or any outliers in completion time. 
Report what you find without making changes yet.

Exercise 3 — Pattern analysis and weak area identification

**Mode: Allow editing or Chat only Sheet: Quiz Responses or Summary**

Part 3A: Department comparison

Compare quiz performance across all 8 departments. 
For each department show: number of respondents, 
average score out of 20, pass rate as a percentage, 
and rank from highest to lowest pass rate. 
Create this as a formatted table in a new sheet 
called "Department Analysis".
Which departments should be prioritised for additional 
AI training based on these results? Justify your 
recommendation with specific data points.

Part 3B: Question weakness analysis

For each of the 20 questions, calculate how many 
respondents answered correctly and express it as 
a percentage. Identify the 5 questions with the 
lowest accuracy rates. What topics do these questions 
cover and what does this suggest about gaps in 
staff understanding?
For the 3 weakest questions, suggest one training 
intervention for each that could improve staff 
understanding of that specific policy point.

Part 3C: Cross-analysis

Is there a correlation between how long respondents 
took to complete the quiz and their score? Do faster 
respondents score higher or lower on average?
Which department had the most consistent scores 
(lowest variation between respondents) and which 
had the most inconsistent? What might explain 
the difference?

Exercise 4 — Python in Excel

**Mode: Allow editing Ask for Python in your prompt**

Before starting: There is no separate Python Tool toggle any more — keep the prompts below, which ask Copilot to use Python explicitly.

Part 4A: Score distribution chart

Use Python to create a histogram showing the 
distribution of quiz scores across all 100 respondents. 
Use the Score column. 
Include: a title "Score Distribution — AI Usage Guide Quiz", 
x-axis label "Score out of 20", y-axis label "Number of Respondents", 
and colour the bars using a teal colour scheme. 
Insert the chart into the workbook.

Part 4B: Department performance heatmap

Use Python to create a heatmap showing question 
accuracy by department. Rows should be departments, 
columns should be question numbers 1 to 20. 
Colour cells green for high accuracy and red for 
low accuracy. This will show which departments 
struggle with which specific questions. 
Insert the chart into the workbook.

Part 4C: Pass/Fail breakdown chart

Use Python to create a stacked bar chart showing 
Pass and Fail counts for each department side by side. 
Use green for Pass and red for Fail. 
Add data labels showing the count in each bar segment. 
Title: "Pass vs Fail by Department". 
Insert the chart into the workbook.

Part 4D: Explain the Python code

Show me the Python code you used to generate the 
score distribution histogram. Explain each part 
of the code in plain language so I can understand 
what it does and modify it if needed.

Exercise 5 — Management summary

**Mode: Allow editing Recommended model: Claude Opus**

Once your analysis is complete, ask Copilot to write a summary suitable for management.

Based on all the analysis in this workbook, write a 
management summary of the quiz results. Include:
- Overall pass rate and average score
- Top performing department and why it stands out
- Department that needs the most support and a 
  specific recommendation
- The 3 questions with the lowest accuracy rates 
  and what training gap they reveal
- One recommended next step for the organisation

Write it in a professional tone suitable for a 
department head report. Keep it under 200 words. 
Insert it as a formatted text block in a new sheet 
called "Management Summary".