Lab 5 - Boost your productivity with data-driven decisions with Copilot in Excel

Imagine you’re a sales manager at Contoso. Your primary responsibility is to analyze sales data and identify trends that can help improve the company’s performance. In this hands-on Lab, you’ll use Copilot in Excel to explore and analyze various aspects of the sales data for Contoso’s Chai products.

You’ll start by getting an overview of the data and identifying key metrics. Next, you’ll analyze sales trends, compare product sales, and calculate total sales. Additionally, you’ll examine the relationship between social media engagement and chai sales, and identify any correlations between online searches and sales. Finally, you’ll generate insights from your analysis and summarize the key findings.

Sample file

Throughout this Lab, we’ll craft prompts for Microsoft 365 Copilot that reference this file. You should have already uploaded it to OneDrive during the lab setup process, but if you need to download it again, you can do so here:

Contoso Chai Tea market trends 2023.xlsx

Exercise 1 - Explore the data

To get an idea of market trends, you must first understand the overall performance of Contoso’s Chai products. Your first step is to get an overview of the data and identify key metrics that can guide your analysis.

  1. Open the sample file (Contoso Chai Tea market trends 2023.xlsx) you uploaded to your OneDrive.

  2. Select the Copilot icon on the far right of the Home tab.

  3. In the Copilot pane, enter the following prompt:

    +++Summarize the dataset and provide an overview of the key metrics.+++

  4. Select Send.

  5. Copilot responds with a detailed set of important takeaways, essentially an executive summary, of the data. It shows patterns and interpretations of the data, and recommended next steps. From this response, you can prompt Copilot to:

    +++Create a table showing the key patterns in the data.+++

  6. Select Send.

  7. Copilot creates a table containing an additional column that shows key patterns.

  8. Select + Add to new sheet to include this data in your Excel workbook.

  9. Review the table. When you finish, return to Sheet 1.

Exercise 2 - Identify sales trends

As a sales manager, you need to identify trends in the sales data to make informed decisions. Let’s look at the total chai sales over the year and look for any patterns or trends that can help improve sales strategies.

  1. Continue in the opened Copilot pane.

  2. Prompt Copilot with:

    +++Show a line chart of Total Chai Sales (units) over the months.+++

  3. Select Send.

  4. Review Copilot’s response, which includes both the chart and text summary, and if you want, add the line chart to a new sheet.

  5. If you added the line chart, review the chart then return to Sheet 1.

Exercise 3 - Compare product sales

To optimize your product offerings, you need to compare the sales of different chai products. Copilot can help you to easily compare the sales of Artisanal Chai and Premade Chai to determine which product category performed better overall.

  1. Continue in the opened Copilot pane.

  2. Prompt Copilot with:

    +++Create a bar chart comparing Artisanal Chai Sales (units) and Premade Chai Sales (units) for each month.+++

  3. Select Send.

  4. Copilot displays the bar chart. Note that Copilot also provides text insights in its response. Select Add to new sheet to include the chart in your Excel workbook.

  5. Once you’ve reviewed the bar chart results, return to Sheet 1.

  6. Summer months can see a wide variance of sales. To understand what type of tea is selling best, you can ask Copilot to determine which product category performed better overall by entering the following prompt:

    +++Summarize the total sales (units) for Artisanal Chai and Premade Chai over the summer.+++

  7. Select Send

  8. Review Copilot’s response.

Exercise 4 - Calculate total sales

Understanding the total sales is crucial for evaluating the success of your sales strategies. Let’s ask Copilot to calculate the total sales for each quarter by adding Artisanal Chai Sales and Premade Chai Sales.

  1. Continue in the opened Copilot pane.

  2. Prompt Copilot with:

    +++Calculate the total sales per quarter.+++

  3. Select Send.

  4. Select Add to new sheet.

  5. Review the total sales, then return to Sheet 1.

Exercise 5 - Generate insights

Finally, let’s summarize the key insights from your analysis. These insights help you make data-driven decisions to drive sales growth at Contoso.

  1. In the opened Copilot pane, enter the following prompt:

    +++Provide a summary of the key insights from the analysis of the Contoso Chai Tea market trends data.+++

  2. Select Send.

  3. Review Copilot’s response.

Exercise 6 - Send your insights to the team

Once you collect insights on market trends, you can share the information with your stakeholders. Here’s how Copilot in Outlook can help you:

  1. Copy the text response generated by Copilot in Excel by highlighting the text and selecting the Copy icon below the response.

  2. Open Microsoft Edge and navigate to +++https://outlook.office.com+++.

  3. Select New email.

  4. Paste the response into the email body.

  5. Select the area below the pasted content, then select the Open Copilot icon that appears.

  6. Enter the following prompt:

    +++Draft an email to my team summarizing the key points from our recent analysis on Contoso Chai Tea market trends.+++

  7. Select Generate.

  8. Review the draft provided by Copilot and select Replace to include the content in your email.

When working in your own environment, you would then send the email to your stakeholders.

You now have hands-on experience using Copilot in Excel to analyze market trends, identify patterns, and generate valuable insights from your data. Continue trying new prompts in your Excel files to gain more practice.

End of Lab