Getting Started with Excel Copilot

If you’ve ever wished Excel could just “do the work for you,” then Microsoft Copilot might be exactly what you need. This AI-powered assistant can generate formulas, analyze data, apply formatting, and even explain complex calculations—all with simple, natural-language commands.

Want to access Copilot?

Check out this Microsoft Office 365 Bundle which includes Copilot, Excel, Word and PowerPoint. It is a one year subscription for $99.99. Use our Amazon affiliate link to ensure you grab the correct license and to help support Excel University:

• Buy on Amazon: Office 365 with Copilot

Video

Step-by-step Guide

In this post, we’ll explore how to use Copilot for three essential Excel tasks:

1. Creating calculated columns
2. Understanding data through analysis and visualizations
3. Editing and applying formatting with ease

Let’s dive in and see how Copilot can transform the way we work in Excel!

1. Creating Calculated Columns with Copilot

One of the most powerful features of Copilot is its ability to generate calculated columns based on a simple prompt. Instead of manually writing formulas, you can just describe what you need, and Copilot does the rest.

Example 1: Calculating Variance in Budget vs. Actuals

Let’s say we have a table with Actual and Budget values, and we want to calculate the variance.

Here’s how we can do it:

Click Home > Copilot

Enable AutoSave (required for Copilot)

Type the prompt:

“Add a variance column.”

Copilot will suggest a formula (e.g., =Actual - Budget) and provide a preview.

Click Insert Column to apply it.

Since this is a formula-based column, it will automatically update when new data is added!

Example 2: Calculating Variance Percentage

Now, let’s add a variance percentage column:

• Use the prompt:
  • “Add a variance percent column.”
• Copilot suggests a formula like: =(Actual - Budget) / Budget
• Click Insert Column, and the formula will be applied throughout the table.

Example 3: Adding Sales Calculations

For a sales dataset with Quantity and Price, we can create an Extended Price column.

Type:

“Add an extended price column.”

Copilot suggests multiplying the quantity by the price

Click Insert Column to apply it.

We can also add tax and shipping calculations:

• “Add a tax column at 5%.”
• “Add a shipping column: $10 if quantity is 5 or more, otherwise free.”

Copilot understands the logic and applies the correct formulas.

Example 4: Extracting Data from Text

If we have a column containing full names and addresses, we can extract specific values.

Tell Copilot to “Extract the first name” and then Insert Column:

Copilot creates formula-based solutions, meaning new entries will automatically be processed.

2. Using Copilot to Understand Data

Copilot isn’t just for calculations—it can also summarize, visualize, and analyze your data.

Example 1: Total Revenue by Region

Let’s say we have a table with Transaction IDs, Regions, and Sales Amounts.

Prompt:

• “What is the total revenue for each region?”

Copilot generates:

• A preview and summary

Click Add to New Sheet to save it.

This is especially useful for users unfamiliar with PivotTables or charting features in Excel!

Example 2: Employee Count by Department

With an employee dataset containing IDs, Names, and Departments, we can count employees per department.

Prompt:

• “How many employees are in each department?”

Copilot creates a new sheet showing a distinct count for each department:

Example 3: Finding the Top-Selling Items

With a dataset of items, quantities, and prices, we can identify the top 3 best-selling products.

Prompt:

“What are the top three items in terms of sales quantity?”

Copilot generates:

Insert to new sheet:

We can continue dialoging with Copilot if we want to refine the analysis. For example, we can ask:

• “Add a margin column.”
• “Now, show the top three items by total margin.”

This allows for quick financial insights without manually setting up complex calculations!

3. Editing and Formatting with Copilot

Besides calculations and analysis, Copilot can also help with formatting and sorting.

Example 1: Highlighting the Top 3 Scores

Given a dataset of names and test scores, we can apply conditional formatting.

Prompt:

• “Highlight the top three scores.”

Copilot applies Conditional Formatting, dynamically updating when scores change.

Note: we could customize the formatting if desired:

• Open Manage Rules to adjust colors, thresholds, etc.

Example 2: Sorting and Filtering

Copilot can help filter data based on conditions.

• “Filter the table to show only scores greater than 50.”

Results:

While experienced users may prefer manual sorting and filtering, Copilot provides a great shortcut for those unfamiliar with Excel’s filter tools.

Example 3: Explaining Formulas

If you come across a complex formula you don’t understand, Copilot can break it down:

• Copy the formula (Ctrl + C)
• Prompt:
  • “Explain this formula” and then paste in the formula
• Copilot provides a step-by-step breakdown of each function and argument.

This is invaluable when working with formulas created by someone else!

Final Thoughts

Microsoft Copilot is a game-changer for Excel users of all skill levels. Whether you need help writing formulas, analyzing data, or applying formatting, Copilot can handle it with simple, natural-language commands.

The best part? It saves time and reduces manual effort, making Excel more accessible than ever.

Have you tried Copilot in Excel yet? Let me know in the comments how it’s working for you!

Want to see more Copilot tips? Let me know what you’d like to learn next!

Sample File

FAQ

1. Is Copilot available in all versions of Excel?

No, Copilot is a licensed feature and is not available in all Excel versions. Check if your version supports it under the Home tab.

2. Do I need AutoSave enabled to use Copilot?

Yes, AutoSave must be turned on for Copilot to function properly.

3. Can I undo actions performed by Copilot?

Yes! If you don’t like the result, you can undo any Copilot-generated action using the undo button.

4. Does Copilot replace PivotTables and formulas?

No, it simply makes them easier to create. You can always modify the output as needed.

5. Can Copilot create charts and graphs?

Yes! When analyzing data, Copilot often generates PivotTables and charts automatically.

6. What if Copilot gives me an incorrect formula?

You can either modify the formula manually or ask Copilot to refine it with a follow-up prompt.

7. Does Copilot work offline?

No, Copilot requires an internet connection since it uses cloud-based AI.

8. Can Copilot help with VBA macros?

Currently, Copilot focuses on formulas and standard Excel functions, but it may assist with basic macro-related tasks in future updates.