Skip to content

How to Generate Formulas Using Copilot in Excel

Tags:

In recent years, artificial intelligence has transformed the way we work in productivity tools, and Microsoft Excel is no exception. With the introduction of Copilot in Microsoft 365, users can now generate formulas, automate workflows, and analyze data with natural language commands. Instead of struggling to remember complex syntax or nested functions, Copilot acts as your intelligent assistant—interpreting your requests and building formulas for you.

Whether you’re a beginner who always forgets how VLOOKUP works or a power user wanting to prototype formulas faster, Copilot offers an intuitive way to work smarter in Excel. In this guide, we’ll walk through how to generate formulas using Copilot in Excel, step-by-step, along with examples and tips on getting the best results.

How to Access Copilot in Excel

Before you can start generating formulas, you need to confirm that Copilot is enabled in your Excel app. You can use Copilot in:

  • Excel for Web
  • Excel Desktop (Microsoft 365 Enterprise or Business plans with Copilot subscription)

Once Copilot is available, you’ll see a Copilot icon (usually on the ribbon toolbar). Clicking it opens a panel where you can enter instructions.

Generating Formulas with Natural Language Prompts

The magic of Copilot lies in being able to write requests like:

“Calculate the total revenue by multiplying quantity and price.”

Copilot then writes and inserts the appropriate formula.

Step-by-Step: How to Generate a Formula

Step 1: Select a Cell

Choose where you want the formula to appear. Copilot often works better when the correct column is selected before giving instructions.

Step 2: Open Copilot

Click the Copilot icon to open the prompt window.

Step 3: Describe What You Want

Write a natural language request such as:

  • “Calculate the profit by subtracting cost from revenue.”
  • “Show the average of column C.”
  • “Find the highest score in this dataset.”

Step 4: Insert the Formula

Copilot generates the formula and gives you the option to insert it directly into Excel.

Examples of Using Copilot for Common Excel Formulas

Here are several real-world scenarios showing how Copilot can help.

1. SUM and Basic Arithmetic

Instead of writing:

=SUM(B2:B50)

You can simply ask:

“Add up all values in the Quantity column.”

Copilot identifies the correct column and generates the formula for you.

2. Calculating Totals with Multiple Columns

If your worksheet has a Price and Quantity column, ask:

“Calculate total sales as price multiplied by quantity.”

Copilot produces a formula like:

=[@[Price]]*[@[Quantity]]

Or, depending on the format, a cell reference version such as:

=B2*C2

3. AVERAGE, MAX, MIN

Instead of searching Excel’s function list, ask:

  • “Show the highest revenue figure.”
  • “Give me the average delivery time.”

Copilot writes the correct functions like MAX(), AVERAGE(), or MIN().

4. Text Formulas

Copilot can help with string manipulation:

“Combine first and last name into one cell with a space.”

It may output:

=CONCATENATE(A2, " ", B2)

Or the modern version:

=TEXTJOIN(" ", TRUE, A2, B2)

5. IF Statements

Complex conditional formulas become much easier. Instead of manually writing:

=IF(C2 > 70, "Pass", "Fail")

Just ask:

“Mark the student as Pass if their score is above 70, otherwise Fail.”

Copilot generates the correct formula automatically.

6. VLOOKUP and XLOOKUP

Lookups are one of the hardest formula types for users to remember, but Copilot simplifies them.

Prompt:

“Look up the customer ID in column A and return the matching name from column B.”

Copilot may create:

=XLOOKUP(A2, A:A, B:B)

Or a VLOOKUP version depending on your settings.

Tips for Writing Effective Prompts

Copilot is powerful, but you can improve accuracy by giving more context. Here are helpful tips:

✔ Be Specific

Instead of:

“Calculate profit”

Try:

“Calculate profit in column F by subtracting cost in column D from revenue in column E.”

✔ Reference Column Headers

Copilot can detect column names, so mentioning them helps.

✔ Start with Small Requests

If you’re transforming large datasets, break requests into clear, manageable tasks.

✔ Review the Formula Copilot Writes

Always double-check logic before inserting it, especially in business-critical worksheets.

Using Copilot to Learn Excel Formulas

Copilot is not only a productivity tool—it’s also a learning tool. If you’re unsure how a formula works, ask:

“Explain the formula you just created.”

Copilot can walk you through:

  • What each part of the formula does
  • Why it chose the function
  • Alternative formulas you could use

Over time, this can help new users become more confident with Excel.

Advanced Use: Data Cleaning

Copilot can also generate formulas for data preparation:

  • Removing duplicates
  • Extracting text
  • Standardizing formats
  • Splitting data into multiple columns

For example:

“Extract the last four digits of the phone number.”

Copilot may write:

=RIGHT(A2, 4)

Or:

“Convert all names to proper case.”

Copilot could suggest:

=PROPER(A2)

Why Use Copilot for Formula Generation?

1. Saves Time

You don’t need to manually build or remember formulas.

2. Reduces Errors

Copilot writes correct syntax with consistent formatting.

3. Makes Excel Easier

New users can work faster without memorizing functions.

4. Boosts Productivity

Workflows that took 20 minutes now take seconds.

5. Teaches Along the Way

Copilot acts like a built-in tutor.

AI is reshaping how we use spreadsheets, and Copilot in Excel is one of the biggest productivity boosts the platform has ever seen. By turning natural language into functioning formulas, Copilot makes data analysis faster, simpler, and more accessible for everyone—from beginners to experts.

Whether you’re handling financial reports, business data, academic sheets, or everyday tasks, learning how to generate formulas using Copilot in Excel can significantly improve your workflow and reduce errors while empowering you to get more done.

Extra posts:

Advanced Excel Formulas for Business Analysis How to Use Copilot for Data Analysis in Excel Copilot in Excel Turning Data Nightmares into Insights in Seconds Step-by-Step: Your First Data Analysis with Copilot in Excel How To Generate a Process Flow using M365 Copilot How to Generate Slides in PowerPoint Using Microsoft Copilot in Minutes