Exercise 1, Task 3: Use Copilot in Excel to analyze communication metrics
As the Communications Manager at Boulder Innovations, part of your role is to assess how internal messages are resonating across the organization. One of your key responsibilities is managing the company’s quarterly newsletter. Boulder’s Senior Leadership Team (SLT) asked you for a quick overview of how different departments are engaging with it—specifically looking at open rates and click-through rates over the past quarter.
Open rate. Indicates whether people are seeing the newsletter. It tells you how many people opened your email out of the total number it was sent to. For example, if you send the newsletter to 100 people and 60 of them open it, your open rate is 60%. This rate helps indicate whether your subject lines or sender name are compelling enough for people to actually open the email.
Click-through rate. Indicates whether people find the email interesting enough to select what’s inside (such as articles, videos, or announcements). For example, if 60 people opened the email and 15 selected a link inside it, your click-through rate is 25% (15 out of 60). This rate tells you if people are engaging with the content rather than just opening the email.
In this exercise, you plan to use Copilot in Excel to analyze the Boulder Q4 Newsletter Click Rates.xlsx spreadsheet that breaks down the company’s Q4 newsletter engagement by department. Your goal is to generate a clear, high-level summary that highlights trends, identifies which departments are most and least engaged, and offers insights that could inform future communication strategies. Rather than manually calculating averages or scanning the table yourself, you’re going to use Copilot to instantly surface trends. Doing so saves time and helps you quickly understand which departments might need more support or different messaging strategies.
Using Copilot in Excel
Excel provides two ways to use Copilot: standard Copilot prompts for asking questions and getting insights about the data in the workbook, and Edit with Copilot in the Copilot pane for making direct, in‑place changes to worksheets, tables, and formulas.
You should use Copilot’s standard prompts in Excel for quick questions, simple summaries, or one‑off insights about the data you’re already viewing. When using the Copilot pane, if you enter a prompt without selecting Edit with Copilot, Copilot responds in a chat‑style mode that generates suggestions or content separately, rather than making direct, in‑place changes to the workbook.
You should use Edit with Copilot when you want Copilot to work directly with the worksheet—such as cleaning data, adding formulas, restructuring tables, or making iterative, in‑place changes. Edit with Copilot is designed for hands‑on data work, so it understands the structure of the sheet and can apply changes directly, rather than just describing what you could do.
In summary, use chat‑style Copilot for thinking and generating ideas; use Edit with Copilot for hands‑on editing inside the file. Edit with Copilot proposes specific changes (formulas, columns, cleanup steps) and, once you confirm, it applies those changes directly to the worksheet rather than expecting the user to explicitly apply them through copy and paste.
This task uses the Edit with Copilot functionality.
In addition, Copilot for Excel provides a response control selector that lets you choose which AI model Copilot uses to work with your workbook. You can leave this set to Auto (the default option) and let Copilot select a model for you, or choose a specific model when you want to influence how Copilot approaches the task.
If you’ve used Copilot Chat, you know that it also includes a response control selector. However, its options are different from the Excel selector. In Copilot Chat, the selector controls how deeply Copilot reasons about your request. In Excel, the selector controls which AI model performs the work. Although these selectors might appear to be similar, they control different aspects of Copilot and aren't the same setting.
This task uses the default Auto selector mode.
Perform the following steps to complete this task:
Select the following link to download the Boulder Q4 Newsletter Click Rates.xlsx file. Once the download is complete, store the file on your OneDrive.
In your Microsoft Edge browser, go to the Microsoft 365 home page.
On the Microsoft 365 home page, select Apps in the navigation pane, and then select Excel from the Apps menu.
In Excel for the web, select the Upload a file button, navigate to your OneDrive, and then select the Boulder Q4 Newsletter Click Rates file.
On the Home tab ribbon, select Copilot. In the Copilot pane, leave the response mode selector set to Auto. Then verify the Edit with Copilot icon appears in the prompt field next to the plus (+) sign. If you don’t see it, select the plus sign and then select Edit with Copilot in the drop-down menu. The icon should now appear in the prompt field.
You want Copilot to look for patterns and outliers in the data and to compare each department's performance to the overall average. So instead of using the predefined prompts that appear at the top of the Copilot pane, you decide to enter your own custom prompt to gather this information.
In the Copilot pane, ask Copilot to summarize trends in the open spreadsheet and identify any departments with below-average engagement. Ask it to display the results in a new sheet.Review the results of this prompt and then return to Sheet1. You now want Copilot to add a visualization of the click rates. Ask Copilot to add a bar chart that compares open and click-through rates by department. Ask it to display the results in a new sheet.
Review the chart that Copilot generated and then return to Sheet1. You now want Copilot to refine your analysis by focusing on the departments that are doing especially well or struggling. Recognizing top performers can help you replicate success, while identifying low performers gives you a clear starting point for improvement.
Ask Copilot to rank the departments based on their click-through rate and then highlight the top three and bottom three departments. Ask it to display the results in a new sheet.Review the response and then return to Sheet1. Since you must report your findings to the senior leadership team, you want Copilot to generate a short summary of the data that was captured in this spreadsheet.
Ask Copilot to create a short summary for the executive leadership team regarding overall departmental engagement. Indicate the average open rate across all departments, the average click-through rate, and identify the departments with below-average engagements. Ask Copilot to also provide some suggestions on how we can improve open and click-through rates for low-performing departments. Ask it to display the results in a new sheet.Review the response. This step turns insights into action, helping you improve future newsletter performance and tailor communication to meet employees where they are. You can’t think of anything else to add to the brief, but you want Copilot to weigh in.
Ask Copilot if there’s anything else it can think of to add to the brief to improve it for executive leadership. Ask it to prioritize each suggestion as either High, Medium, or Low. Ask it to suggest changes that you can review so that you can determine which ones you want to manually insert into the brief.Review Copilot’s suggestions. Ask Copilot to add the High and Medium priority items into a new sheet.
You determine that your analysis is complete given all the data that Copilot in Excel provided. Since Excel automatically saved your file, close this tab in your Microsoft Edge browser.