A Complete Guide to use Copilot in Excel + Free Crash Course

You know what, there are a lot of people, even Excel experts, out there who are a little bit skeptical about using Copilot in Excel or any other Microsoft Office application.

But for me, Copilot is not just an application or AI chatbot that provides answers; it’s more like an integration that helps me reclaim my time.

That is why I am ready to bet on it, at least for the next five years. Given the rapid pace of change, every Excel user I have spoken to wants to save time and have an AI assistant that can help them enhance their workflow.

And that is the reason I’m creating this in-depth guide for anyone who wants to start using Copilot in Excel, as well as in Microsoft Word and PowerPoint.

In this guide, I will walk you through seven different lessons or tasks that you can complete with Copilot in Excel, Word, and PowerPoint.

Before we begin, I want to assure you that I will be honest throughout this guide.

I will explain the benefits of the copilot, its application in Microsoft Excel, and share some of the improvements required in the future to make it more robust and adaptable for all Excel users.

First, you need to understand what Copilot in Excel is and how it works.

Enable Co-Pilot in Excel

To use Copilot, no additional steps are required. Once you subscribe to the copilot, it will automatically show up in your Excel.

As I mentioned earlier, once you subscribe to Copilot, you need to update your Office 365 application, and the Copilot icon will be added to the right side of your Home tab.

• Windows: File > Account > Update Options > Update Now
• Mac: Help > Check for Updates

I have spent the last 6 months extensively using Copilot to try to get all the activities in Excel. And that’s why I will not only share with you all the good things, but also my notes on the errors and problems I have faced while using the copilot. The second thing you need to do before using Copilot is to save your workbook in OneDrive to enable AutoSave.

Even when you have a Copilot subscription and the Copilot button is on your Home tab, clicking it checks if the AutoSave option is turned on or not.

And if the autosave save option is not turned on, you need to save your Excel workbook into OneDrive to turn on the autosave option, and only after that, you can have the chatbot copilot pane add the right side of your window.

Copilot’s Interface

Copilot’s interface is straightforward to use. Although it changes almost every month with updates, it remains overall easy to use, much like a messaging application we use in our day-to-day life.

Once you open the Copilot, you will have the Copilot panel on the right side of your Excel window. In this panel, please enter your prompt, as this is where we will receive all responses from Copilot.

It also saves all the charts you have with Copilot, as well as all the responses you have received from Copilot, so that you can access them in the future.

When you open the Copilot panel for the first time, it also gives you a few quick prompts that you can use to start with, and the best part is that the more you work with Copilot, the more it understands your data, and it will suggest more quick prompts along the way.

If you are working with sales data, it will provide you with quick prompts to analyze, extract specific data, create pivot tables, and charts.

7 Ways Copilot Can Be a Game Changer for You in Excel

1. Generating Formulas with Co-Pilot

Now, in this section of the guide, we will generate a range of formulas, from basic to advanced, using Copilot.

Using these links, you can access all the prompts and sample data I used in this guide to utilize Copilot, allowing you to follow along with me.

Now, let’s write your first prompt.

A. Combine the first and last Names

In the sales data, you can see that we have two columns, one for the first name and one for the last name. Now, I want to combine the first and last names into a single column.

So, what I’ll do is open the copilot and enter my prompt that says, “Combine first and the last name”, and I’ll hit enter to see what I’m going to get from Copilot in return.

You can see here that it’s a simple, to-the-point prompt that asks you to combine the first and last names from the data. And this is what I got from Copilot in response to this prompt.

As you can see in the response from Copilot above, I have two different options to use the formula suggested by Copilot. With the first option I have, I can copy the formula suggested by Copilot and then paste it into a new column.

In the second option, I have a button labelled “Insert Column” that I can click to insert a new column with the formula suggested by Copilot.

Now, when you click on this insert column button, it will add a new column at the very end of your data by expanding it. Here’s my new column, which combines the first and last names.

When combining names into a single column, the co-pilot can read the pattern in the data, identify first and last names, and combine them as needed. That’s what Copilot told me about how it can understand the data: “I can identify the first and last names in the data based on the context and patterns in the dataset. For example, in the provided data, the first two columns contain names that follow a common pattern of first names and last names. Even if the headers are not explicitly labelled, the structure and content of the data can help determine which columns represent first and last names.”

Now, let’s use a different prompt to combine the first and last name in a different way.

This time, in addition to incorporating the first and last names, I want to include a delimiter between them, which is a comma and a space.

For this, my prompt will be “Combine last and first name using a comma and a space between”.

Now, let me add this to the Copilot chat bar and hit Enter. See what I get in return from Copilot.

The response I received from Copilot is that I have again been given a formula to copy or an insert button to insert the formula column at the end of the data.

Now you can see in this response, I have got a simple concatenate formula that combines the use of the last name.

Then I have “&” and then a comma (,) and space ( ) with double quotation marks, and then again, an ampersand, and in the end, I have a reference to the first name column.

B. Extract Year, Month of Joining, and Years of Service

Now, let’s move to the employee data and try to add a few formula columns there with the help of Copilot.

In this dataset, I have a ‘Date of Joining’ column for all employees, and I want to extract the year of joining from this column. I’ll use a simple prompt that says, “Extract Year of Joining.”

In response to my prompt, the copilot has returned a simple formula using the YEAR function that extracts the year from the date of joining column.

And again, I have two options to use this formula: one is to simply copy it, and the second is to use the Insert Column button.

In the same way, I want to extract the month of joining, and for this, again, I’m going to use a to-the-point prompt that says, “Extract Month Name of Joining (Small Abbreviation)”.

In this prompt, I also mentioned that I want to use small abbreviations for the names of the months. Now, let me enter this prompt to see what I get in return from Copilot.

And this is what I received from Copilot in response.

Copilot always adds a new formula column at the end of the dataset. However, if you specify a location, it can insert the column there, though this may not be the best approach for maintaining the data structure.

It is always better to have a concise and clear prompt to generate a formula from Copilot.

Similarly, you can also extract the month of joining from the date of joining. For this, I will use the prompt that says, “Extract Month Name of Joining (Small Abbreviation)”.

In this prompt, I have used the word small abbreviation to get the small name of the month, so I want months as Jan, Feb, & Mar.

And the moment I hit enter, it returns a formula that uses the text function to extract the month from the date of joining.

Then, I need to click the Insert button to insert a new formula column at the end of the data, which includes the month of joining.

When extracting the year and month from a date, that date must be an actual date.

If you already know the function you want to use, mention it in your prompt to Copilot. It will then generate the formula using that specific function in its response.

Now, moving ahead, I want to calculate the years of service for every employee. We have the date of joining, and I want Copilot to return a formula that can calculate the year of service.

For this, I am using a crisp and straightforward prompt that says, “Calculate Years of Service”.

And when I enter my prompt, within a few seconds, Copilot returns a formula that uses the date from the ‘Date of Joining’ column to calculate the total number of years each person has worked, in other words, their total years of service.

C. Remove Hyphens from the Phone Numbers

In my sales data, I have two columns with phone numbers, and each number contains a hyphen. I want to remove those hyphens using a formula.

So, I go to Copilot and simply type my prompt: “Phone Number without Hyphens”.

Here, I’m not specifying the column names or even mentioning that there are two columns; I’m simply making a plain-English request. So, let’s see how it goes.

This time, Copilot provides me with two different formulas to use, and as you can see here, it’s smart enough to detect that I have two columns in my data with phone numbers that contain hyphens.

Now, I have two options: I can copy each formula and insert it one by one to create new columns without hyphens.

Or I can click on ‘Insert’, and Copilot will automatically add new columns at the end of the table with the cleaned phone numbers, hyphens removed.

To remove a hyphen from a column, Co-Pilot can’t do it on the original column itself, but it can insert a new column and use a formula to remove hyphens. Or even sometimes, it will only share the formula with you that you can use to insert a new column.

2. Apply Conditional Formatting using Copilot

Applying Conditional Formatting with Copilot is just as powerful as using it to generate formulas. With a single prompt, Copilot can understand the pattern or condition you want, scan your data, and instantly suggest the right formatting rule.

It identifies the best logic to use, whether it’s highlighting top values, applying color scales, or marking duplicates, and sets everything up for you. Click the Apply button.

In this part of the tutorial, you’ll learn how to use Copilot to apply Conditional Formatting and quickly analyze your data with just a few words.

A. Highlight by Employees with the Salary below 50000, Top 10, Below Average with Copilot

Now, let’s say you want to highlight all employees whose salary is above $ 50,000. For this, you need to enter a simple program that says, “Highlight all the Salaries that are below 50000”.

When I enter this prompt and then press Enter, Mobile takes a few seconds and then responds with an option that I can use to apply conditional formatting to highlight all the salary cells for employees whose salaries are below $50,000.

And then to apply this formatting, I just need to click on the apply button, and it will instantly highlight all the cells with the salary below 50,000.

The best part about using Copilot for conditional formatting is how easily you can undo the changes. Right after you apply the formatting, you’ll see the Undo button, just one click, and it instantly removes the formatting you just applied.

Now, let’s say you want to highlight all the salary cells with a different color instead of red, like we did in the previous example.

You can easily tweak your prompt to reflect that. For instance, you could say: “Apply green color to highlight cells in the Salary column where values are below 50000”.

Just change the color and condition as needed, and Copilot will do the rest.

If a column already has conditional formatting and you ask Copilot to apply a new rule, it will add the new formatting on top of the existing one, so the previous rule stays unless you remove it yourself or ask Copilot to remove it first.

Let’s say you want to highlight the salary cells for the top 10 employees in your data.

The idea is to identify those with the highest salaries visually. Type in a prompt like “Highlight top 10 employees with the highest salary” and press Enter. Copilot will take care of the rest.

And then a prompt with “Highlight the employee with the highest salary in each department” to highlight the employee from each department with the highest salary, or “Highlight the employee with the lowest salary in each department” for employees with the lowest salary.

3. Sort and Filter Data using Copilot in Excel

Just like that, you can also use Copilot to sort and filter your data. All you need to do is enter your prompt, and Copilot will sort or filter the data exactly the way you want.

A. Sort Data in Ascending and Descending Order

Let’s say you want to sort your employee data based on the date of joining. In that case, you need to enter the prompt, “Sort the data in descending order by the ‘date of joining’ column”. And hit Enter to let Copilots view the data using the column date of joining.

As you can see in the example above, Copilot responded with a button to apply sorting to the column in the table. Once I clicked the ‘Apply’ button, the data was instantly sorted within seconds.

And if I want to sort my data in ascending order, I just need to use a prompt like: ‘Sort the data in ascending order by the Date of Joining column.’

This is the exact opposite of the previous example, where I used the word ‘descending’ to instruct Copilot to sort the data from newest to oldest.

This time, by using ‘ascending,’ I’m telling Copilot to sort the data from oldest to newest.

Using the right terminology is crucial when working with Copilot in Excel. Words like “filter,” “show,” and “display” might feel similar, but Copilot treats them differently.

B. Filter the Data with Copilot

You can also filter data using Copilot. Just type your prompt, and within seconds, Copilot will show you a button to apply the filter based on the criteria you mentioned.

In the example below, I entered the prompt ‘Filter top 10 employees by salary,’ and Copilot responded with a button to apply that filter.

And when I click the Apply button, it instantly filters the top 10 employees based on their salary.

Similarly, if I want to filter sales data for two specific divisions, such as Midwest and South, I need to type the prompt ‘Filter data for South and Midwest’ and press Enter. Copilot will then show the Apply button to apply the filter.

And once you click the Apply button, it applies the filter to the Division column for South and Midwest.

4. Create a Pivot Table with Copilot in Excel

Creating a Pivot Table with Copilot is quite simple if you know how to structure your prompt. Before we dive into the steps, it’s important to understand that Copilot doesn’t require long, detailed prompts.

A short, well-structured prompt that conveys your intent is all you need to get the job done.

Let’s say you want to create a Pivot Table to show the total salary department-wise. In that case, instead of using a long or detailed prompt, you can type something like: Pivot Table to Show Total ‘Salary’ by ‘Department’.’

That’s clear, short, and gets the job done. Once you enter the prompt, Copilot takes a few seconds to analyze your data. Then, it responds with a preview table that resembles a PivotTable. Below that, you’ll see a button labelled “Add to a new sheet”.

When you click this button, Copilot automatically inserts the Pivot Table into a new worksheet.

In this example, we asked Copilot to show the total salary by department. But you can easily change the calculation.

For instance, if you want to see the average salary by department instead of the sum, modify your prompt to say ‘average’ instead of ‘sum.

As you can see here, when I entered the prompt using ‘average’ instead of ‘sum,’ Copilot responded with a new output showing the average salary by department, rather than the total.

Along with this response, I also got the ‘Add to a new sheet’ button, which I can use to insert the Pivot Table into a new worksheet.

Now, I want to create a Pivot Table that shows the count of employees by department. For this, I will use the following prompt: “Pivot Table to count of Employee ID by Department.”

Here, ‘Employee ID’ is the column I’m counting, and by adding ‘by Department,’ I’m instructing Copilot to group the count by each department. This will give me the total number of employees in each department.

So, as you’ve seen, creating a Pivot Table with Copilot is incredibly easy. All you need to do is enter a clear prompt, and within a few seconds, Copilot responds with a button to insert the Pivot Table exactly as requested.

When you ask Copilot to create a Pivot Table, you won’t always get a traditional Pivot Table in return. Sometimes, Copilot might generate a simple summary table instead, showing the information you asked for, just not in Pivot Table format.

Instead of using short and crisp prompts like we did in the earlier examples, you can also use a more detailed prompt to create a Pivot Table.

In a Pivot Table, there are four key fields: Rows, Columns, Values, and Filters. And within your prompt, you can specify exactly which column you want to place in each of these fields. This gives you more control over how the Pivot Table is structured.

Let’s say you want to create a Pivot Table that shows the count of employees based on department and employee status. This means the table should display the number of employees in each department who are Active, On Leave, or Resigned.

For this, we are going to use a prompt that says “Create a Pivot Table using the Employee Data. Place ‘Department’ in the Rows, and ‘Employment Status’ in the Columns, and the count of Employee ID in the Values”.

When I entered this prompt, you can see the response I got from Copilot: in the Rows field, it placed the department names; in the Columns field, it added the employee status values: Active, On Leave, and Resigned.

It also included a Grand Total column showing the total number of employees in each department, along with a Grand Total row that gives the overall count for each status.

5. Analyze Data using Copilot

In this section, we’ll learn how to analyze data using Copilot.

For example, if you want to find the five lowest-paid employees in the IT department, all you need to do is enter a simple prompt like: ‘Five lowest-paid employees in the IT department’ and hit Enter.

Copilot will then generate the analysis for you within seconds.

In the response from Copilot, along with the data showing the five lowest-paid employees in the IT department, I also received a button labelled “Add to a new sheet”.

When I clicked this button, Copilot extracted the actual rows for those employees from the main dataset and added them into a separate worksheet.

In the same way, you can ask Copilot to show the three highest-paid employees from the Sales department.

Once you enter the prompt, Copilot will return a summary table along with a button to ‘Add to a new sheet.’ Clicking this button will insert the actual data for those employees into a new worksheet.

This time, when I clicked the ‘Add to a new sheet’ button, instead of copying the complete data for those high-salary employees, Copilot inserted a small Pivot Table into the new sheet.

It applied a few filters to display only the names and salaries of the top employees from the Sales department.

Things to Take Care of While Analysing Data with Copilot

When analysing data with Copilot, it’s important to keep your prompts clear and specific. Use direct phrases like “Top 5 highest-paid employees in Marketing” to get accurate results. Always refer to columns using their exact headers from your data, such as Department, Salary, or Status.

You can add filters to your prompt, such as “in IT department” or “sales above 50,000,” to narrow down the results. Copilot may return either a summary table or a filtered set of rows, so be ready for both. Use the “Add to a new sheet” button to save the results in a new worksheet for further use.

If the output isn’t quite right, try rephrasing your prompt; sometimes, a minor tweak can make a big difference. Also, avoid overly complex or vague instructions; breaking your request into simpler parts helps Copilot deliver better responses.

6. Create Charts in Excel using Copilot

Now, in my opinion, creating a chart with Copilot is a little tricky. But once you learn how to create charts, you can save a lot of your time and increase your productivity.

Before creating a chart with Copilot, verify that the data you are using contains no blank cells or errors.

Additionally, your data should have clear and descriptive headers, and each column should be formatted correctly according to the type of data it contains.

If you look at the example above, you’ll see that I started the prompt with the word “visualize.”

When you want to create a chart with Copilot, you have two options: you can either ask Copilot to visualize your data and let it choose the most suitable chart, or you can directly request a specific chart type.

For example, “Create a line chart with this data.” But for Copilot to generate a specific chart, your data must be in the correct format.

In most cases, if you only have raw data and ask Copilot to create a chart, it will first build a Pivot Table and then generate a Pivot Chart based on that. So, you’ll end up with both a Pivot Table and a Pivot Chart.

If you want just a single chart without a Pivot Table, you’ll need to make sure your data is already structured in a way that can be used directly to create that chart.

Here’s another critical point: if you want to create a chart that can easily be created as a Pivot Chart, you can ask Copilot to generate that chart for you.

For example, if you want to create a column chart but only have raw data (not data already structured for charting), Copilot will take your request and respond with a button to insert the chart.

As I mentioned earlier, it creates a PivotTable first and then builds a PivotChart on top of it. That’s exactly what I’ve done in the example above, by just entering a simple prompt “Visualize Sum of ‘Salary’ by ‘Position’ using Column Chart”, Copilot generated a Pivot Table and a Pivot Chart based on my raw data.

As you’ve seen in the two examples above, I used the word “visualize” in my prompt. This tells Copilot that I want to create a chart to visually represent my data.

But you don’t always have to use the word “visualize”, you can also use the word “create”, which works just as well and conveys the same intent to Copilot.

Both words let Copilot know that you’re looking to create a chart from your data.

And that’s exactly why, in the example below, I used the word “create” in my prompt to request a bar chart showing the amount by division.

Since I wanted to visualize the sales data I have, I asked Copilot to create this chart, and it responded by generating a bar chart based on the divisions and their corresponding amounts.

Unlike other things we have created using Copilot, once you are satisfied with the response you have received from Copilot, you need to click the ‘Add to a new sheet’ button to transfer your chart, or I would say, pivot chart, to a new sheet.

Now, let’s say you want to create an age distribution chart from the data in your worksheet. To build this chart, you need to have the age calculated for each employee in the dataset.

As you can see, we have the Date of Birth for all employees, and from this, we can calculate their age. To do that, we’ll use Copilot to add a formula column.

The idea is to ask Copilot to insert a new column that calculates and displays the age of each employee based on their date of birth.

So first, I’m going to enter the prompt ‘Calculate age’ into Copilot. This will add a new Age column by calculating each employee’s age based on their Date of Birth.

Once you have the formula column with the age of all employees, the next step is to create an age distribution chart based on that data.

Now, I’m entering the prompt ‘Visualize the age distribution for the employees’ and hitting Enter to see what kind of response I get from Copilot.

Now, if you look at the example above, Copilot has responded with a chart, along with a button that adds it to the sheet.

When I clicked the button, the chart was inserted into the worksheet, a bar chart where each bar represents a five-year age range, providing a clear view of the age distribution among employees.

This chart is not a pivot chart; it’s a regular bar chart created using defined bin widths to group the ages into five-year ranges.

7. Clean Your Data using Copilot

Recently, Microsoft added a dedicated ‘Data’ option in Copilot specifically designed to help you clean your data.

The best part is that this option scans your data, identifies irregularities, and brings them to your attention without requiring you to review everything manually.

When you open a file, Copilot analyses the data and highlights any issues it detects, allowing you to review and fix them one by one, right from within Copilot.

As shown in the example above, I received a notification from ‘Clean Data with Copilot’ indicating that there are four suggestions to improve my dataset. It also included a button that I can click to view each of these suggestions in detail.

If you don’t see the notification, you can go to the Data tab and click on the ‘Clean Data’ button. This will prompt Copilot to scan your dataset, identify any irregularities, and then provide suggestions that you can review and fix, all within Copilot itself.

Once you click ‘Show Suggestions’ or go to ‘Clean Data’, all suggestions are displayed in a pane on the right side of your window. From there, you can view all the errors and irregularities listed.

Now you can correct them manually or use the Apply button in Copilot, which will correct those errors and irregularities for you.

As you can see in my example, it indicates that I have redundant spaces in my data, and when I click ‘Apply’, it removes all those spaces for me.

I can now correct all the irregularities and clean my data according to the suggestions from Copilot.