How to Use Microsoft Copilot in Excel

Quick Answer: Microsoft Copilot in Excel is a built-in AI assistant that helps you analyze data, write formulas, and create visualizations with natural language prompts. Type in what you want the Excel document to do, and Copilot in Excel will do the majority of the technical work for you.

Ever stared at a large dataset in a spreadsheet and wondered where to even start? Sorting through thousands of rows of data can be a real chore, but as luck would have it, AI help has arrived in Excel in the form of Copilot. Memorizing Excel syntax and formulas is now a thing of the past; if you can explain what you want in natural language, then your chances of getting your data whipped into shape are good.

You still need to keep your wits about you, though. As good as Copilot is, you still need to remember that it's your responsibility to check and verify your outputs. For tasks like finding trends in sales data or creating a pivot table that groups data fields by different metrics, Copilot will be your new best friend. 

Let’s find out more about how to use Copilot in Excel, what it can do in its current form, and how you can dodge common mistakes that newcomers make when first getting started with Copilot in Excel.

What is Microsoft Copilot?

Microsoft Copilot is an AI assistant integrated into Microsoft 365 apps across desktop and web versions. It uses Large Language Models (LLMs) and has access to your data and the shared data of your company or organization. In Excel, it can read your spreadsheet and follow your instructions in natural language. 

Here’s how it is different from generic AI tools:

• It works directly within Excel (web and desktop), not in a separate browser window.

• It understands your actual data and can read your columns and rows if formatted in a table.

• It can write formulas, create charts, and provide real-time insights on the fly. 

• It keeps your data private within your Microsoft environment. 

If you are wondering what the big deal is, there’s one word you need to think about. Time. Tasks that could have taken you an hour to Google and figure out, like formulas and the frustrating debugging steps, can now be conjured up in seconds. If you work with data regularly, you know how this time-saving can add up.

What are the Core Features of Copilot in Excel?

Here are some of the interesting features that Copilot brings to the table (Sorry, but I couldn’t resist the Excel pun).

Data Analysis

Copilot scans your spreadsheets and finds patterns that you might have missed, or not even thought to look for. You can ask simple questions like “What are the top 5 products by profit?” or “Show me trends in our sales data related to our marketing campaign spend”. 

It pulls details directly from your data and presents them clearly. It is highly recommended that you look into prompt engineering to learn the basics about how LLMs work for the best results. 

Formulas and Calculations

This capability alone is reason enough to celebrate. You don’t need to memorize syntax (or reference Google)  for VLOOKUP, INDEX MATCH, or nested IF statements anymore. Just describe what you want, and let Copilot get on with the work while you grab a coffee. 

Try out something like “Calculate the percentage change between columns A and D,” and Copilot will generate the formula for you. Copilot will also explain what the formula does and how it works, so you will actually learn as you work with it.

Visualization

A picture is worth a thousand Excel formulas. Charts are eye candy for meetings and presentations, and without them, it is difficult to convey your message to people who don’t understand the technicalities of your work. Tell Copilot what kind of visualization you want, and it will build it for you. 

Start with queries like “Build a bar chart comparing Q3 and Q4 sales by division” or “Create a graph of new customer sign-ups by month for 2025”. If you aren’t sure about which visualization will work best for the specific data, you can ask Copilot to provide suggestions.

Automation and Reporting

Copilot helps you format data for reports and can highlight specific metrics or summarize large datasets. It isn’t full automation like Power Automate, but it can speed up repetitive daily tasks that eat up your time.

Step-by-Step Guide to Using Copilot in Excel

If you're ready to see how Copilot can help you manage data in Excel (and a whole lot more), here's how to get started. 

Step 1: How to Get Copilot in Excel

To get started with Copilot in Excel, you need to check a few items off the list:

• A Valid License: Copilot Chat is free for basic M365 subscribers, so you can ask questions about your Excel sheets, and it will reply with suggestions. That excludes advanced features like generating and applying formulas or creating charts. For in-app generation, you will need a  $30/user/month Copilot for M365 subscription for business users, or a $20/user/month Copilot Pro license for home users.

• A Cloud Connection: Your files must be saved in OneDrive or SharePoint with Autosave enabled.

• Structured Data: Your data has to be formatted as an Excel table for it to be read successfully.

If you don't see Copilot in Excel, then you may need to check the Microsoft 365 admin center. If you don’t have admin access, then you will need to request it through your organization. Make sure you have an active Copilot license. 

Step 2: Format Your Data as a Table

To use Excel and Copilot together, your data must be structured in a formatted table. All you need to do is select your data range, click on Insert, and then Table. Make sure the “My table has headers” option is checked if your first row contains header names. 

It’s tempting to just get started on a raw Excel spreadsheet before storing and organizing your data, but for the best results, don’t skip this step.

Step 3: Open the Copilot Panel

Click on the Copilot button inside the Excel ribbon above your Excel sheet to get access to your Copilot command center. A side panel will open up where you can start your conversation with Copilot.

Step 4: Start with Simple Prompts

When you’re just getting started with Copilot, don’t overthink it. Try some simple requests as you get used to the AI's conversational flow. A good place to begin is with something general, like asking for a summarization or a request for insights about the data in your formatted table. You might be surprised by what Copilot comes up with.

Step 5: Refine, Iterate, and Polish

Copilot isn't perfect every time. You will spend a lot of effort figuring out how to ask the right kinds of questions and steer the conversation so you get the results you are after. You’ll need to rephrase some of your queries and be specific about exactly what you want it to do.

The more context you give to Copilot, the better the results will be. With a little practice, you’ll wonder how you got anything done without AI.

How to Maximize Copilot's Effectiveness in Excel

There are a few tips and tricks that will help you get the most out of Copilot in Excel:

• Try to use descriptive headers like “Sales_Q4” instead of a generic name like "Column A.”

• Keep your data clean by removing blank rows and inconsistent formatting.

• Ask Copilot one question at a time, and focus on that until you get the output you want. Peppering AI systems with multiple questions can sometimes confuse them or cause them to forget part of the request

• Always review the formulas Copilot produces before accepting them. It works really well, but sometimes it can make mistakes that will affect your work if you don’t pick them up

AI Agentic Coding training covers many fundamentals to help you understand how to query Copilot for great results.

How to Avoid Potential Pitfalls with Copilot in Excel

Copilot currently has some limitations you should know about to avoid wasting time and getting frustrated. 

• Garbage In, Garbage Out: Copilot is amazing tech, but it isn’t magic. If your data is messy, with merged cells, inconsistent date formats, or missing values, you won’t get great results. Clean up your data so that Copilot doesn’t struggle.

• Trust, But Verify: Always check the AI system's outputs and verify them before you send your work. AI systems can make mistakes, and it's up to you to verify that what you have received from the system is accurate.

• Use AI Where it Makes Sense: If you find that the AI is slowing you down in some areas, then don’t feel obliged to use it everywhere. As you learn about Copilot’s strengths and weaknesses, you will figure out where to use it for maximum effect.

If you want to know how to turn off Copilot in Excel, there’s currently only one way to do it. You can either disable it in your Excel Options menu or by asking your administrator to disable it for you. 

When and Where to Use Microsoft Copilot in Excel

Copilot slots into workflows comfortably where you’re exploring data or building reports, and it's also good at getting you answers fast. 

Some examples where Copilot in Excel is useful are:

• Sales teams that need to analyze sales data to identify trends or build forecasts.

• Finance teams building reports. 

• HR departments that need to summarize employee survey results.

• Operations teams who are tracking inventory levels.

As a general Excel tool, Copilot serves as a useful assistant that can quickly provide preliminary data and figures. For more advanced formulas, it is still a good idea to manually check and validate Copilot’s suggestions.

Conclusion

Microsoft Copilot in Excel is an excellent time-saver and can significantly speed up your daily tasks. Describe what you want, and watch the AI handle all the technical details while you think about refining and improving your work. 

The best way to get started with Microsoft Copilot is to just start using it. Format a table, clean up your data, and open Copilot. Start by asking it a question and see how it handles it. You’ll quickly learn where it works best for you, and where you’re better off doing the work on your own. Learn how to get started with Microsoft Copilot with Jonathan Barrios.

Want to try a CBT Nuggets course? Sign up and get your first 7 days for free.