LexiLab Academy | Learn Smarter. Build Faster. Grow Without Limits.

Let me be honest with you: This is what I did that saved my job.

Six months ago, my boss sent me a spreadsheet with 12,000 rows, three different sheets, and a note that said: "Need the quarterly report by 4 PM." I stared at it. I knew basic SUM and AVERAGE, but he wanted conditional lookups, text cleaning, and a dynamic summary. I had two choices: spend 10 hours on Google trying to remember how VLOOKUP works, or figure out a smarter way.

I opened Excel Copilot, typed what I wanted in plain English, and it wrote the formulas for me. I delivered the report at 3:30 PM. My boss thought I was an Excel expert.

I'm not. I just learned how to talk to Copilot.

If you've ever felt that cold sweat when you see #N/A, #VALUE!, or a formula that looks like =IFERROR(INDEX(...MATCH...))), this guide is for you. This is the only tutorial you'll ever need to make Excel Copilot write any formula for you, even if you are a complete beginner.

What is Excel Copilot and Why It's a Game Changer?

Excel Copilot is Microsoft's AI assistant built directly inside Excel (for Microsoft 365 users). Think of it as ChatGPT, but it can see your entire spreadsheet, understands your column names, and writes formulas that actually work on your data.

You don't write formulas anymore. You describe what you want.

Instead of writing: =XLOOKUP(A2, Customers!A:A, Customers!C:C)

You just type: "Find the customer email for the ID in cell A2 from the Customers sheet"

And Copilot does the rest. It writes it, explains it, and inserts it for you.

This is not the old "Formula Builder." This is different. It understands context, typos, and real-world business language.

If you want to know more about excel click here.

Who can use it?
You need Microsoft 365 with a Copilot Pro or Copilot for Microsoft 365 license. If you don't see the Copilot button on the Home tab (top right, like in the image above), update your Excel and check your subscription. If you use Excel for the web (office.com), you will see it first there.

Step 0: Prepare Your Data Like a Pro (This is Crucial)

Copilot is smart, but it is not a magician. If your data is a mess, its formulas will be a mess. Before you even click Copilot, do these 3 things. It will save you 90% of errors.

1. Convert Your Range to a Table: Select your data and press Ctrl + T. Check "My table has headers". Why? Now Copilot can use real names like [Sales] and [Product] instead of C2:C10000. It's more accurate and your formulas become readable.

2. Give Your Columns Clear Headers: Don't use "Col1" or "Data". Use "Customer Email", "Order Date", "Total Price". Copilot reads headers to understand your intent.

3. Remove Blank Rows Inside Your Table: Copilot gets confused by empty rows. Filter and clean them quickly.

Do this once, and every prompt after will be 10x better.

The Step-by-Step Guide: How to Make Copilot Write Any Formula

Let's go from zero to hero with real examples you can copy.

Step 1: Open Copilot
In Excel Desktop or Excel for Web, go to the Home tab > Click Copilot on the far right. A chat panel will open on the right side, exactly like the screenshot above.

Step 2: Be Specific, But Talk Like a Human
This is the biggest mistake beginners make. They type "formula". That's too vague.

Use this simple framework: Action + Where + Condition

Bad prompt: "Sum formula"
Good prompt: "Calculate the total sales for the Product 'Laptop' in column C only"

Copilot understands verbs like: calculate, find, extract, count, combine, clean, show, highlight.

Step 3: Let Copilot Explain and Insert
Copilot will suggest a formula, like =SUM(C2:C10) or something more complex, with a plain-English explanation. You will see two buttons:

Insert into : Clicks it for you.[Cell]
Explain formula: If you want to learn what it does.

Always click "Explain formula" the first few times. That's how you actually learn.

Now, let's get practical.

7 Real-World Examples (Copy & Paste These Prompts)

Here are the exact prompts I use almost every day. Try them on your own sheets.

1. The Total with a Condition (The One That Saved My Job)
My problem: I needed total sales, but only for the year 2024.
My prompt: "Create a formula that sums the values in the 'Sales' column, but only where the 'Date' column year is 2024"
Copilot gave me: =SUMIFS(Sales[Sales], Sales[Date], ">=1/1/2024", Sales[Date], "<=12/31/2024")
I would have never written that SUMIFS from memory.

2. Looking Up Data Without VLOOKUP Nightmares
Problem: Match customer IDs to names.
Prompt: "Write a formula to find the customer name for the ID in A2 by looking it up in the Customers sheet, columns A and B. If not found, return 'Not Found'"
Copilot gives you XLOOKUP, which is modern and better than VLOOKUP: =XLOOKUP(A2, Customers!A:A, Customers!B:B, "Not Found")

3. Cleaning Messy Text
Problem: You have " john DOE@email.com " with spaces and weird caps.
Prompt: "Clean the email addresses in column D to remove extra spaces and make them all lowercase"
Copilot: =TRIM(LOWER(D2))

4. Extracting Information
Problem: You have "Order #12345 - John Smith" and you only want the number.
Prompt: "Extract only the numbers from cell A2"
For Excel 365 users, it will give you the amazing new function: =TEXTAFTER(TEXTBEFORE(A2, " -"), "#") or =REGEXEXTRACT(A2, "\d+")

5. Counting Specific Things
Prompt: "Count how many times 'Delayed' appears in the Status column"
Copilot: =COUNTIF(Table1[Status], "Delayed")

6. Combining First and Last Name
Prompt: "Combine the first name in column A and last name in column B with a space in between"
Copilot: =A2 & " " & B2 or =TEXTJOIN(" ", TRUE, A2, B2)

7. Creating a Formula That Flags Problems
Prompt: "Write a formula that shows 'Over Budget' if the actual cost in column E is greater than the budget in column D, otherwise show 'OK'"
Copilot: =IF(E2>D2, "Over Budget", "OK")

Advanced Tricks: How to Think Like a Copilot Pro

Once you get the basics, use these 3 power moves.

1. Chain Your Requests: Don't ask for everything at once. Start simple, then refine.
First: "Calculate total profit in column F"
Then, in the same chat: "Now do the same but only for the 'West' region"
Copilot remembers the context of your table.

2. Ask It to Fix Your Broken Formula: This is magic. Copy your broken formula, paste it into Copilot and say: "This formula =VLOOKUP(A2,B:C,3,0) gives me #REF! Can you fix it?"
It will explain the error and correct it to =VLOOKUP(A2,B:C,2,FALSE) or suggest XLOOKUP.

3. Ask for a Column Formula, Not Just One Cell: Say "Create a formula for the entire Profit Margin column that calculates (Sales-Cost)/Sales and formats as percentage". Copilot will understand you want it for the whole structured column.

Common Mistakes That Make Copilot Fail (And How to Fix Them)

Your data is not a Table: If Copilot keeps using A2:A100, you forgot Ctrl+T. Convert to Table.
You are too vague: "Help with sales" means nothing. "Calculate average sales per month" means everything.
Merged Cells: Unmerge them. Now. Copilot hates merged cells.
You have multiple tables on one sheet: Keep one table per sheet when you are starting. Copilot gets confused about which one you mean.

Is Excel Copilot Safe for Work Data?

Yes. Unlike pasting your data into a public chatbot, Copilot runs inside your Microsoft 365 compliance boundary. Microsoft states your business data is not used to train the public AI models. For sensitive financial data, it's the only AI you should be using inside Excel.

The Bottom Line: Stop Memorizing, Start Describing

Excel used to be about memorizing 500 functions. Now it's about knowing how to describe the problem. The people who win at work are not the ones who know every formula; they are the ones who know how to ask AI to write it for them.

I went from being scared of spreadsheets to being the go-to Excel person on my team. Not because I got smarter, but because I started using Copilot as my personal formula tutor.

Start with one prompt today. Just one. Type "Summarize this table and tell me 3 insights" and watch what happens. You will never go back.

Ready to become unstoppable in Excel?