ntworld.ink
Microsoft Excel · Course 2 · Reference

Using Copilot in Excel

One of the most useful things about Copilot, Microsoft's AI assistant, is that you do not need to know the name of a function to use it. You describe what you want in plain English, and Copilot suggests the formula. This reference shows how to ask well, with example prompts you can adapt, and the one rule that matters most: always check the answer.

// take it with you Download this reference (Word)
Back to course overview
// on this page
// what copilot is

What Copilot Is

Copilot is Microsoft's AI assistant, built into newer, online versions of Microsoft 365. In Excel it sits as a button on the Home tab (its exact spot moves between updates). You type a request in plain language and it answers in the side panel.

It is an optional helper, not a part of the course you must have. It needs a recent, internet-connected version of Excel and the right licence, so it may not be available on every computer, and that is fine. Everything in this course works without it. Where Copilot does help, it helps most with one thing in particular: turning "what I am trying to do" into "the formula that does it".

You do not need to know that the function is called CONCAT or SUMIF. You only need to describe, in ordinary words, what you want to happen. Naming the function is Copilot's job; knowing what you want is yours.

// what it does well

What It Does Well

Copilot is genuinely good at the language side of spreadsheets:

// where it falls short

Where It Falls Short

Copilot is a confident writer, which is a strength and a trap. It can give a wrong answer in the same calm, sure voice as a right one. So it is weak exactly where being confidently wrong matters:

// how to ask well

How to Ask Well

A good prompt gives Copilot three things: where your data is, what you want to happen, and where the answer should go. The more plainly you say those, the better the formula you get back.

The examples below show the pattern. Each is the kind of thing you could type, even without knowing the function's name.

// prompts: build a formula

Prompts: Build a Formula

// you want to join two columns (you may not know the word "concatenate")

"I have first names in column A and surnames in column B. In column C I want the full name, like 'Jordan Lee', with a space in between. What formula do I use?"

Copilot is likely to suggest
=A2&" "&B2

The & joins pieces of text together, and " " is the space between them. Copilot might instead suggest =CONCAT(A2," ",B2) or =TEXTJOIN(" ",TRUE,A2,B2); all three do the same job.

// you want a word that depends on a condition

"In column E, I want it to say 'Over' when the amount in column B is bigger than the budget in column A, and 'OK' otherwise."

Copilot is likely to suggest
=IF(B2>A2,"Over","OK")

That is the IF function: a test, then what to show if it is true, then if it is false.

// you want to count or add only the matching rows

"How do I count how many rows in column B say 'Yes'?"

Copilot is likely to suggest
=COUNTIF(B2:B100,"Yes")

And for adding instead of counting: "Add up only the amounts in column C where column B is 'Darwin'" gives =SUMIF(B2:B100,"Darwin",C2:C100).

// you want part of some text, but do not know how

"Column A has email addresses. In column B I just want the part before the @ sign."

Copilot is likely to suggest
=TEXTBEFORE(A2,"@")

It may also suggest using Flash Fill (type one example, press Ctrl + E) instead, which is often the simplest answer of all for tidying text.

// you want a number of days, or an age, from dates

"Column B has birthdates. In column C I want each person's age in whole years, as at today."

Copilot is likely to suggest
=DATEDIF(B2,TODAY(),"Y")

This works because, as Session 1 showed, Excel stores dates as numbers, so it can do maths with them.

// prompts: fix an error

Prompts: Fix an Error

When a cell shows an error code instead of an answer, paste the formula and the error into Copilot and ask what is wrong. Be specific about which cell and what you expected.

// a divide-by-zero error

"My formula in D2 shows #DIV/0!. Here it is: =C2/B2. How do I make it show a blank instead of the error when B2 is empty?"

Copilot is likely to suggest
=IFERROR(C2/B2,"")

IFERROR wraps any formula and shows your own result (here, a blank) instead of a scary code.

// you inherited a formula and do not understand it

"What does this formula do, in plain English? =VLOOKUP(A2,$D$2:$F$50,3,FALSE)"

Copilot will explain it

It will tell you, in words, that the formula looks up the value from A2 in the first column of the table D2:F50 and returns the matching value from the third column, with FALSE meaning an exact match. Explaining a formula is one of the things Copilot does most reliably.

// a copied formula went wrong

"I copied =B2*E1 down a column to work out GST, but the lower rows are wrong. What did I do wrong?"

Copilot is likely to explain and suggest
=B2*$E$1

It should spot that the rate reference E1 drifted as you copied, and tell you to lock it with dollar signs as an absolute reference, exactly the lesson from Session 2.

// always check the answer

Always Check the Answer

Whatever Copilot gives you, the final check is yours. The safe habit is simple: let Copilot write the formula, then confirm the result with something you can see.

Copilot is a fast, confident assistant, not an oracle. It is brilliant for "what is the formula for this?" and "what does this mean?". For "is this number right?", the answer always comes from you.