Copilot 2

How to Use Excel Copilot to Understand Your Data

Welcome to the second post in the Copilot in Excel series. In this post, we’re diving into the world of Excel Copilot to help us understand and analyze our data. In this post, we’ll explore how Copilot can help us make sense of the data in our workbooks. Whether you’re new to Excel or just looking to work smarter, Copilot can assist. Let’s jump right in!

Video

Step-by-Step Guide

Excel Copilot is an AI-powered tool designed to enhance your experience with Excel by helping you create, understand, edit, and analyze your data more efficiently. By typing in simple prompts, you can ask Copilot to perform various tasks, from generating graphs and pivot tables to calculating complex metrics.

If you haven’t already, feel free to check out my Copilot 1 post with provides more general information about Copilot in Excel as well as availability, licensing information, and links.

Exercise 1: Analyzing Transaction Data

Let’s start with our first exercise. We have a table filled with transaction data, including columns for Transaction ID, Region, Representative, Item, and Amount.

Our goal is to understand this data better using Copilot.

We open the Copilot pane by clicking Home > Copilot.

We enter the prompt: “What is the total revenue for each region?”

Copilot results:

We click Add to a new sheet:

Copilot quickly inserts a PivotTable and graph into a new worksheet. This dual output is fantastic because it provides us with both a detailed table view and a visual representation of the data.

If you’re not familiar with PivotTables or charts, this feature is a game-changer. Copilot does the heavy lifting, allowing you to benefit from these powerful tools without needing to master them immediately. We can also modify the resulting PivotTable or chart as desired.

Exercise 2: Counting Employees by Department

Next, let’s move on to a different type of dataset—a list of employees, including their ID, name, address, department ID, and department name.

This time, we want to know how many employees work in each department.

Copilot prompt: “How many different employees are there in each department?”

Copilot results:

We click Add to a new sheet:

This provides a clear view of the distinct count of employees for each department, exactly as we wanted.

Exercise 3: Identifying Top-Selling Items

For our third exercise, we have a transactions table with columns for Item, Quantity, Price, and Cost.

Our goal is to identify the top-selling items based on sales quantity.

We asked Copilot: “What are the top three items in terms of sales quantity?”

Copilot returns:

We click Add to a new sheet:

After adding the result to a new sheet, we see a PivotTable filtered for the top three items, sorted in descending order by quantity.

This is exactly what we wanted! The process is straightforward, and the results are easy to interpret, making it simple to adjust or further analyze the data as needed.

This flexibility allows us to make adjustments based on our needs. For example, we could change the filter to show the top five items instead of the top three if desired.

Conclusion

And there you have it—a few ways that Copilot in Excel can help us quickly understand and analyze data in our workbooks. Whether you’re looking to create visualizations, perform calculations, or analyze data trends, Copilot is a fantastic tool to streamline your workflow and enhance your Excel experience.

If you have any suggestions, questions, or alternatives, please share in the comments below!

Sample File

You can download the sample file if desired.

FAQs

Q: What is Excel Copilot?
Excel Copilot is an AI-powered tool integrated into Excel to assist users with data analysis, visualization, and more by responding to natural language prompts.

Q: How do I access sample prompts in Copilot?
When using Copilot, you can find sample prompts under different categories like Create, Understand, Edit, and Ask. These samples help guide you in asking the right questions.

Q: Can Copilot create charts and graphs?
Yes! Copilot can generate charts and graphs based on your data, often by first creating a pivot table and then visualizing the data.

Q: Is Copilot useful for beginners?
Absolutely! Copilot is especially helpful for those who are new to Excel or unfamiliar with certain features like pivot tables or complex formulas.

Q: What happens if Copilot needs more information to perform a task?
If Copilot requires additional details or formulas to perform a task, it will prompt you to provide them, ensuring accurate and relevant results.

Q: Can I customize the outputs provided by Copilot?
Yes, all outputs generated by Copilot, including pivot tables and charts, can be customized further to meet your specific needs.

Q: Does Copilot replace the need to learn Excel skills?
Not necessarily. While Copilot makes it easier to perform tasks, understanding the fundamentals of Excel will help you make the most of this tool and enhance your overall productivity.