Copilot TrainingCPS HR Training Center · Tue Oct 20, 2026
Your progress
0%

Lab 6 · Module 4 · Create and Calculate

Excel: Calculate, Explain, Chart

30 minSlide 25 3-question quiz

Overview

What we are doing

The situation
Each month the team tracks requests received, completed, and still open. Before the monthly review you need two new measures and a trend chart.
Your role
You are the analyst who prepares the monthly numbers.
Your task
Use Copilot in Excel to add two measures (new calculated columns), explain them, and chart a trend.
Files you need
File 06 (06_Service_Request_Data.xlsx).
Done when
You have two new columns that you checked by hand for two rows. You also have a line chart and a trend summary that does not claim any causes.
Why it matters
A formula Copilot writes can point at the wrong column and still look right. Two rows checked by hand catch that before the numbers go to a manager.
Good to know
Slide 24 shows three ways to work in Excel: Chat explains, Plan proposes steps, and Edit can change your workbook.

Check before you finish

  • Do your hand calculations match Copilot's?
  • Completion Rate formula: Completed / Received x 100.
  • First-Contact Resolution Share formula: Resolved on First Contact / Completed x 100.

How to do it: the basic steps

5 steps, one screen each. Select the ? on a card for why the step is needed and what each entry does.

  1. Get the sheet where Copilot can work (Open the data in Excel for the web)

    Why: Copilot in Excel needs the file in OneDrive and the data formatted as a table.

    1. Download 06_Service_Request_Data.xlsx
    2. Save to OneDrive
    3. Open in Excel (web)
    4. Select a cell in the data

    Download 06_Service_Request_Data.xlsx from the Files step, save it to OneDrive, and open it in Excel for the web with your work account. Use the .xlsx, not the .csv. Select any cell inside the data. If Copilot asks to convert the range to a table, select Convert (or Home, Format as Table).

  2. Let Copilot calculate, then prove two rows (Add two columns, then check two rows)

    Why: Copilot proposes a formula column. Checking two rows by hand is how you know the formula is right.

    1. Home
    2. Copilot
    3. Copy prompt (Prompt 1)
    4. Send
    5. Insert column

    On the Home tab select Copilot to open the pane. Select Show the prompts below, copy Prompt 1, paste, Send. When Copilot offers a column, select Insert column for each. Then check two rows with a calculator: Completion Rate = Completed / Received x 100; First-Contact Resolution Share = Resolved on First Contact / Completed x 100.

  3. Get an explanation you can test (Ask about trends)

    Why: Trend explanations are where tools overstate. The data has gaps; a good answer says so.

    1. Copy prompt (Prompt 2)
    2. Send
    3. Read for caveats

    Copy Prompt 2, paste, Send. Read the answer for words like steadily or consistently and check them against the months.

  4. Make the chart and put it on its own sheet (Chart days to resolve)

    Why: A chart is a claim. On its own sheet it is easy to check against the column it came from.

    1. Copy prompt (Prompt 3)
    2. Send
    3. Add to a new sheet

    Copy Prompt 3, paste, Send. When the chart appears, select Add to a new sheet. Compare its points with the Average Days to Resolve column.

  5. Decide what the numbers can carry (Check and record)

    Why: Two hand-checked rows and a read trend are the minimum before any of this goes in a report.

    1. Hand checks match?
    2. Five Checks

    Do your hand calculations match Copilot's? Run the Five Checks: Source, Verification, Context, Resilience, Authorized Owner. Then mark the lab steps done.

= why the step is needed and what each entry does. Like this = something you select or click. Like this = something you type or paste.

Microsoft changes these screens often; a label on your screen may differ slightly. If a button is missing, your organization may not have turned the feature on: use the Path B steps.

Where to click

  1. File 06 in Excel
  2. select a cell in the table
  3. Home
  4. Copilot

Watch where to click Illustration - not the actual Copilot screen

A simplified, labeled diagram of the clicks. It is not the actual Copilot screen. The step list below the player is the text version.

Press Play to watch where to click.

Do it: step by step

Check off each step as you go. Your progress saves in this browser.

Path B (Copilot Chat only)? These steps are for Path A. Use the Path B steps in the Files step instead.

Prompts: vague vs detailed

Follow the brackets. The detailed prompt is built from the four C.H.A.T. parts: [Context], [How come], [Assign task], and [Type of output]. Keep the labels when you paste it; Copilot reads them fine, and they help you check that each part is there.

Compare a vague prompt with a detailed, source-checkable prompt. The activity below separates the request from the Guardrails you need before use.

Quick start (optional, 1 minute): ask Copilot to explain a formula

Class prompt
[Context] I selected a cell in the Ending Backlog column of file 06.
[How come] I want to understand this formula before anything in the workbook changes.
[Assign task] Explain this formula. Explain only; do not change the workbook.
[Type of output] A short, plain-language explanation of what the formula calculates.

A one-minute first prompt before the three main prompts. It shows the three ways Copilot works in Excel: Chat explains, Plan proposes steps, and Edit changes the workbook. Optional, and not part of the done check.

Why the detailed version works (letter by letter)
  • CContext I selected a cell in the Ending Backlog column of file 06
  • HHow come I want to understand this formula before anything in the workbook changes
  • AAssign task Explain this formula
  • TType of output A short, plain-language explanation of what the formula calculates
  • GGuardrail: keep it off-limits Explain only; do not change the workbook

Use CHATPROMPT to shape the request: Context, How come (why you need it), Assign task, Type of output, Persona, References, Optimize, Modularize, Progression, and Transcendence (expert level: the next useful question after the checked answer). Add Guardrails separately for training-only input, source checking, limits, and the Authorized Owner.

Prompt 1 of 3

[Context] I am checking the monthly service request numbers in file 06, the training service-request spreadsheet I shared.
[How come] I need two new measures that I can check by hand.
[Assign task] Add a Completion Rate column (Requests Completed divided by Requests Received, times 100) and a First-Contact Resolution Share of Completed Requests column (Resolved on First Contact divided by Requests Completed, times 100). Round to one decimal place. If a needed value is blank, or a denominator is zero, leave the new cell blank.
[Type of output] Show the full table with the two new columns. Then write the Excel formula for each new column and explain each formula in plain language.

Why the detailed version works (letter by letter)
  • CContext I am checking the monthly service request numbers
  • HHow come I need two new measures that I can check by hand
  • AAssign task Add a Completion Rate column (Requests Completed divided by Requests Received, times 100)
  • TType of output explain each formula in plain language
  • GGuardrail: keep it off-limits If a needed value is blank, or a denominator is zero, leave the new cell blank

Use CHATPROMPT to shape the request, as described above the first prompt.

Prompt 2 of 3

[Context] Same chat as Prompt 1, using the training service-request spreadsheet I shared (file 06) and its two new columns.
[How come] I need a trend summary that does not claim any causes.
[Assign task] Identify trends and unusual values in Completion Rate and First-Contact Resolution Share.
[Type of output] Explain what each measure means, what it does not establish, and what additional information would be needed to evaluate the backlog. Do not infer causes without supporting evidence.

Why the detailed version works (letter by letter)
  • CContext Same chat as Prompt 1, using the training service-request spreadsheet
  • HHow come I need a trend summary that does not claim any causes
  • AAssign task Identify trends and unusual values in Completion Rate and First-Contact Resolution Share
  • TType of output Explain what each measure means, what it does not establish, and what additional information would be needed
  • GGuardrail: keep it off-limits Do not infer causes without supporting evidence

Use CHATPROMPT to shape the request, as described above the first prompt.

Prompt 3 of 3

[Context] Same chat as Prompt 2, using the service request data I shared from file 06.
[How come] I need to see the trend over time, not just the numbers.
[Assign task] Create a line chart of Avg Days to Resolve by month.
[Type of output] A line chart with one point for each month. If you cannot make a chart, give a month-by-month table instead.

Why the detailed version works (letter by letter)
  • CContext Same chat as Prompt 2, using the service request data I shared from file 06
  • HHow come I need to see the trend over time, not just the numbers
  • AAssign task Create a line chart of Avg Days to Resolve by month
  • TType of output A line chart with one point for each month

Use CHATPROMPT to shape the request, as described above the first prompt.

Optional challenge (only if time allows)

[Context] Same chat as Prompt 3, using file 06. Assume the beginning backlog is 248 requests (the September ending backlog in file 06), new requests are 10 percent higher than in September, and the completion rate stays at the September rate.
[How come] I want a what-if estimate to discuss, not a prediction.
[Assign task] Estimate the ending backlog using Ending Backlog = Beginning Backlog + New Requests − Completed Requests.
[Type of output] List every assumption and label the result a model, not a forecast.

Why the detailed version works (letter by letter)
  • CContext Assume the beginning backlog is 248 requests (the September ending backlog in file 06), new requests are 10 percent higher than in September
  • HHow come I want a what-if estimate to discuss, not a prediction
  • AAssign task Estimate the ending backlog using Ending Backlog = Beginning Backlog + New Requests − Completed Requests
  • GGuardrail: make it checkable List every assumption
  • GGuardrail: people decide label the result a model, not a forecast
Check your model (open after you try)

Beginning backlog: 248 (September Ending Backlog, file 06). New requests: 468 x 1.10 = 514.8. September completion rate: 452 / 468 = 96.6%. Completed: 514.8 x (452 / 468) = 497.2. Ending backlog: 248 + 514.8 - 497.2 = 265.6, about 266 requests. This is a model, not a forecast.

Quiz

1. Which formula gives the Completion Rate?
2. In June, 455 requests were completed and 390 received. What is the completion rate?
3. August has no first-contact data. What should its First-Contact Resolution Share cell show?

Self-check only. Answers stay on your device.

Path B Text Pack and downloads

Download the practice files (Path A)

Path A (full access): these downloads are for you. Save them where Copilot can reach them (OneDrive for the Office app labs). Path B: nothing to download; the files are below as text, ready to paste. Path C: nothing to download; read the text below when you check sources.

Excel06_Service_Request_Data.xlsx8 KBCSV06_Service_Request_Data.csv1 KB

Path B (Chat only): short steps

Do you need Excel or the .xlsx file on Path B? No. You paste file 06 as text into Copilot Chat and check the math with a calculator. Skip the warm-up prompt, because it needs Excel.

  1. Select Copy all files + Prompt 1 below, paste it into the chat, and select Send. It includes the Quick start and Prompt 1. No Excel needed: the table is pasted as text.
  2. Check two rows with a calculator. The two formulas are in Do it.
  3. Send Prompt 2 of 3 in the same chat. If time allows, send Prompt 3 of 3 and the Optional challenge.

These prompts work on both paths. They say "I shared", which fits whether you attach or paste the files. Only the warm-up has its own Path B version.

Prompt 1 of 3 (Path A & B)
[Context] I am checking the monthly service request numbers in file 06, the training service-request spreadsheet I shared.
[How come] I need two new measures that I can check by hand.
[Assign task] Add a Completion Rate column (Requests Completed divided by Requests Received, times 100) and a First-Contact Resolution Share of Completed Requests column (Resolved on First Contact divided by Requests Completed, times 100). Round to one decimal place. If a needed value is blank, or a denominator is zero, leave the new cell blank.
[Type of output] Show the full table with the two new columns. Then write the Excel formula for each new column and explain each formula in plain language.
Prompt 2 of 3 (Path A & B)
[Context] Same chat as Prompt 1, using the training service-request spreadsheet I shared (file 06) and its two new columns.
[How come] I need a trend summary that does not claim any causes.
[Assign task] Identify trends and unusual values in Completion Rate and First-Contact Resolution Share.
[Type of output] Explain what each measure means, what it does not establish, and what additional information would be needed to evaluate the backlog. Do not infer causes without supporting evidence.
Prompt 3 of 3 (Path A & B)
[Context] Same chat as Prompt 2, using the service request data I shared from file 06.
[How come] I need to see the trend over time, not just the numbers.
[Assign task] Create a line chart of Avg Days to Resolve by month.
[Type of output] A line chart with one point for each month. If you cannot make a chart, give a month-by-month table instead.
Optional challenge (Path A & B)
[Context] Same chat as Prompt 3, using file 06. Assume the beginning backlog is 248 requests (the September ending backlog in file 06), new requests are 10 percent higher than in September, and the completion rate stays at the September rate.
[How come] I want a what-if estimate to discuss, not a prediction.
[Assign task] Estimate the ending backlog using Ending Backlog = Beginning Backlog + New Requests − Completed Requests.
[Type of output] List every assumption and label the result a model, not a forecast.
Quick start (Path B)
[Context] I pasted the service request table from file 06. It has an Ending Backlog column.
[How come] I want to understand how Ending Backlog is calculated before I use it.
[Assign task] Explain how the Ending Backlog column is calculated from the other columns. Explain only; do not change the table.
[Type of output] A short, plain-language explanation of the calculation.
File 06 - Service Request Data (spreadsheet as text) (1,366 characters)
Download .txt
FICTIONAL TRAINING FILE - The City of Fernhollow, its staff, residents and data are invented for the CPS HR Microsoft 365 Copilot workshop. Do not use real work data.

FILE 06 - SERVICE REQUEST DATA (text version of the spreadsheet)
Sheet: Monthly Data (Excel table name: ServiceRequests)
Blank = no data; 0 = measured and zero. Ending Backlog = Beginning Backlog + Received - Completed.

Month | Beginning Backlog | Requests Received | Requests Completed | Resolved on First Contact | Avg Days to Resolve | Escalated | Ending Backlog | Notes
Jan 2026 | 120 | 410 | 395 | 150 | 6.1 | 12 | 135 | 
Feb 2026 | 135 | 398 | 380 | 141 | 6.3 | 9 | 153 | 
Mar 2026 | 153 | 445 | 412 | 160 | 6.8 | 0 | 186 | No escalations recorded (confirmed zero)
Apr 2026 | 186 | 460 | 418 | 155 | 7.4 | 14 | 228 | 
May 2026 | 228 | 472 | 430 | 149 | 8.0 | 18 | 270 | 
Jun 2026 | 270 | 390 | 455 | 170 | 8.4 | 21 | 205 | Focused effort to clear older requests
Jul 2026 | 205 | 430 | 418 | 162 | 7.6 | 15 | 217 | 
Aug 2026 | 217 | 455 | 440 |  | 7.2 |  | 232 | Case-system migration: first-contact and escalation data not captured (blank = no data)
Sep 2026 | 232 | 468 | 452 | 181 | 6.9 | 13 | 248 | 

Lab 6 measures:
Completion Rate = Requests Completed / Requests Received x 100
First-Contact Resolution Share of Completed Requests = Resolved on First Contact / Requests Completed x 100

Path C (Watch and verify): Calculate two rows of each measure by hand, then compare with the trainer's Copilot results.

Debrief question: Which month went over 100 percent, and what does the Beginning Backlog column show for that month? Remember: a blank and a zero mean different things.