Here are some of the best and amazing things the Microsoft Copilot in Excel can do for you in seconds!

With Microsoft Copilot now integrated across Microsoft 365 apps, especially Excel, working with spreadsheets has become smoother and more enjoyable than ever. You no longer need to be an Excel expert because Copilot already understands the tool inside out.

Just subscribe to a compatible Microsoft 365 plan along with the Microsoft Copilot Pro add-on. You’ll be on your way to mastering Excel with ease.

Already set up? Great. Follow along as I present to you the smartest ways to get the most out of Copilot in Excel. Let’s get started!

What Copilot in Excel Can Really Do?

Microsoft Copilot in Excel is simply what you have so far been using as the independent Copilot app.

In Excel, it has become more powerful since it can read the Excel datasets in a worksheet and suggest data analytics and visualization options. Here’s how it can make your spreadsheet work easier:

• Data Analysis: Copilot scans your tables and highlights key insights right away. You can then ask follow-up questions to explore the data further.
• Modeling: While working on financial models, you can test different scenarios by asking “what-if” questions like “model how a change to A affects B.”
• Visualizations: Let AI help you build clear, easy-to-understand charts and graphs that bring your data to life.
• Writing Formulas: Struggling with formulas? Copilot can generate the right ones for your calculations, making even complex logic more approachable.
• Organizing and Cleaning Data: Copilot quickly tidies up messy data. It can remove extra spaces, combine column values, flag duplicates, apply filters, and more.
• Suggestions: Get smart recommendations for formulas, functions, and next steps based on what you’re working on.
• Insights: Discover patterns, trends, outliers, and relationships within your data that you might have missed.
• Automating Tasks: Need to speed things up? Copilot can write VBA code to automate repetitive steps, saving you valuable time.

Here are the mandatory requirements to use this feature:

• The dataset you want to analyze must be formatted as an official Excel Table.
• The worksheet must either have AutoSave turned on or be opened in Excel for the web to ensure Copilot can work properly.
• Copilot is only available as part of Microsoft 365 and requires a separate Copilot subscription in addition to your regular plan.
• Copilot features are supported only in Excel for Microsoft 365 and aren’t available in older or standalone versions of Excel.

Now, explore below some functional and useful activities Copilot in Excel can accomplish for you automatically:

Explore Expert Prompt Suggestions

The Copilot in Excel comes with automatic prompt suggestions. In reality, the generative AI scans through the entire dataset in the active worksheet and suggests some prompts that you can enter in its texting interface for execution.

So, you don’t need to be well-versed in prompting generative AI tools. The Copilot service will itself walk you through.

However, you can refine, fine-tune, or customize the default prompts to make those better and accurate for data analytics and visualization purposes.

Convert the source dataset to an Excel table by selecting the whole dataset.

To do this, select the first cell of the source dataset and press Ctrl + Shift + Down Arrow to select all the rows in the first column until the last row.

Now, press Ctrl + Shift + Right Arrow to select all the columns to the right of the dataset until columns with real values exist.

Press Ctrl + T to convert the selected dataset to a table. Click OK on the Create Table dialog box.

Now, click on the Copilot app icon in the top right corner of the Excel Home tab.

The Copilot navigation panel will open on the right side.

Select the entire table by clicking on the Name box in the top left corner of the Excel worksheet and choosing the table you just created, which should be Table1.

The Copilot in the Excel navigation panel will show multiple options, like Create, Edit, and Understand.

Go to the bottom of the Copilot sidebar to find the Prompts card. Here, you’ll find automatically created prompts relevant to the selected data table under the following broader categories:

• Create
• Understand
• Edit
• Ask
• View more prompts

Hover your mouse cursor over any of these categories to open a right-side overflow menu. From there, you can now choose specific Copilot prompts that have been tailor-made just for the selected data table.

You can use these prompts as is or customize them for better results.

Automatically Add Formula Columns

Excel Copilot makes it easy to create formula columns even if you’re not confident writing formulas yourself. This feature uses AI to interpret your active worksheet’s datasets and then builds the appropriate formula.

It’s especially helpful if you’re a beginner-level Excel user or are working with complex spreadsheets, as it explains what the formula does and allows you to preview results before inserting them.

Technically, Copilot leverages machine learning models to understand the intent of the dataset and convert it into dynamic Excel formula syntax.

Let me show you how below in step-by-step:

Convert the source dataset to an Excel table and click on the Copilot icon. It’s usually found in the Home tab or as a sidebar if already enabled.

In the Copilot pane, locate and click Add formula columns to begin the process of creating a new calculated column.

You can either select one of the suggested prompts shown by Copilot (e.g., “Calculate sales tax”) or type your own description, such as “Calculate the difference between column B and column C.”

Copilot will analyze your prompt, create a relevant formula based on your dataset, and display it along with a brief explanation of what the formula does.

To understand the logic, click Explain formula, and Copilot will provide a breakdown of how the formula works.

To preview the results without committing, hover your mouse over the Insert formula button to see sample outputs.

To insert the formula into your sheet, simply click the Insert formula button, and Copilot will apply the new column directly.

Insert Multiple Formula Columns in One Prompt

Copilot can also insert multiple calculated columns into the source dataset with a single prompt. For example, you’ve got a dataset containing full names.

You wish to split the full name column into the First and Last Names columns. You can use Copilot in Excel to accomplish this by simply writing a small prompt.

Let me walk you through the exact process below in simple steps:

Select the source dataset, which must be in the Excel table format.

Click on the Copilot icon in the top right corner of the Home tab. If the tool is already open, look for the controls in the navigation panel on the right side.

Click on the Copilot messaging interface at the bottom of the Copilot navigation panel and type in your prompt. You can use the following prompt if you wish:

Read the source column of full names and split that into two columns of First and Last Names. Keep the original column as is and create two ne columns. Don’r replace the source column.

Copilot will populate the logic for two columns and present the formula it plans to use.

You can either manually copy and paste the formulas in the blank column to the right side of the existing table or column.

Alternatively, you can simply click on the Insert columns button to instruct Copilot to add the two new columns: First Names and Last Names.

That’s it! You’ve successfully added two new calculated columns in the existing data table using the Excel Copilot AI tool.

Use Conditional Formatting Without Learning Its Steps

The Conditional Formatting tool can be really confusing to you if you’re just learning Excel. And even if you’re an expert in Excel, it often takes 10 to 15 minutes to figure out the accurate formula to set up the right Conditional Formatting rule you need.

Instead, you can simply allow Copilot to go through your source dataset and ask the generative AI tool to conditionally format cells in the source data table.

You can accomplish this by instructing the AI in plain natural language, like English. No need to learn coding at all!

Suppose you wish to highlight all the values in a column in yellow. The logic behind highlighting would be the top values by percentage change in each row of the selected column. You can use the following prompt for this task:

Find out and highlight those cells in the selected column that show the top 5 percentage change in the whole column. Use the yellow shade to highlight the cells.

Alternatively, you can simply click on the Highlight command in the Copilot navigation panel. It should be shown in the top commands card, where Copilot suggests relevant data analytics functions automatically.

As you enter the prompt or click on the Highlight command, Copilot instantly finds and formats the cells in the source dataset that match the Conditional Formatting rule you’ve dictated.

You can now go to the Conditional Formatting command and click on the Manage Rules option to see the rule that Copilot has added itself.

This allows you to customize the rule if you need to make it better in the future.

Use the Sort & Filter Tool With Text Prompts

Another amazing thing you can do in Excel using the Copilot AI is sort and filter your dataset in various ways. It allows multiple column-based sorting and filtering as well.

Select the source cell range on the active worksheet and click on the Sort and Filter command in the Copilot navigation panel on the right side.

If it’s not there, select the dataset and then click on the Copilot command in the Home tab. It should be on the far right side of the ribbon menu.

When you click on the Sort and Filter button, Copilot shows you a few filtering options that are relevant to the dataset you have chosen.

You can implement any one of those filtering suggestions or enter a custom prompt in the Copilot texting interface. You can use the following prompt to try it out:

Filter the values in the Design column of the selected data table by values in the %Change column that are negative.

Copilot will immediately filter the selected dataset.

Automatically Analyze Datasets With Copilot

So far, I’ve shown how Copilot can process small datasets in Excel to create data insights. Let’s find out how Copilot can handle a large dataset and provide a comprehensive data analytics report that you can present at work.

Convert a large dataset into an Excel table and select the whole data table using the name box in the top left corner of the worksheet.

Launch Copilot from the Home tab, and a left-side navigation panel will open on the right side.

There, you should see the Analyze command. Click on that.

If you don’t see this command by default, click on the Copilot texting interface at the bottom. Enter the following prompt:

Show data insights from the whole dataset in the active worksheet. The data analytics insights should consist of PivotTables and PivotCharts.

Copilot will analyze the input dataset and show a comprehensive, ready-to-present data analyst report.

You can further extend the analysis by selecting an object on the report and sending new prompts to explain the objects, like a PivotTable or PivotChart, or customizing to remove specific data and add new ones.

Clean Data With Copilot

Cleaning data means fixing issues like duplicates, extra spaces, inconsistent formats, or blank rows that can lead to errors in analysis. These problems make formulas break and charts misleading.

With Copilot in Excel, you can clean up your data quickly by asking it to remove duplicates, merge columns, fix formats, and more.

It understands your context and handles the tedious cleanup for you, so you can focus on insights instead of formatting.

Suppose there’s a timesheet where the joining and leaving times are added in single columns in text format. You can subject this data to further calculations without cleaning it.

You can convert the raw data into a table and activate Copilot.

Now, go to the Copilot texting interface on the right side navigation panel and type in the following prompt:

Create two new columns for Join and Leave Time columns with calculated columns containing a formula. The formula must convert the text values in the Join and Leave Time columns into date-time formats.

As you punch in this prompt and hit the Enter key, Copilot inserts two new calculated columns with proper date and time formatting for the selected dataset.

You can now easily use these calculated columns in further calculations.

Perform Advanced-Level Text Analysis Using Copilot

Copilot in Excel can also analyze text values and produce insights that you can use in your project, like web scraping, digital marketing, audience analysis, etc. Let me show you how below:

Get a Quick Summary and Key Themes

Copilot can scan your text data in minutes and give you a brief summary along with a list of key themes. You can also ask it to adjust the summary’s length, tone, or focus based on your needs.

Analyze Sentiment in Your Data

Copilot can detect the emotions behind your text, positive, negative, or neutral, and give you a summary of the overall sentiment.

You can also request charts or PivotTables to explore common issues or spot trends more easily.

View Source Text for Each Insight

Copilot shows which parts of your data support the analysis results, so you can quickly verify the findings without searching manually.

Add Sentiment or Theme Labels as a New Column

After generating themes or sentiment insights, Copilot can add a new column to your data with labels for each row, making it easier to sort or filter by topic or emotion.

📝 Note: Text analysis currently works best for datasets with up to 3,000 rows. Larger datasets will be supported soon. Copilot also understands multiple languages, and no translation is needed.

Conclusions

So these are some of the amazing and quick data analyses you can do using the Copilot AI in Excel 365.

Since this is a generative AI with a real-time texting interface, just like ChatGPT, you can use your own imagination to create some better prompts, too.

Don’t forget to share your suggestions below on using the Copilot AI in Excel.