Getting started with Microsoft Copilot Excel Tips and Best Practices

How to Use Copilot in Excel Top Tips

Getting started with Microsoft Copilot Excel: Tips and Best Practices

Wondering how to use Copilot in Excel to upgrade your workflows? We’ve got you covered. Included within the complete Microsoft 365 Copilot toolkit, Copilot for Excel helps you optimize everything from data analysis and visualization to spreadsheet creation.

Now generally available to all users, Excel Copilot is a convenient, easy-to-use tool powered by generative AI. It features the same foundational features as other Microsoft 365 Copilot solutions, combining large language models with data from the Microsoft Graph and your Microsoft 365 apps. However, Excel has unique functionalities, such as access to Python integrations.

Whether you want to streamline financial planning, data transformation, or chart generation, here’s your complete guide to using Microsoft Copilot in Excel.

How to Use Copilot in Excel: What You Can Do

Learning how to use Copilot in Excel starts with discovering what the generative AI assistant can do. All of the “Microsoft 365” app versions of Copilot have similar functionality, such as a conversational interface and access to your Microsoft data.

However, they’re intended to offer different levels of support based on your workflows in specific apps. For instance, in Excel, users can:

Leverage Insights from Natural Language Queries

Like all versions of Microsoft’s Copilot tools, Copilot in Excel features a conversational interface, allowing users to interact with an intelligent bot using natural language. Rather than dealing with complex code, you can simply type questions into a chat box to access suggestions, data summaries, and insights into your data.

Copilot can reveal correlations between data sets, suggest what-if scenarios, and help you uncover trends in seconds. For instance, you can enter commands like “Give me a breakdown of sales by channel and type, presented in a table.”

Clean and Transform Your Data

Once you learn how to use Copilot in Excel, you’ll spend much less time stressing over managing, cleaning, and transforming your data. The AI app can instantly identify missing values in templates, inconsistencies, and other problems with your data.

With simple natural language requests, you can ask Copilot to remove extra spaces using the “Trim” option or delete data duplicates. Plus, you can perform advanced analytics using Python libraries like skit-learn, pandas, or matplotlub to help fix errors in your data.

The Gen AI Copilot in Excel can also remember the context of previous queries and results so that you can perform data analysis in cleaning tasks iteratively.

Generate Charts, Visualizations, and Models

One excellent feature of Copilot in Excel is the ability to generate charts and visualizations instantly in your spreadsheets. The bot can recommend the best chart based on your data, effortlessly highlighting important information.

You can ask Copilot to showcase how a variable will affect your data with a progression chart or use access insights into data trends with conditional formatting. Copilot can even provide formula column suggestions for more complex calculations. Alternatively, you can create models for various scenarios by asking questions like “Model how a change to growth for [variable] would influence gross margins.”

How to Use Copilot in Excel with Python

Webinar On Demand

In August 2023, Microsoft introduced a new update for employees learning how to use Copilot in Excel. You can now access Python programming tools and libraries within your spreadsheets.

In addition to helping users write formulas, format data, and analyze information, Copilot can also help you write Python code. Tell the AI assistant what you want to do with your data, and Copilot will insert working Python code into your grid. With Python and Copilot together, you can:

Analyze data instantly: Describe the analysis you want to do, and Copilot will generate Python code in a cell for you to use. For instance, based on historical data, you can ask Copilot to forecast your sales for the following year.

Access libraries and visualizations: Copilot leverages all the flexible libraries available through Python for users. You can use the AI tool for forecasting, data optimization, statistical tests, causal models, classification, sampling, and creating various charts.

Break down complex problems: With Python and Copilot in Excel, users can break down complex problems step-by-step using Copilot as a handy assistant. You can ask follow-up questions about any data you analyze and generate new ideas.

How to Use Copilot in Excel: Getting Started

Learning how to use Copilot in Excel is pretty straightforward, but there are some prerequisite steps you’ll need to take. First, you’ll need access to a Microsoft 365 Copilot license. This costs $30 per user per month, on top of the price you pay for your Microsoft 365 subscription.

You’ll also need an Entra (Formerly Azure Active Directory) ID and a OneDrive account to save and share files through the cloud. If you will be using Excel with Outlook, Loop, Microsoft Teams, or Whiteboard, you’ll need access to those tools, too.

Ensure there aren’t any security or privacy policies that might make it difficult for Copilot to access your data. Additionally, you’ll need to ensure you’re on the “Current” or “Monthly Enterprise” channel for your Microsoft 365 apps and have the latest version of Excel.

If you’re using Excel through a business account, ensure your administrator has assigned you the appropriate license for Copilot and enabled access to the app.

How to Access Microsoft Copilot Excel

Once you’ve got the proper license and access, you can access Copilot in Excel from your web or desktop app. The Copilot icon should appear in the right corner of your Excel menu ribbon. Click on this whenever you want to work with Copilot.

Remember, you’ll need an Excel table that includes data before Copilot can help you analyze anything. To turn a range of cells into an Excel table, select the cell or data range and click “Home” followed by “Format as Table.” In the dialog box that appears, select the checkbox for “my table as headers” if you want the first row of the range to appear as the header.

Once you have your data formatted as a spreadsheet, you can click on the Copilot icon to converse with the bot naturally, asking questions or requesting visualization suggestions. Alternatively, you can tell Copilot to analyze your data by clicking the Copilot icon, followed by “Get Started” and then “Analyze.” Copilot will also provide a list of other suggested prompts to consider.

Hit “Send.” Copilot will analyze your data to show insights such as summaries, trends, outliers, PivotTable objects, or charts. You can then hit the “refresh” button for more prompts to take your analysis further. If you want to add a suggested chart to a sheet, just click “Add to Sheet.”

How to Use Copilot in Excel: Quick Tips

Now you know the basics of how to use Copilot in Excel, here are some quick tips to help you get the most out of the experience:

Ensure your organization is prepared for Copilot: Microsoft offers some valuable resources and guidance for beginners. There’s a guide here that can show you how to prepare and set up the proper technical configurations and protect your data.

Master your prompt strategy: With any LLM, the better your prompts are, the better your results will be. The Copilot Lab Experience from Microsoft helps you track down “power prompts” and offers examples of prompts to try.

Customize your Copilot: With Microsoft Copilot Studio, you can customize your generative AI experiences within Microsoft 365 apps. There are options to help you add your own data and workflows to Copilot and customize responses. Developers can also create their own generative AI apps with the Studio.

Stay up to date: Keep track of the latest features rolled out for both Copilot Excel and Microsoft 365 Copilot. You can watch the Microsoft feature roadmap for updates, join the Copilot Community, or follow our news reports here.

Practice: Experimenting with Copilot will help you to use it more effectively. Try different prompts and analytical strategies, and share your results with your team. You can even share prompts with team members through Microsoft 365.

There you have it, everything you need to know to use Microsoft Copilot for Excel and take your spreadsheets to the next level.