How to use Microsoft Copilot in Excel: data analysis made easy

However, making sense of complex datasets in Excel doesn’t have to be a chore. With Microsoft Copilot, Excel users now have access to an AI-powered assistant that can make data analysis a breeze, whether you’re new to Excel or a seasoned pro.

In this blog, we’re taking you through the basics of how to use Microsoft Copilot in Excel, as well as providing some useful tips for making the most of it in data analysis.

What is Microsoft Copilot?

In case you’re not up to speed, let’s talk about what Copilot actually is. Microsoft Copilot is an AI-driven assistant integrated into Microsoft 365 applications, including Excel.

With Copilot, you can ask questions in plain English and get instant answers, formulas, and even visualisations – no more memorising formulas or googling how to create that one specific chart.

Getting started with Copilot in Excel

Before diving into how to use Microsoft Copilot for data analysis, let’s cover how to get Copilot set up in Excel in the first place!

First of all, you need to ensure you have access. Microsoft Copilot is available to Microsoft 365 users, so you’ll need an active subscription to access it.

Once you have access, it’s time to launch Copilot in Excel. First, open Excel, then look for the Copilot icon in the right-hand corner of the top ribbon:

Before diving into how to use Microsoft Copilot for data analysis, let's cover how to get started.

Click that icon and you can start typing commands and asking questions, directly to Copilot!

6 ways Copilot can help with data analysis in Microsoft Excel

Now you’ve got Copilot up and running, data analysis in Excel is about to get a whole lot easier! Copilot can do much more than help with basic spreadsheet jobs. It can assist you with intricate data analysis and visualisation tasks, giving you insights faster and easier than ever before.

Here are some of the different ways you can use Copilot in Excel to help with data analysis:

1. Automated data cleaning

Data cleaning is often one of the most time-consuming tasks in data analysis, but with Copilot, it’s quick and painless. It can help you automate parts of the process by removing duplicates, filling in missing data, and correcting formatting inconsistencies.

Try commands like “Remove duplicate entries in column A” or “Find and replace errors in the dataset” to completely automate certain parts of your data analysis.

2. Visualising data

We all know that visuals are easier to digest than a sea of numbers and Copilot can help with this – by creating charts and graphs from data instantly.

Try asking Copilot to “Create a bar chart showing monthly sales performance” or “Generate a pie chart to show the distribution of customers by region” to instantly transform your data into easy-to-digest visuals.

3. Forecasting and making predictions

Want to see into the future? Copilot can help…well, sort of! It can use your data to create forecasts and make predictions. Example prompts include: “Forecast sales for the next quarter based on this data.” “Analyse seasonal trends and predict future performance.” Copilot will use historical data patterns to offer forecasts, helping you make more informed business decisions.

4. Simplifying complex formulas

Memorising Excel formulas can feel like learning a whole new language, but Copilot simplifies this process. Instead of struggling with syntax, you can simply tell Copilot what you need, and it’ll generate the formula for you.

For example: “Calculate the average revenue for the last 12 months.” “Apply a VLOOKUP to match data from two different tables.” Copilot will write the formula and apply it for you, letting you sit back and take all the credit.

5. Quick summaries of complex data

Have you ever been given a massive spreadsheet and thought – Where do I even start?

Copilot can help by giving you a quick rundown of the data if you ask it to “Summarise the key trends in this dataset” or “Explain the relationship between column B and column C,” for example. The AI will then sift through the numbers and give you easy-to-understand explanations.

6. Create custom reports

After you’ve wrapped up your analysis, you’re probably going to need a report. Instead of spending hours manually writing it up, use Copilot to automatically create a report that summarises the data you need and includes visualisations like charts and tables.

Using the =Copilot function in Microsoft Excel

The =COPILOT puts AI-powered insights, summaries, and content generation capabilities directly into your spreadsheet. It allows you to type natural language prompts directly in a cell.

Using =Copilot is as simple as one, two, three:

1. Type your question or command: Use the syntax =COPILOT (prompt, [data_reference])
2. Include an option data reference: This is needed if you want Copilot to work its magic on a specific set of cells. If you don’t, you can leave it out entirely.
3. Copilot does the legwork: The AI will run the prompt, generate a response, and place it directly into the highlighted cells.

You can use this function for a range of data analysis tasks, like summarising trends, classifying information, and highlighting patterns in data. You can also use it to create formulas or provide dynamic outputs (where the results update automatically as your data changes).

Best practices for using Copilot in Excel

Creating an effective brief (or “prompt”) for AI is an art form in itself. A brief that includes knowledge sources, examples of output, and references will result in a higher quality output than one that doesn’t.

With that in mind, here are some tips for prompting Copilot effectively:

• Be specific: When you give Copilot instructions, try to be as clear as possible. For example, instead of saying, “Show sales,” go for “Show Q2 sales by product category.”

• Review the output thoroughly: Copilot is smart, but it’s always a good idea to double-check its outputs, especially if you’re dealing with critical data.

• Use Copilot to fill your gaps: It’s better to use Copilot to complete tasks that are time-consuming or that you’re less capable of delivering than to ask it to complete a function you’re already highly skilled at. Copilot is smart, but it can’t replace you.

If you’re interested in learning more about how Microsoft Copilot can help you transform your business with AI, visit our Copilot page to find out more or get in touch today to speak to one of our experts for an initial chat.