Type Commands, Not Formulas — Excel Copilot Hands-On

Copilot has not made formulas disappear. What it has changed is the way we access them.
Translated from Chinese.

Many people do not struggle with Excel itself. They simply lose the will to continue the moment they see formulas, parentheses, and nested conditions.

In the past, you had to learn a function first, then translate your needs into a formula. Now, you can explain the business rules first and let Copilot build and fill in the formula.

This tutorial starts with common formulas such as VLOOKUP, IF, and SUMIF to see how far natural language can lower the barrier to learning Excel.

This tutorial requires editing access to Copilot in Excel. Availability depends on your Microsoft 365 plan, app version, and—if you use a work account—your organization’s admin settings.

I said in an earlier article that Excel Copilot is a productivity tool. Now I am going to show you where that value comes from.

This tutorial covers using Copilot to create Excel formulas and fill them down.

Step 1: Understand the Basic Objects in Excel

Once you understand the diagram below, we can begin.

Important: Office.js operations occasionally return no visible result. When that happens, remind the AI that the previous instruction was not executed or refreshed. For example: “The previous instruction was not executed.”

Step 2: Find Copilot

Go to Home → Copilot. In some versions, Copilot appears as a floating button in the lower-right corner of the window.

Open it, then select Allow editing in the chat pane.

Note: Depending on your screen resolution, Copilot may not appear on the ribbon. You can search for Chat with Copilot using the search bar at the top of the window.

Step 3: Build a Practice Workbook

Enter the following prompt in the Copilot chat box:

Create two sheets. In Sheet2, create a table using Apple product data.
1. In column A, enter the product category. Use Product Category as the header.
2. In column B, enter phone, tablet, or laptop. Use Product as the header.
3. In column C, enter the generation, such as 13th generation or 14th generation.
4. In column D, enter the model, such as Pro or Air.
5. In column E, enter the color.
6. In column F, enter the storage capacity.
7. In column G, randomly generate order numbers with a consistent length and format.
8. In columns H, I, and J, enter Accessory A, Accessory B, and Accessory C, such as headphones, a mouse, or a keyboard.
9. Randomly generate 200 rows. If options 3, 4, 5, 6, or 8 are unavailable for a product, enter /.
Then, in column A of Sheet1, randomly select 25 order numbers from column G of Sheet2 and add 5 other order numbers.

This prompt creates a workbook we can use for the rest of the tutorial. That is one of Copilot’s strengths: I can give you a block of text that creates the practice material directly, instead of giving you a download link—or giving you nothing and asking you to imagine the result.

To avoid filling the article with too many screenshots, I will not include an image for every remaining step. I have tested each step.

If you look closely at the prompt, there is no code in it. It uses only natural language and a few Excel terms introduced in Step 1. It is all “nouns + logic.” That is the core of how AI understands instructions. Let’s continue.

Step 4: Use AI to Perform VLOOKUP Tasks

VLOOKUP is one of the most widely needed functions. It is commonly used to match and analyze data.

• Standard VLOOKUP retrieval

Go to Sheet1. We are going to retrieve Accessory B for each order. Enter the following prompt in Copilot:

Using column A in Sheet1, retrieve the corresponding values from column I in Sheet2 and place them in column B of Sheet1.

Copilot will use a formula to retrieve the data. If you use VLOOKUP manually, you first need to understand how VLOOKUP works. With AI, you only need to understand the basic Excel objects from Step 1 and describe what you want.

• Retrieve multiple columns

Now let’s retrieve Accessory A and Accessory C at the same time. Enter:

Using column A, retrieve the corresponding values from columns H and J in Sheet2 and place them in columns C and D of Sheet1.

Or simply continue with:

Continue by retrieving columns H and J.

Yes, Copilot in Excel has context. It carries information from the previous steps, so you do not need to rewrite the entire instruction. You only need to add what comes next. Notice that I skipped column I here. The source columns do not have to be next to each other.

In the past, you might have needed a nested formula such as CHOOSECOLS + XLOOKUP, or INDEX + MATCH + MATCH/COLUMN. Just looking at those combinations can feel complicated. You could also copy several VLOOKUP formulas, but either way, it is more work.

• Retrieve values from columns to the left

Now let’s retrieve the product, generation, color, and storage capacity. Enter:

Using column A, retrieve the corresponding values from columns B, C, E, and F in Sheet2 and place them in columns E, F, G, and H of Sheet1.

Or simply continue with:

Continue by retrieving columns B, C, E, and F.

This retrieves values from columns to the left of the lookup column—something VLOOKUP cannot do. This workflow covers both left-side retrieval and non-adjacent columns.

In the past, you would have needed CHOOSECOLS + XLOOKUP, INDEX + MATCH + SWITCH, or a helper column in Sheet2 before copying more formulas. Now, you do not even need to know what XLOOKUP is to complete the task with AI.

Step 5: Use AI to Perform IF Tasks

IF is another function with extremely high demand. A simple IF formula is easy enough, but adding other functions, combining it with VLOOKUP, or nesting several IF statements can quickly become exhausting.

• Standard IF

Let’s add a warning to the table:

Enter Warning in I1 and fill column I with a note. If the value in column H is less than or equal to 256 GB, enter Storage may be insufficient. Otherwise, enter /.

• Use IFERROR

Modify column H. If no value can be retrieved, enter No information.

Careful readers may already have noticed that some values in the table are blank—or different, because AI does not produce exactly the same content every time. With VLOOKUP alone, you would see the annoying #N/A error. Copilot uses IFERROR to display a blank instead. That is an autonomous choice made by the AI. I am simply changing its default behavior from displaying a blank to displaying “No information.”

• Use nested IF statements

Enter Device Note in J1. If column E is blank, enter No information. If it is a phone, enter Remember to buy a screen protector. If it is a laptop, enter Remember to buy a mouse. If it is a tablet, enter Remember to buy a keyboard and mouse.

This requires four levels of IF. For many people, that is already troublesome. Miss one closing parenthesis, and you may end up staring at the formula character by character, trying to find it. With AI, you only need to explain what each result should be.

• Combine IF with multiple conditions

Enter Device Warning in K1. If column E is Phone and columns B, C, or D contain a keyboard or mouse, enter The customer may have purchased an unnecessary accessory. If the storage capacity in column H is less than 256 GB, enter Storage capacity is very low. If both warnings apply, list them in that order. Otherwise, enter No information.

This is a complicated formula that requires AND and OR conditions. In the past, adding even one more condition could make the formula much less stable. Now, even if you want to add another condition—such as something related to the product generation—you only need to add a few words and send the prompt to Copilot again. The difference in efficiency is enormous.

Step 6: Use AI to Perform SUMIF, SUMIFS, COUNTIF, and COUNTIFS Tasks

• Add price information

First, go to Sheet2 and enter this prompt:

Insert a new Price column before column B in Sheet2. Use the product category, product, generation, model, and storage capacity in the existing table to fill in the corresponding official Apple price.

If you do not specify Sheet2, Copilot may insert the content into Sheet1 instead, where the data required for the lookup is unavailable.

• Use AI to create summary formulas

After inserting the Price column, the original Product column moves to column C. Stay in Sheet2 and enter:

Enter Product in M1, then list the three product names from column C below it. Enter Sales Volume in N1 and calculate how many times each product appears in column C. Enter Revenue in O1 and calculate the total revenue for each product using the product names in column C and the prices in column B.

You will find that Copilot completes the task quickly. The formulas themselves are still fairly simple, though. An experienced Excel user may not be any slower than AI here.

Some people may ask: there are already plenty of formula generators and Excel add-ins, so why should I use AI?

Because Copilot can write the results directly into your workbook. You only need to understand the table and explain what you want. A formula generator mainly increases your tolerance for not fully understanding a formula. It does not free you from the execution work.

Other people try one or two AI formula generators or add-ins, get a poor formula, and conclude that AI itself is bad. In reality, the problem is often that the instruction was not clear enough. Copilot shows the result directly in the workbook. If it is wrong, you can ask whether the issue came from your instruction, adjust the prompt, and try again. Many formula generators do not give you that opportunity for self-directed improvement.

Conclusion

What Copilot really replaces is not Excel knowledge. It replaces the process of translating a business requirement into a formula. You still need to know what you want to calculate, how two tables correspond to each other, and whether the result makes sense.

It allows beginners to solve the problem first and understand the formula later. But if you cannot explain the business rules, AI can only generate a complicated-looking formula that is still wrong.

Excel Copilot also has a serious limitation: it struggles with large workbooks. When working with a large spreadsheet, I recommend generating the formula only in the first row and then filling it down manually, or processing one column at a time. Otherwise, you may end up with Excel freezing.