Copilot in Excel-5 Minutes to Outperform 90% of Excel Users with AI

Quick note: I'm a native Chinese speaker. This article was translated with AI assistance — but I've personally tested every step in English before publishing. What you see here works exactly as shown.

Prerequisites: This tutorial requires the Copilot feature in Excel (Microsoft 365 subscription). Availability may vary by region and may require additional configuration.

Following my previous two articles in the Copilot from a User's Perspective series, this is the first article in a new companion series: AI Tutorials. I'll continue updating the previous series — I just think it's important to break up the rhythm with something immediately actionable from time to time.

Why did I dare use this title? I'm sure many of you think I'm exaggerating. In 5 minutes, most people can't even explain what a cross-sheet lookup is — but if you follow this tutorial today, I'm confident you'll agree with the title.

If you don't believe me, start your timer now.

Step 1: Open Excel and Learn the Terminology

Before we start, let's make sure we speak the same language:

• Column — The vertical axis, labeled with letters (e.g., Column A, Column B).
• Row — The horizontal axis, labeled with numbers (e.g., Row 1, Row 2).
• Cell — A single coordinate. For example, A3 means Column A, Row 3.
• Range — A span from one cell to another. For example, B3:B10 means Column B, Rows 3 through 10. B3:D4 includes six cells: B3, C3, D3, B4, C4, D4.
• Worksheet — The tabs at the bottom of your Excel file (Sheet1, Sheet2, etc.). Each tab is a separate table.
• Workbook — The Excel file itself. 

You might be thinking: "You're starting THIS basic? No way you'll deliver on that title!"

But here's the thing — if you understand these terms, you already have everything you need to use Copilot in Excel.

Step 2: Create a Practice Dataset

Create a new Excel file, open Copilot, and enter this prompt. Make sure to click "Allow Edits" when prompted.

Create Sheet2 first with these columns: Name, Gender, Student ID, Score, Height, Class, and Commute Method. Randomly generate 30 rows of data. Make sure the Student IDs are NOT sequential numbers. Then create Sheet1: randomly pick 10 Student IDs from Sheet2 and list them in Column A. For both sheets, format the header row with a light gray fill, increase the font size by 1, and center-align.

Most tutorials only teach you concepts — they never give you a dataset to practice with. Here, I just had AI generate a ready-made practice dataset so you can follow along with every step below.

Now, let's get to work.

Step 3: Use AI to Replace VLOOKUP

VLOOKUP is the single most searched Excel function on the internet. Give me 30 seconds, and I'll make it irrelevant.

With your tables ready, go to Sheet1. In the Copilot sidebar, type:

Based on Column A in Sheet1, pull the values from Column D and Column E in Sheet2.

That's it. You just accomplished what VLOOKUP does.

Now here's where it gets interesting. VLOOKUP has a well-known limitation — it can only pull data from columns to the right of the lookup column, never to the left. Try this:

Based on Column A in Sheet1, pull the values from Column A and Column B in Sheet2.

If this works — and it will — you've just gone beyond what traditional VLOOKUP can do. And you never had to understand how VLOOKUP works under the hood.

The prompts I used above are deliberately bare-bones. You can be much more specific:

Based on Column A in Sheet1, pull the values from Column D and Column E in Sheet2. Insert these two columns before Column A in Sheet1, and fill them with a light gray background.

The more Excel terminology you know, the more precise your prompts become — and the fewer errors you'll encounter.

Did you notice something? Everything you just typed was nouns + logic. That is the core operating principle of generative AI. Let's keep going.

Step 4: Multi-Condition Sorting

Switch to Sheet2, where we have the full dataset.

Sometimes you need complex sorting — Class in ascending order, Score in descending order within each class, and Student ID in ascending order within each score group. I consider myself an upper-intermediate Excel user, and I still couldn't do this manually — it requires nested sort configurations that most people never learn.

But just describe what you want. In the Copilot sidebar, type:

Sort the data with the following priority: Class ascending, Score descending, Student ID ascending.

All three columns are sorted simultaneously, each with its own direction. If you could do this without AI, you'd already be an advanced Excel user. AI just eliminated that skill gap — and it's faster too.

You might have noticed I didn't use column letter references (like "Column F") this time. In fact, I didn't need to in Step 3 either. AI can read the headers, think, and identify the right columns on its own.

Step 5: Conditional Formatting

Still on Sheet2.

Sometimes you need visual differentiation — for example, blue highlighting for male students and pink for female students. In the Copilot sidebar, type:

Fill the rows of male students with blue, and the rows of female students with pink.

Without AI, I'd filter for males, apply the fill, then filter for females and repeat. That two-step process is surprisingly slow for something so simple.

Sometimes you need to spot duplicates. Try:

Bold the text in cells where Height values are duplicated.

Without AI, this requires setting up conditional formatting rules — a skill that already puts you in intermediate-to-advanced territory.

Now the sheet looks a bit messy. Let's reset:

In Sheet2, reset all cells except the header row to default formatting.

A Note on Prompting Style

You'll notice that in Step 5, my prompts were almost entirely natural language — no column letters, no technical references. So why didn't I start the tutorial that way?

Because I wanted to give you something you could copy-paste and get working immediately — something reliable and reproducible. I use natural language prompts because I've spent enough time with AI to understand its boundaries and behavior.

The terminology-based approach from Step 3 is what I call "The Noun Method" — combine domain-specific nouns with natural-language logic to form complete instructions:

Based on (logic) Column A (noun) pull (logic) from Sheet2 (noun) Column B (noun) and (logic) Column C (noun)

Once you understand The Noun Method, you can effectively operate any generative AI tool. The key is learning the relevant nouns for each domain — and in Excel's case, there are remarkably few to learn.

Closing Thoughts

If you followed along with every step, the whole process probably took 10–15 minutes. But I believe that the moment you successfully ran the VLOOKUP prompt in Step 3, you stopped doubting the title.

If you'd like more Excel + AI tutorials, follow me and leave a comment. I'll keep them coming.

Next up: What You Need to Know About Tokens