Exercise: Analyze a financial spreadsheet using Copilot in Excel

Completed

For Finance professionals, Copilot in Excel offers numerous benefits, such as the ability to ask questions about your data set in natural language rather than just formulas. The tool can reveal correlations, suggest what-if scenarios, and create powerful visualizations based on your queries. For example, you can use Copilot in Excel to break down sales data by type and channel. Or you can project the impact of a variable change in your data and then generate a chart to help visualize it. You can also model how a change to the growth rate for a variable would impact gross margin.

The bottom line is that by automating repetitive tasks, Copilot in Excel unlocks more creative and innovative approaches within the workforce, accelerating progress in various business lines. It’s a valuable tool for Finance professionals who want to streamline their daily processes and make more informed decisions.

When you use Copilot in Excel, you must have an Excel table that includes data in a worksheet. You can quickly turn a range of cells into an Excel table by following these steps:

  1. Select the cell or the range in the data.
  2. Select Home > Format as Table.
  3. In the Format as Table dialog box, select the checkbox next to My table has headers if you want the first row of the range to be the header row.
  4. Select OK.

In this exercise, you'll use Copilot in Excel to analyze a market trend spreadsheet that's already in an Excel table. This exercise examines many of Copilot's pre-built functions and prompts.

Exercise

As Fabrikam's Director of Finance, you want to analyze how effective the company's Q1 marketing campaigns were. Your Director of Marketing provided you with a spreadsheet that identifies each of the marketing campaigns the company engaged in during the first quarter. The spreadsheet provides basic budget and revenue figures and the number of targeted and engaged users. It’s now your job to analyze the numbers to determine the effectiveness of each campaign type.

Perform the following steps to use Copilot in Excel to analyze the data that appears in the Q1 marketing campaigns spreadsheet:

  1. Select the following link to download the Fabrikam Q1 marketing campaigns spreadsheet.

  2. After the download completes, move the file to your OneDrive account, and then open and close the file to get it in your Most Recently Used (MRU) file list.

  3. If you have a Microsoft 365 tab open in your Microsoft Edge browser, then select it now; otherwise, open a new tab and enter the following URL: https://www.office.com

  4. On the Microsoft 365 home page, select the Excel icon in the navigation pane on the left.

  5. In Excel, on the File page, select Fabrikam Q1 marketing campaigns from the file list.

  6. Select the Copilot option on the right side of the ribbon.

  7. In the Copilot pane that appears, note the predefined prompts that are displayed above the prompt field. Before you select any of these predefined prompts to improve your analysis, you first want Copilot to make some specific changes to the spreadsheet. To begin with, you want Copilot to identify which campaign types are most profitable. To do so, enter the following prompt:

    Create a pivot table to analyze the total revenue generated by each campaign type.

  8. Review the results of this prompt. Copilot displayed two response windows. The first response included a pivot table that summarized the total revenue by campaign type. The second response included an explanation for what it did in the first response. In the first response containing this table, select the +Add to a new sheet button. Doing so adds this table to Sheet 2 of this spreadsheet, which Copilot then opened for you.

  9. In looking at Sheet 2, you notice that Copilot didn't create a chart to accompany the pivot table. In looking at the prompt you submitted, you realize that you just asked it to create a pivot table - you never mentioned anything about a chart. You would like to see a visualization of this data, so you want to enter a prompt asking Copilot to generate a chart to go along with the pivot table in Sheet 2. However, notice that while you're in Sheet 2, the prompt field is disabled.

    Note

    The prompting field is only enabled in the sheet that has the Excel table. For this spreadsheet, that's Sheet 1. As you continue through this exercise, each time Copilot adds data to a new sheet, you must return to Sheet 1 to request more changes.

  10. Select Sheet 1, and then enter the following prompt:

    In Sheet 2, you created a pivot table to analyze the total revenue generated by each campaign type. Create a chart in Sheet 2 to visualize this data.

  11. Review the result. If Copilot created a chart showing the revenue by campaign type, then proceed to the next step. However, if Copilot displayed a message indicating that you can't ask it to work on a sheet other than the original sheet that has the Excel table, then you must simplify your previous prompt. In this case, enter the following prompt:

    Calculate the total revenue generated by each campaign type.

  12. Review the chart that Copilot created showing the revenue by campaign type. This result is what you wanted, so select the +Add to a new sheet button at the bottom of the window. Doing so adds this chart to Sheet 3 of this spreadsheet.

  13. In looking at Sheet 3, you note how Copilot included the pivot table along with the chart. You realize that Sheet 2 has the same pivot table, while Sheet 3 has the table and chart. Since you want Sheet 3 that has both the table and chart, you decide to remove Sheet 2 to avoid any future confusion. To delete Sheet 2, right-click on it, select Delete from the menu that appears, and then select OK to confirm the deletion. Doing so leaves you with Sheets 1 and 3.

  14. Since you want to make more changes, select Sheet 1 to return back to your sheet with the pivot table.

  15. You now want to identify which campaigns were the most effective. To do so, you want Copilot to calculate the Return on Investment (ROI) for each campaign. To calculate the ROI, enter the following prompt:

    Calculate the ROI for each campaign.

  16. Review the results of this prompt. Copilot shows you the calculation, and you can select the Explain formula option for an explanation of the ROI calculation. You want Copilot to add the ROI to your spreadsheet, so select the +Insert column button that appears at the bottom of the window.

  17. Note the results. Copilot added a new column containing the ROI for each individual campaign. While that's fine, you want it to determine the ROI for each campaign type. You realized your mistake when you reviewed your prior prompt. You asked Copilot to calculate the ROI for each campaign, when in fact, you wanted to calculate the ROI for each campaign type. Enter the following prompt to calculate the ROI for each campaign type:

    That change looks good. However, I would like you to also calculate the ROI for each campaign type.

  18. Review the results. Copilot created a graph showing the ROI by campaign type. Select the option at the bottom of the window to +Add to a new sheet. Doing so adds this table to a new Sheet 2 of this spreadsheet. In addition to the clustered bar chart that it created (hover your cursor over the chart to see the chart type), it also created a pivot table containing the ROI by campaign type. After you finish reviewing this data, select Sheet 1.

  19. You now want Copilot to determine which campaigns were most effective at engaging users. You feel the best way to visualize this data is to have Copilot create a chart that shows the relationship between total users targeted and total users engaged. To do so, enter the following prompt:

    Create a chart that shows which campaign was most effective at engaging users.

  20. Review the results. Copilot created an Engaged Users by Campaign Name bar chart. However, if Copilot just summarized the total engaged users by campaign, that's not exactly what you wanted. To correct this result, enter the following prompt:

    That type of chart isn't what I was looking for. Please create a chart that shows the relationship between total users targeted and total users engaged.

  21. Review the results. In our testing, Copilot indicated that it couldn't create a scatter chart with this data. However, it did explain the pivot table that it created. You feel that result is good enough for now, so select the +Add to a new sheet button at the bottom of the pivot table window. Doing so adds this table to Sheet 4 of this spreadsheet. When you're done reviewing this data, select Sheet 1.

  22. You have one final piece of data that you want Copilot to provide. You would like Copilot to identify the top-performing campaigns based on revenue generated. You can then use this information to identify which campaigns are most profitable. Enter the following prompt:

    Identify the top-performing campaigns based on revenue generated.

  23. Review the results. Copilot created a graph showing the Revenue by campaign name. Select the option at the bottom of the window to +Add to a new sheet. Doing so adds this table to Sheet 5 of this spreadsheet. In addition to the clustered bar chart that it created, it also created a pivot table displaying the total revenue by campaign. When you're done reviewing this data, select Sheet 1.

  24. That completes the data that you wanted Copilot to analyze for you. However, you're curious as to what other suggestions Copilot may have to further analyze your campaign data. In the list of predefined prompts that appear above the prompt field, look for a suggestion titled Show suggestions for formula columns. If you see this suggestion, then select it now. However, if you don't see this suggestion, then enter it manually in the prompt field.

  25. Review the column suggestion. If you want to add it to your spreadsheet, select the +Insert column button. If a predefined prompt appears that says Give me another suggestion, then select it now. However, if this predefined prompt doesn't appear, then enter it manually in the prompt field (in our testing, this predefined prompt sometimes appeared before inserting the column, but then disappeared after the column was inserted).

  26. Repeat the previous step a few times to have Copilot suggest different calculations. Insert the ones you like and skip the ones you don't.

  27. You now want Copilot to show you some more charts that visualize various types of data. In the list of predefined prompts, select the Show data insights button.

  28. Review the results. If this chart is of interest to you, select the +Add to a new sheet button and then return to Sheet 1.

  29. Then select the Can I see another insight? button. Again, if this chart is of interest to you, select the +Add to a new sheet button and then return to Sheet 1.

  30. After seeing these first two charts, you realize that you like the various charts that Copilot provides. Instead of looking through more charts one by one, you want Copilot to add to your spreadsheet all the insights that it can create based on your data. In the predefined prompts, select the Add all insights to grid prompt.

  31. Review the results. Copilot created multiple charts in the final sheet, along with several pivot tables. You determine that your analysis is complete given all the data that Copilot in Excel has provided. Since Excel automatically saved your file, close your Microsoft Edge browser tab.