How to use Copilot in Excel: Step-by-Step Guide

Microsoft Excel has long been a cornerstone for data management, analysis, and reporting across industries. From small businesses managing inventories to large corporations analyzing market trends, Excel is a versatile tool that caters to countless needs.

And with the advent of AI-powered software, spreadsheets like Excel have been supercharged and given a new life. In this blog, we’re going to dive deep into Copilot as the main AI power that drives efficiency in Excel. We will see how to use Copilot in Excel, why it is the preferred AI for most businesses, as well as how to overcome any limitations it might have.

What is Copilot in Excel?

Excel is already a powerful spreadsheet software from Microsoft. But now, adding to its prowess, Copilot in Excel is an innovative AI-driven assistant that redefines how users interact with spreadsheets. Gone are the days when you had to scroll endlessly online to find out what formula to use. Or the time spent on creating new columns and tables. Unlike traditional manual methods, Copilot brings intelligence and automation to the forefront, empowering users to perform tasks faster and more accurately.

This tool simplifies complex tasks, provides intelligent insights, and automates workflows. By leveraging natural language processing, Copilot enables users to create formulas, analyze data, and format spreadsheets effortlessly.

Step-by-step guide on how to activate Copilot in Excel

To begin using Copilot in Excel, there are some necessary and mandatory steps that you need to take first. Below are the simple steps you need to follow in order to launch and activate Copilot in Excel.

Step 1: Obtain a Microsoft 365 subscription

To access Copilot in Excel, you will need an active Microsoft 365 subscription. Microsoft 365 offers various plans that are tailored to individual, business and enterprise needs, ensuring you get the tools required for productivity. You may contact the Microsoft professionals at Gestisoft to get more information on the packages that are available for Microsoft 365.

Note: Note that each Microsoft 365 package provides different levels of functionality.

Step 2: Obtain a Copilot subscription

Once you have access to Microsoft 365, subscribe to the Copilot add-on to unlock its full potential in Excel. This subscription is separate but essential for accessing AI-powered features. You may check the availability and pricing through your Microsoft partner or software provider, such as Gestisoft to determine which package suits your business the best.

Step 3: Install Excel 365 app or access the web app

To maximize compatibility with Copilot in Excel, download and install the desktop version of Excel from your Microsoft 365 account, or use the Excel web app. The web app provides flexibility for on-the-go tasks, while the desktop version delivers robust functionality for intensive projects.

Step 4: Look for the Copilot icon

Once installed, open Excel and locate the Copilot icon, typically found on the toolbar or within the “Insert” or “Data” tabs. This icon is your gateway to accessing and using Copilot in Excel. If you do not see it, double-check your subscription status and contact your IT team for support.

Capabilities when it comes to how to use Copilot in Excel

When it comes to the capabilities of Copilot in Excel, they are limitless. AI-powered software is known to save time and increase efficiency, and this is no different when it comes to how to use Copilot in Excel.

Copilot in Excel enhances spreadsheet functionality with features like:

• Generating complex formulas: With natural language prompts, you can quickly create formulas that would otherwise take time and effort.
• Automating data analysis: Copilot identifies trends, detects anomalies, and provides concise summaries, enabling faster decision-making.
• Formatting cells and tables efficiently: From conditional formatting to creating visually appealing tables, Copilot handles formatting with ease.
• Creating data visualizations: It transforms raw data into insightful graphs and charts in seconds.

Understanding these capabilities is key to mastering how to use Copilot in Excel, as it streamlines operations and enhances efficiency.

How to use Copilot in Excel?

Now time for the big question: how to use Copilot in Excel? This is a rather straightforward question, but the answer is not. This is because there are many ways that you can use Copilot in Excel.

In this blog, we will be covering each of the way that Copilot can be integrated in Excel’s usual tasks, and how businesses can benefit from that. First things first, we will begin with the primary step on how to use Copilot in Excel, which is through prompts.

How to use Copilot prompts in Excel?

As mentioned above, there are various ways on how to use Copilot in Excel, and one of them is through prompts. Prompts are quick and easy ways for users to obtain an output from Copilot based on what they are looking for.

Some examples of prompts that you can use are:

• Find the average of Column B
• List unique values from this range
• Format cells with conditional rules
• Summarize the data in rows 5-50

Experimenting with natural language queries is the best way to explore how to use Copilot prompts in Excel efficiently.

Note: For Copilot to interpret and work with your data in Excel, it should be in Table format.

Step 1: Click on the Copilot icon to open the preview text box on the right of the screen.

Step 2: You may then use Copilot’s suggestions in the chat box or write your own to generate the results that you are looking for.

When using Copilot in Excel, you can create formulae, highlight and format cells, summarize the data or even analyze the data. Below are some examples on how to use Copilot in Excel to do just those.

How to create formulae in Excel with Copilot?

Formulas are central to what makes Excel unique and powerful. And thanks to AI-driven power like Copilot, you can streamline your tasks easily and quickly.

You can click on the “Create Formulas” button on the Chat Box of Copilot to allow the software to suggest pertinent formulas that apply to your information.

For example, I have a set of data below that shows the total tips that employees received for a month. By clicking on the “Create Formulas” button, Copilot suggests me to add a column titled “Full Name” that groups both the First Name and Last Name of the employees. And to make things easy, you can click on “Insert Column” for it to automatically generate.

You can also choose to create your own formula and apply it in the Copilot’s chat box for it to generate the results.

From the same example as above, let’s say I now have the monthly salary of each person, and I want to know the total income that each employee received. I just need to type that request into the Copilot chat box in Excel, and it will suggest the formula as well as have the “Insert Column” function that I can use to automatically add the new column.

How to highlight and format cells in Excel using Copilot?

With Copilot in Excel, you can also highlight, and format cells based on your specific needs.

You can again choose the Copilot’s suggestion in Excel to apply color and formatting or write your own prompt as needed.

For instance, I asked Copilot to highlight the highest number in the “Total Income” column, and clicked on “Apply” once it gave me the formula.

You can also choose to rearrange your data based on how you wish to view it. For instance, you can sort the numbers columns from smallest to biggest, or the names columns in alphabetical order.

How to summarize data with Copilot in Excel?

Another crucial option that makes Copilot essential in Excel is the summarize data feature. If you have been wondering how to use Copilot in Excel to get an overview of your entire data, then this option is the one you need.

You can again choose the “Understand” option of Copilot in Excel so that the AI suggest you the most appropriate way to interpret your data.

Based on the data that I already had, Copilot create a PivotTable that summarizes all information into a single table. This is a great way to have a complete overview of the tips, salaries and total income based on each employee and globally.

You can also write your own prompt to guide Copilot on the type of data that you wish to see, and in which format. For instance, I asked it to show me only the employees and salary information, to which it generated a new table.

This functionality is especially useful for presentations, reports, or gaining a quick overview of large datasets.

Copilot Excel benefits

Using Copilot in Excel offers numerous advantages:

• Time-saving automation: Automates repetitive tasks, allowing users to focus on strategic activities that add value to their work. For instance, creating daily reports or updating recurring spreadsheets becomes seamless with just a few prompts.
• Error reduction: Minimizes human error in formula creation and data analysis by ensuring calculations are accurate and consistently applied. This is especially critical for financial models and large datasets.
• Enhanced collaboration: Facilitates teamwork by generating dynamic insights that are easy for all team members to understand. With real-time sharing and AI-driven suggestions, Copilot bridges the gap between technical and non-technical users.
• Simplified workflows: Reduces the complexity of advanced Excel functionalities, such as pivot tables or conditional formatting, by making them accessible through natural language commands. This allows users of all skill levels to achieve professional-grade results.

Understanding these benefits reinforces why learning how to use Copilot in Excel is essential for businesses and individuals alike. Whether you're preparing financial forecasts, creating academic research reports, or managing organizational data, Copilot ensures efficiency, accuracy, and enhanced productivity.

Limitations when it comes to how to use Copilot in Excel

Despite its capabilities, Copilot in Excel has limitations, but understanding them and exploring solutions can enhance its usability:

• Internet dependency: Copilot requires an active internet connection for optimal performance. This can be inconvenient for users in areas with limited connectivity. Ensure you have a stable connection before starting critical tasks or use offline features of Excel for interim work until connectivity is restored.
• Language support: Copilot is limited to supported languages and regions, which may restrict accessibility for some users.
• Ambiguous queries: Copilot may struggle with highly specific or poorly structured prompts, leading to incomplete or incorrect results. Refine your prompts to be as clear and concise as possible. For example, instead of asking "Analyze data," specify "Analyze Q3 sales data trends."

Acknowledging these limitations ensures realistic expectations when using Copilot in Excel. By addressing these challenges proactively, users can maximize its strengths and create a smoother workflow experience.

Tips on how to use Copilot in Excel

Now that we have summed up how to use Copilot in Excel, as well as looked at some of its benefits and limitations, below are some tips that you can adopt when using Copilot in Excel.

Tip 1: You can always undo any action by pressing the “Undo” button after each Copilot generated result. This ensures that you can always go back to your initial dataset, without fearing that you have alter or delete anything.

Tip 2: Copilot has this cool feature called “Show explanation”. It is very useful if you want to understand the formula that Copilot used in Excel, and how it came to the result and deduction.

Tip 3: Refine your prompts to make them as concise and accurate as possible. Avoid being too generic in your prompts and you shall obtain pertinent results that will add value to your dataset.

Tip 4: As with any AI-generated content, it is essential that you review and verify any content that has been generated by Copilot in Excel.

Want to know more about how to use Copilot in Excel? Want to have professional guidance and services when it comes to AI and Microsoft products? Our team at Gestisoft are all ears for you and will be happy to assist you in your journey. Contact us today and let’s book a free consultation based on your specific business needs.

Further reading:

7 Steps to Writing an Effective Copilot Prompt

How to Use Copilot Across Different Microsoft Apps and Platforms?

Top 5 Microsoft Copilot features that transform business productivity

How to automate repetitive tasks with Copilot in Dynamics 365?

Your Ultimate Guide on how to use Copilot in Word