Whether you're a beginner or an experienced Excel user, navigating complex formulas, endless data cleaning, and time-consuming formatting can be a daunting task. That's where Microsoft's AI-powered Copilot assistant comes in. From generating formulas and displaying insights in charts to highlighting interesting data, Copilot enables you to work smarter, not harder. Read on to discover the most cutting-edge ways to leverage Copilot's features and take your Excel experience to the next level.
What is Copilot in Microsoft Excel?
Before we explore Copilot in Microsoft Excel, let's briefly talk about the overall integration and explain why it's important for spreadsheet users.
Copilot essentially acts as your personal assistant in Excel, as it knows your spreadsheets intimately, just like you do. It can suggest formulas, answer your questions, and even allow you to do more with a specific cell. For example, if you're working with a monthly expenses database in Excel, just ask Copilot to calculate things and create a chart based on the data.
Note that Copilot doesn't have a magic wand in Excel. It accomplishes the same thing you could do with advanced Excel functions and features. The main advantage is that you don't have to delve into Excel menus and can use a single interface to get the job done. It is, of course, a boon for new Excel users who are less comfortable with writing formulas.
Access Copilot in Microsoft Excel
First of all, Copilot in Microsoft Excel is only available in desktop versions. You can't access it from the Excel web version. As you may know, Excel's desktop apps require a Microsoft 365 subscription. Pricing starts at $7 per month for Microsoft 365 for Home.
Once you've downloaded Excel for Windows or Mac, it's time to purchase a Copilot Pro subscription to unlock access to the AI bot in Office apps, including Excel. It's priced at $20 per month.
Now you can launch Microsoft Excel on Windows or Mac and find the Copilot button at the top of the Home menu. Let's see some of its features in action.
5 Gain insights from your data
Thanks to a handy Copilot integration in Excel, you can easily generate valuable insights from a database. Let's start with an example.
- Open an Excel sheet with a database and click the Copilot icon in the top right menu.
- Simply ask Copilot View data insights.
- The AI chatbot can quickly analyze the data and create a small pivot chart to show the number of units sold by each seller.
- Copilot has also released a small summary of the data in text format.
Without Copilot, you would have to convert the database to a table and create pivot tables in another sheet to extract the same data. Copilot accomplished the same task with just a simple text prompt.
You can also ask How many units did Jim sell? and the system should calculate and display the final data with a pivot table. See the screenshot below for reference.
You can even make your requests more specific. For example, you can ask In which month did Jim sell the most units? and receive relevant answers in the shortest possible time.
4 Generate suggestions for a formula column
Based on your current database, Microsoft Copilot can also suggest a formula column and let you insert one with a single click. Let's continue with the same example.
- Open the Excel spreadsheet and ask Copilot to Show a suggestion for a formula column.
- Excel calculates total sales by removing the dollar sign from the sales amount and multiplying it by the number of units sold. It even displays the required function in case you want to use it for another column or customize it.
- You can take a look at the top five results and Insert column for whatever suggestion you want to use.
In the example above, Excel randomly generated a formula column. You can even be more specific with your requests. When I asked, Combine seller and region and create another columnExcel quickly created a formula and displayed a column preview.
The possibilities are endless here. It all depends on your use case, your database, and your text prompts. You should make your prompts as verbose as possible to get the results you want in the shortest time possible.
3 Apply color and formatting
This is another time-saving hack for Copilot users in Excel. When you're looking for specific data or products, fire up Copilot and let it highlight relevant cells.
- I opened the same Excel sheet.
- I called Microsoft Copilot and asked it to scan all cells with XPS14.
- A suggestion and preview were displayed. By clicking Apply we can see the formatting in action.
You can also ask for the total number of units sold for profit excluding $6,000 and get the final results. Just click Applyand you will immediately receive the information you require via chat.
2 Understand your database
Microsoft has intelligently integrated Copilot into your databases. You can simply select columns and use the Copilot shortcut to highlight data or create charts in real time. Let's continue with the same example.
- Open an Excel spreadsheet. Let's select the columns for sellers and units sold.
- Click the Copilot button, slide diagramand create a quick chart showing the total number of sales made by each employee.
- You can also click on the Tables Menu and check out some of the ready-to-use pivot tables.
Copilot changes the suggestions based on the data you select in Excel. For professionals who frequently work with large databases, this can be a huge productivity boost.
1 Additional Copilot functions in Excel
Copilot efficiently cleans up cluttered data by removing extra spaces, merging values from different columns, highlighting duplicates, and applying sorting and filtering functions.
As you can see from the screenshots above, contextual recommendations for formulas, functions, and actions are also suggested.
Programmers can even go a step further and ask Copilot to write VBA code to automate command tasks in Excel.
Excel on autopilot
Overall, if you're an experienced Excel user, Copilot is definitely worth giving a try. It can automate your tedious tasks, help you discover insights hidden in your data, and streamline your workflow in minutes. What are you waiting for? Discover Copilot, use the tricks above, and transform your Excel experience from a chore to a breeze.
Aside from Excel, your active Copilot plan also unlocks the AI assistant for other Microsoft 365 apps, including OneNote. Check out our separate post for the best tricks for using Copilot in Microsoft's note-taking app.