Copilot in Excel: How to access, features, and use cases

With the introduction of Microsoft Copilot in Excel, interaction with spreadsheets is revolutionized through the advanced use of artificial intelligence (AI). Microsoft Copilot in Excel acts as an intelligent assistant, enhancing user efficiency through expert suggestions and rapid insights. This innovative feature leverages AI to improve the Excel experience by automating complex tasks, suggesting optimal formulas and relevant data visualizations, and providing continuous support in data management and analysis.

Introduction to Microsoft Copilot

Microsoft Copilot is an advanced AI-based tool designed to optimize the work efficiency of businesses and individual users. Acting as an AI chatbot, Microsoft Copilot collaborates with human capabilities, automating repetitive and complex tasks to enhance productivity.

The main features of Microsoft Copilot include:

• Email management: Automation and organization of email communications.
• Scheduling: Organization of meetings and events, integrating with existing calendars.
• Meeting preparation: Provides relevant information and materials.
• Personalized recommendations: Suggests actions and decisions based on data analysis and user habits.

Introduction to Microsoft Copilot in Excel

Microsoft 365 Copilot in Excel is a cutting-edge feature that utilizes artificial intelligence and machine learning algorithms to optimize the user experience in Excel. By integrating these advanced technologies, Copilot offers intelligent suggestions and real-time formula insights while users work on their spreadsheets. By analyzing patterns, context, and user behavior, Copilot in Excel anticipates the user's needs and provides targeted support.

The main features of Microsoft Copilot in Excel include:

• Intelligent suggestions: Using data analysis and user context, Copilot proposes appropriate formulas and functions to facilitate data processing.
• Machine learning: Copilot's machine learning models analyze user behavior to offer personalized suggestions and continually improve the assistance provided.
• Real-time assistance: Copilot provides immediate support while the user works, reducing the need to interrupt the workflow to search for information and solutions.

For example, if the user needs to perform a complex calculation or create a specific chart, they can simply describe the request in natural language. Copilot will interpret and perform the desired operation, eliminating the need to deeply understand Excel's language and functionalities.

Microsoft Copilot in Excel also allows:

• Formula debugging: Identifies and corrects errors in complex formulas, increasing calculation accuracy and saving valuable time.
• Automatic formatting: Suggests character styles, data types, and colors to improve the readability and presentation of spreadsheets.
• Data visualization: Proposes the most relevant chart types based on the data present, facilitating the creation of effective and intuitive visualizations.

Copilot in Excel: prerequisites for use

To enable the features of Microsoft Copilot in Excel, specific prerequisites must be met, and a series of technical steps must be followed involving the acquisition of necessary subscriptions and application installations.

• Acquire a Microsoft 365 subscription: Ensure you have a Microsoft 365 subscription that supports Copilot, such as Microsoft 365 Personal, Family, or Business. These plans provide the necessary foundation for integration with Copilot.
  ‍
• Obtain a Copilot subscription: Select the appropriate Copilot plan for your Microsoft 365 subscription and complete the purchase. If the organization has purchased Copilot for the user, the system administrator must assign a Copilot license to the specific user.
  ‍
• Install Excel 365 or access the web app: Download and install Excel 365 on your desktop device. After installation, log in with your Microsoft account to activate Copilot features. Alternatively, access the Excel web application via a browser, logging in with your Microsoft account to use Microsoft Copilot in Excel online.

Copilot in Excel: key features

Microsoft Copilot in Excel is an advanced tool designed to improve the management, analysis, and visualization of data in spreadsheets. Let's look specifically at its functionalities.

Data analysis

Microsoft Copilot in Excel automatically examines data tables, highlighting crucial information and providing significant insights. Users can further interact with Copilot by asking specific questions to delve into the details revealed.

Financial modeling

During financial model processing, Microsoft Copilot in Excel allows users to explore different scenarios through "What if" questions. For example, you can ask Copilot to "model how a change in X would affect Y," facilitating the prediction and analysis of variable changes.

Visualizations

Integrated into Microsoft Copilot in Excel, this feature assists in creating charts and diagrams, suggesting the best visual representations for the data, transforming complex datasets into clear and understandable visualizations.

Formula writing

Microsoft Copilot in Excel supports users in writing complex formulas, providing contextual suggestions and automatically completing mathematical and logical functions, simplifying the construction of advanced calculations.

Data organization and cleaning

Copilot can perform data cleaning operations such as removing unnecessary spaces, merging values from different columns, highlighting duplicates, and managing sorting and filtering operations, thus optimizing the quality of datasets.

Intelligent suggestions

Based on the data context and ongoing operations, Microsoft Copilot in Excel provides recommendations for formulas, functions, and actions, improving work efficiency and accuracy.

Data insights

Microsoft Copilot in Excel identifies trends, anomalies, and correlations in the data, providing insights that can guide informed and strategic decisions.

Task automation

Copilot can generate VBA code to automate current tasks, reducing the need for manual intervention and speeding up the workflow. This is particularly useful for complex repetitive operations that would take a long time if done manually.

How to enable Microsoft Copilot in Excel

To enable Microsoft Copilot in Excel, follow these steps:

1. On Windows, click on "Start".
2. To open the "Registry Editor", search for "regedit" and click on the top result. Navigate to the following path:
   ‍HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Common\
ExperimentConfigs\ExternalFeatureOverrides\excel
3. Right-click on the Excel key.
4. Select the "New" menu and choose the "String Value" option.
5. Select the "Microsoft.Office.Excel.Copilot" key and press "Enter".
6. To enable Copilot, double-click on the newly created key and set its value to "true".
7. Select "OK" and restart your computer.

How to use Copilot in Excel

Getting started with Microsoft Copilot in Excel is a simple and intuitive process. Follow these technical steps to make the most of Copilot's advanced features:

1. Start Excel:
   • Open Excel 365 on your computer or access the Excel web app using your Microsoft account.
2. Open a cloud-stored workbook:
   • Open the workbook stored on OneDrive or SharePoint where your data resides. Copilot works only with files stored in the cloud.
3. Activate Copilot:
   • In the Home tab of the Excel ribbon, click the Copilot button to activate it.
4. Convert data into a table:
   • Microsoft Copilot in Excel operates exclusively on Excel tables. If your data is not already in table format, press Ctrl + T to convert it, or let Copilot perform the conversion automatically.
5. Use Copilot:
   • In the Copilot pane, follow the suggested options or directly enter your request in the text box. You can also explore additional prompts provided above the chat box.
6. Review and apply results:
   • Microsoft Copilot in Excel can directly implement solutions in the spreadsheet for operations like highlighting, sorting, and filtering. For more complex actions, such as adding formula columns or creating visualizations, Copilot will show results in the pane. You can click on "Explain formula" for additional details. Hover over action buttons like "Insert Column" to preview the results. Once the accuracy is confirmed, click "Insert" to add the result to the worksheet.
7. Manage unexpected results:
   • If you get unexpected results, use Copilot's Undo button for quick corrections.
8. Explore further prompt suggestions:
   • To get more prompt ideas, click on the View Prompts icon at the bottom of the Copilot pane. Explore the suggested categories like Create, Understand, Modify, and Ask. Each category provides a variety of prompts that can be used directly or modified to suit your specific needs.

Copilot in Excel: key use cases

Let's explore some practical examples of how Microsoft Copilot in Excel can be used. By examining the suggested categories, you can get an overview of the various functionalities and tasks that Copilot can perform.

Adding formula columns with Copilot in Excel

Do you need to calculate something in your dataset but don't know how to write a formula? Ask the AI to build a formula for you:

1. Click on "Add formula columns" in the Copilot pane.
2. Select one of the suggested prompts or describe your calculation needs in your own words.
3. Copilot will write a formula and explain how it works.

Choose the desired action:

• Explain the formula: Click on "Explain formula" to better understand the logic behind the generated formula.
• Preview results: Hover over the "Insert formula" button to preview the results.
• Direct insertion: Click on the "Insert formula" button to add the results directly into the worksheet.

Now let's look at some practical prompt examples.

Formula to compare Q1 and Q2 revenues

Copilot can add a formula to compare Q1 and Q2 revenues.

=IF([@[Q1 Revenue]]>[@[Q2 Revenue]], “Q1”, “Q2”)


Percentage change calculation

You can ask Copilot to create a column that shows the percentage difference between Q1 and Q2 revenues.

=([@[Q2 Revenue]] - [@[Q1 Revenue]]) / [@[Q1 Revenue]]


Copilot will generate an accurate percentage change formula and automatically apply the percentage format to the newly added column.

Q3 revenue projection

Copilot can be used to project Q3 revenues based on available data.

Other examples of formulas generated by Microsoft Copilot in Excel:

Total revenue calculation for Q1 and Q2

=[@[Q1 Revenue]] + [@[Q2 Revenue]]


Profit margin calculation

Assuming Revenue and Profit columns are present:

=[@[Profit]] / [@[Revenue]]


Project duration calculation in days

Assuming the Start Date column is available:

=[@[End Date]] - [@[Start Date]]


Creating multiple formula columns simultaneously with Copilot in Excel

With the latest update in April 2024, Microsoft Copilot in Excel introduced an advanced feature that allows the creation of multiple formula columns simultaneously with a single prompt. This is particularly useful for addressing common situations like splitting full names into First Name and Last Name fields.

Key features include:

• Detailed prompt:You can provide Copilot with a specific prompt, such as "split the name column into first name and last name" or "extract the first and last names from column C and insert them into two new columns".
• Formula suggestions:Copilot automatically suggests the appropriate formulas to perform the requested operation. The proposed formulas can be manually copied and pasted or directly inserted into the worksheet with a single click.

Here's a practical example:

"Extract the first and last names from column C and insert them into two new columns".

Formulas suggested by Copilot for the first name:

=LEFT(C2, FIND(“ “, C2), 1) - 1)

Formulas suggested by Copilot for the last name:

=RIGHT(C2, LEN(C2) - FIND(“ “, C2))


Benefits:

• Efficiency: Saves time by creating multiple formula columns simultaneously instead of one by one.
• Accuracy: Reduces the risk of errors thanks to the precision of the formulas suggested by Copilot.
• Reliability: Improves data management by providing quick and reliable solutions for complex formula operations in Excel.

Highlighting with Copilot in Excel

With Microsoft Copilot in Excel, you can effectively highlight crucial information in your spreadsheets using conditional formatting. Here's how to proceed:

• Using Copilot for highlighting: Access the Copilot pane and select the "Highlight" option.
• Follow the provided prompts or specify your preferences: You can describe your conditional formatting preferences directly in the text box or follow the prompts provided by Copilot.
• Dynamic conditional formatting: Copilot applies conditional formatting, meaning changes made to the data will automatically reflect in the applied formatting. For example, if the data changes, the formatting of the highlighted elements will update accordingly.

You can ask Copilot to highlight:

• Revenue values above a specific threshold, such as 50,000.
• The 10 lowest values in a column.
• Duplicate rows in the worksheet.

Current limitations

At the moment, Copilot only supports simple conditional formatting and cannot handle complex requests involving multiple conditions. For example, it cannot perform conditional formatting based on multiple criteria simultaneously.

Sorting and filtering with Copilot in Excel

With Microsoft Copilot in Excel, you can use the advanced sorting and filtering process for data in your spreadsheets. Here's how to use this feature:

• Using Copilot to sort and filter: Access the Copilot pane and select the "Sort and Filter" option.
• Select suggested prompts or specify your needs: You can choose from the prompts suggested by Copilot or detail what you are looking for directly in the chat.

You can instruct Copilot to filter "Design" projects where the percentage change is negative. Copilot will immediately perform the search and display results that match your criteria. Additionally, Copilot facilitates simultaneous filtering through multiple criteria, providing additional assistance compared to traditional Excel filters.

Analyzing with Copilot in Excel

Now we enter the heart of data analysis with Microsoft Copilot in Excel, one of the most powerful features available for exploring and understanding data. To start the analysis with Copilot in Excel, you have two main options:

1. Navigate to the Analyze section.
2. Show data insights.

The primary method by which Copilot conducts analysis is through pivot tables and pivot charts. When asked to show insights on the data, Copilot generates two pivot tables and six visualizations that clarify various aspects of the data. Here is what we can deduce from these charts:

• Design projects show the highest profits.
• Research projects have experienced the largest percentage increase in revenue from Q1 to Q2.
• Profit margins for most projects fall between 3% and 10%.
• The most common budget range is between $56,000 and $71,000.
• There is a significant correlation between budget and revenue.

If you wish to further explore the analysis, you are free to do so. For example, you can request a detailed breakdown for specific data categories. Additionally, it is possible to explore various hypothetical scenarios (What if), ensuring that your queries are precise and detailed. Copilot relies on clarity and precision and may struggle to respond to vague or ambiguous questions.

Visualizing with Copilot in Excel

When the charts suggested by Microsoft Copilot in Excel for your data analysis do not meet your expectations, you have the option to directly instruct Copilot to create the desired chart. This process is simple and allows you to specify the chart type and data categories to include.

Here's how to proceed:

1. Access the Copilot pane in Excel.
2. Instruct Copilot to generate a specific chart.

For example, a bar chart showing Q1-Q3 revenues for "Development" projects.

This approach allows you to customize visual outputs according to your needs without having to navigate through various visualization options. Copilot will execute your instructions and provide the desired chart.

Automating tasks with Copilot in Excel

With Microsoft Copilot in Excel, you can automate your daily tasks by generating VBA code. Copilot not only writes the code for you but also provides detailed instructions on how to integrate it into your worksheet and run the corresponding macro. Here's a practical example:

Benefits of Microsoft Copilot in Excel

1. Natural language queries

With Microsoft Copilot in Excel, it is possible to formulate queries using natural language. Users can ask questions or give commands using common words, and Copilot will respond with appropriate charts, tables, or formulas. This eliminates the need to remember the specific syntax of Excel functions, facilitating interaction even for complex tasks.

2. Task acceleration

Microsoft Copilot in Excel speeds up the execution of repetitive tasks such as data cleaning, sorting, and filtering. Thanks to its ability to identify patterns and anomalies in the data, users can focus on data analysis rather than manual manipulation, thereby improving overall efficiency.

3. Accessibility for all skill levels

ùMicrosoft Copilot in Excel is designed to be intuitive and accessible to users of all skill levels. Both beginners and Excel experts can benefit from Copilot's advanced features, making Excel easier and less intimidating to use.

4. Continuous learning and improvement

The AI algorithm of Microsoft Copilot in Excel continually improves as it learns from user interactions. This continuous learning makes Copilot increasingly effective in understanding specific user needs and providing relevant and timely assistance, optimizing the overall user experience.

Conclusion

Microsoft Copilot in Excel represents a significant step forward in the automation and simplification of spreadsheet operations. By integrating AI and machine learning, Copilot transforms the user experience, making complex functions more accessible and improving work efficiency.

Its ability to process natural language queries, speed up repetitive tasks, and continually learn from user interactions makes it an indispensable tool for both beginners and Excel experts. Ultimately, Microsoft Copilot in Excel not only enhances productivity but also opens new possibilities for data analysis and management, confirming Excel as a reference tool in office applications.

FAQ on Copilot in Excel

What is Excel Copilot?

‍Excel Copilot is an AI-powered tool in Microsoft Excel that enhances productivity by offering intelligent suggestions, automating tasks, and providing real-time assistance.

How can I enable Copilot in Excel?

‍To enable Excel Copilot, ensure you have a Microsoft 365 subscription, install Excel 365, and follow the steps in the Registry Editor to activate the feature.

What features does Copilot in Excel offer?

‍Key features include intelligent formula suggestions, data visualization, task automation, and real-time data analysis.

How does Copilot assist with data analysis in Microsoft Excel?

‍Copilot in Excel automatically analyzes data, highlights insights, and creates pivot tables and charts to help users understand trends and correlations.

Can Copilot write Excel formulas for me?

‍Yes, Excel Copilot can generate complex formulas based on natural language requests, simplifying the process of performing calculations in spreadsheets.