Sample Copilot Prompts for Excel 365

Excel's Copilot redefines how users analyze and visualize data, making complex tasks simpler and more efficient. This AI-powered tool enables users to generate data summaries, create stunning visualizations, and perform predictive analytics with just a few prompts. Copilot can provide the insight you need to spot sales trends, forecast future performance, and transform text data into actionable insights — all within Excel's familiar interface.

With features like natural language commands, Python integration, and dynamic updates, Copilot can guide you to make data-driven decisions instead of relying on guesswork.

Once you've added Copilot to your Microsoft 365 subscription, you're almost ready to go. Below, I'll share some sample prompts that can transform your Excel workflows. Buckle up! It's time to maximize your efficiency and uncover valuable insights!

How to write a Copilot prompt

AI prompts are instructions or questions you use to generate the desired output. The best prompts contain the following elements:

1. what you want
2. why you want it
3. what the output should look or sound like
4. whether you have examples to train the tool you're using

However, it is quite common to provide only two of the above and still achieve excellent results.

Sample Copilot prompts in Excel

Below are practical examples and use cases to demonstrate how Copilot can enhance efficiency and integrate seamlessly with other Microsoft 365 apps and services.

1. Split columns with ease

With just a single prompt, Copilot can split data from one column into multiple columns. This feature saves time when working with datasets that need reformatting, such as separating first and last names or extracting dates from timestamps.

Let's open the FSales sheet in our practice exercise and ensure that the data is formatted as a table, with AutoSave enabled. Next, let's use the following prompt to split the Products column into Product and Brand:

Split the data in the Products column into two new columns: 'Product' and 'Brand'. Use the delimiter ',' to place the text before the comma in the Product column and the text after the comma in the Brand column. Trim any extra spaces around the names.

Copilot in Excel analyzes the data in the Products column and suggests adding two new calculated columns. These columns will extract the Product and Brand by splitting the original content at the comma delimiter.

When hovering the mouse cursor over the Insert columns button in the Copilot pane, a preview appears, showing how the new column formulas will be inserted. The preview suggests that it is working as intended.

By clicking the Insert Columns button, the proposed change will be applied, adding two new columns with calculated formulas that separate the Product and Brand names, delivering the result I wanted!

2. Generate formulas for unique values

If you need to calculate distinct values from a list, Copilot can generate the formula for you. Simply describe what you need, and Copilot will write and apply the necessary formula to count or list distinct entries. This is especially useful when working with large datasets where manual formula creation could be time-consuming.

We can calculate the unique count of products using the FSales Excel table. To do this, we'll apply the following prompt. It's essential to understand the distinction between "distinct" and "unique" in such scenarios.

Find the total number of unique products in the fsales transactional table.

To determine the unique number of products, Copilot used the following formula:

=ROWS(UNIQUE(fsales[Products]))

We can request Copilot to provide an explanation of how this formula works. Let's enter the following prompt:

Can you explain the formula?

3. Data visualization and insights

Copilot can automatically create insightful visualizations based on your data, such as PivotTables, charts, or graphs. It analyzes your dataset, recommends the best visualization type (like bar charts or line graphs), and generates the visual for you. For example, if you're tracking sales trends, Copilot can quickly visualize sales growth over time, making it easier to identify key patterns.

Excel's Copilot can analyze data, generate charts, organize information, and provide high-level insights, among other features!

Let's input the following prompt to showcase the insights from our dataset.

Show data insights

The following insights are displayed:

Copilot conducted an analysis of purchasing costs by shipping date. We can add this analysis to a new sheet by clicking the "Add to a new sheet" button or request another analysis by clicking the "Can I see another insight?" button.

Let's select the "Can I see another insight?" button.

This time, Copilot generated an analysis of the amount by payment type.

If these analyses aren't what we want to display, we can aim to show sales by year and sales by brand. To achieve these analyses, let's input the following prompt:

Show the amount only by year and brand in table

We can explore additional questions for analysis. For example, let’s Retrieve the top 5 products based on sales. To do this, let’s enter the following prompt:

Retrieve the top 5 products based on amount and display in column chart

4. Conditional formatting, performing date calculations, and text processing

Copilot can simplify tasks like applying conditional formatting rules to highlight key information (e.g., marking overdue payments or top performers) and performing date-based calculations, such as calculating time intervals or tracking project deadlines.

Additionally, Copilot can work with text data, transforming it into a structured format or performing operations like date calculations, helping you process data more efficiently.

Here, I'll show how to highlight cells that meet specific criteria. Our goal is to highlight the cells in the Amount column that are greater than the average value.

To achieve this, let's input the following prompt:

Highlight the cells in the Amount column in red if their values exceed the average of the Amount column.

Let’s click on “Suggest Conditional Formatting”. We have a few suggestions here. Now, let's modify our prompt to the following:

Highlight cells greater than average in column Amount

Copilot offers us the following:

Let's agree by clicking the Apply button.

Now, I want to add Quarter and Year columns to my fsales table using the Date column. Let's enter the following prompts to do this:

Add columns for the quarter and year of the date

The following result is returned:

Click on "Insert Columns" to display the following result.

I will use the dstores sheet for text processing. I want to clean up the Area sq.m. column by removing the metric units to enable correct sorting.

Let’s input the following prompt:

Add a new column for Area sq.m., converting it to numeric values and excluding the text inside parentheses.

The following result is returned:

Let's click the "Insert Columns" button. The following output is shown.

Hopefully, this motivates you to start building a strong foundation in Excel skills while giving you confidence, knowing that Copilot can assist in bridging knowledge or skill gaps.

5. Leveraging Python for advanced analytics

One of the most advanced features of Copilot is its integration with Python, allowing for powerful data analysis and visualization that goes beyond Excel’s standard tools. You can describe your analysis in plain language, and Copilot will automatically generate and execute Python code within Excel, offering advanced visuals like heatmaps and predictive analytics. This feature is particularly useful for users who want to access the power of Python without needing to be proficient in the language.

For a demo showcasing the integration with the Copilot feature, I'll use the Excel sheet titled "GDP Growth Rates by Countries and Years." The dataset contains country codes along with GDP growth rates for various countries from 2013 to 2023. I aim to present this data in four columns: Countries, Country Codes, Years, and Growth Rates (dataset sourced from the World Bank's open data platform).

Given that the GDPGrowthRates DataFrame is already loaded, we would like to restructure it by unpivoting the columns representing years (2013 to 2023). The data is to be presented with the following columns: Country Name, Country Code, Year, Growth Rate.

We enter the following prompt:

Given that the GDPGrowthRates DataFrame is already loaded, could you help restructure it by unpivoting the columns representing years (2013 to 2023)? The transformed DataFrame should follow this format: Country Name, Country Code, Year, and Growth Rate.

Copilot instructs us to enter the following Python code:

import pandas as pd

# Assuming df is your DataFrame

df = pd.melt(df, id_vars=["Country Name", "Country Code"], 

var_name="Year", value_name="Growth Rate")

We can either navigate to Formulas and select Insert Python or simply type “=PY”, which initiates a Python formula. After pressing Tab, I'll enter the Python environment.

Let's assume “df” is our DataFrame, containing the data from the GDPGrowthRates table. I’ll press Shift + Enter to move to a new line, then use Ctrl + V to paste the code for melting (or unpivoting) the DataFrame. To execute the code, I'll press Ctrl + Enter, which returns a DataFrame as expected.

Our goal is to have the values spill directly into Excel cells, so we need to convert the Python output into Excel values. It seems we're almost there, with the country, year, and retirement age displayed.

However, some #NUM! errors are showing up.

Let's switch back to Copilot and ask how to remove rows with blank values from a DataFrame. We'll enter the following prompt:

How can I remove blank values in a DataFrame?

After pressing Enter, it seems we’ve received a Python-based solution.

Let’s copy “.dropna()” and incorporate it into the formula.

It seems an index column was added, but that’s fine—we’ve got the exact result we were aiming for.

---

Benefits of using Copilot to identify insights in Excel

What are the advantages of using Copilot in Excel? For one thing, it's easier for users to uncover meaningful insights from their data, enabling faster and more informed decision-making. With AI-powered features, Copilot simplifies the process of analyzing complex datasets, delivering actionable insights with minimal effort. In summary, Copilot offers the following:

1. Quick data summaries and pattern identification

Copilot can instantly generate summaries of your data, identifying key patterns and trends with a simple natural language prompt. For example, users can request a quick summary of sales performance, and Copilot will highlight top-performing products or identify seasonal trends. This helps users derive insights in seconds, eliminating the need for manual analysis.

2. Easy data visualization 

Copilot enhances decision-making by suggesting the best visual formats, such as bar charts, line graphs, or PivotTables, to represent insights effectively. By automatically applying filters, layouts, and fields, Copilot ensures that users get the most relevant visualizations to communicate their findings. This feature is especially useful for professionals needing to present insights to stakeholders clearly and efficiently.

3. Predictive analysis for forecasting

Through integration with Excel’s predictive tools and Python libraries, Copilot supports forecasting and trend analysis. Users can request predictions—such as future sales trends or customer churn rates—directly in the spreadsheet. With Python-powered models, Copilot can perform advanced analytics, including regression analysis and time-series forecasting, making complex predictive tasks more accessible.

4. Sentiment analysis and text-based insights

Copilot isn’t limited to numerical data—it also helps analyze text-based data, transforming product reviews, survey responses, or feedback into actionable insights. With features like sentiment analysis and keyword extraction, users can quickly understand customer sentiment and identify emerging themes, enabling proactive responses.

5. Dynamic and refreshable insights

One of Copilot’s strengths is the ability to generate refreshable analyses. When new data is added or existing datasets are updated, Copilot can re-run the analysis and provide updated insights automatically. This ensures that users always work with the latest data, saving time and reducing manual effort.

By leveraging Copilot’s visualization tools, predictive models, and text analysis capabilities, users can quickly uncover valuable insights that drive business outcomes. Whether identifying sales trends, forecasting future performance, or summarizing customer sentiment, Copilot enables efficient, data-driven decision-making directly within Excel’s familiar environment.

Ready to unlock the full power of Copilot in Excel?

Enroll in our Copilot for Microsoft 365 course and take the next step toward mastering this innovative tool. 

Get skilled-up in Copilot

Learn how to use Microsoft's newest sidekick in short, easy-to-understand lessons

Start learning