Back to Index

Microsoft 365 Copilot in Excel

Demo Setup

Download the Excel sample document EV_Charger_Sales_Analysis_v1.xlsx. You will use this file to run the demo steps outlined in this guide.

Talking Points

Microsoft 365 Copilot in Excel turns anyone into a data analyst, enabling you to quickly understand key trends, performance metrics, and the factors driving your business or project outcomes.

Whether you’re working with sales data, financial reports, customer feedback, or operational metrics, Copilot helps you shape and analyze your information, providing insights from complex datasets across multiple sources.

With Copilot, you can easily extract meaningful insights to inform better decision-making, no matter the type of data or the complexity of the analysis.

Demo Steps

NOTE: Make sure to open the Excel file EV_Charger_Sales_Analysis_v1.xlsx before proceeding.

  1. Navigate to the “Sales by Product” tab in the Excel file.

  2. Use Copilot to Calculate Monthly Revenue:

    Let’s start by figuring out which category of your business is driving the most revenue. This sheet contains three years of sales data, with thousands of rows showing sales by product by month. Although it’s a routine task, this volume of data can be unwieldy. You can ask Copilot to quickly calculate the monthly revenue by product.

    Use the following prompt:

    Calculate monthly revenue by product and add a column with total revenue - refer to the Prices worksheet.
    • Copilot knows how to do that and which data to reference across tabs.
    • Copilot creates a plan for how it will run those numbers, executes that plan showing its work as it goes, and prompts you to ask questions or iterate on the solution it reached.
    • Click +Insert column, then navigate back to the Sales by Product tab.

  3. Use Copilot to analyze revenue by entering the following prompt in the Copilot pane:

     What is the total revenue for each category so far in 2024?
    • Copilot will run the numbers and create a bar chart that you can add to your workbook.
    • Click +Add to a new sheet, then navigate back to the Sales by Product tab.

  4. Now, use Copilot to highlight products with low sales by entering this prompt:

     Highlight rows where the value in column H is less than $100K.
    • Copilot will apply conditional formatting, helping you identify products that aren’t performing to your standards.

  5. Navigate to the “Reviews” tab to analyze customer feedback.

  6. Ask Copilot to summarize the top concerns by entering the following prompt:

     Summarize the top 3 customer concerns we should focus on.
    • Copilot will analyze the feedback and surface the top three customer concerns. It looks like charging speed is an emerging issue.

  7. Next, highlight reviews mentioning charging speed by entering this prompt:

     Highlight reviews that mention issues related to charging speeds.
    • Copilot will highlight all relevant reviews in the dataset.

Wrap-up

With the help of Copilot, you’ve analyzed complex data sets and gained insights on product performance and customer feedback. These insights can be used to inform your next business review.