🎓

Excel AI Gen Z Masterclass

for people who'd rather be doing literally anything else

Generation 2050Excel Ai

11 min read·2,407 words

EXCEL + AI

for people who'd rather be doing literally anything else

🧠 The No-BS Guide to Making Spreadsheets Your B*tch

Copy. Paste. Slay.

(yes, you can use AI to write formulas)

(no, you don't need to memorize anything)

(fr fr, this actually works)

Look, We Get It

Excel is giving... corporate millennial energy. It's that thing your manager uses while saying things like "let's circle back" and "synergize our deliverables."

But here's the tea: Excel + AI is actually cracked. Like, genuinely useful. You can literally just TELL the AI what you want and it writes the formula for you. No cap.

This guide is for everyone who:

Has ever Googled "how to add numbers in Excel" (no shame)
Thinks VLOOKUP sounds like a disease
Would rather unalive their laptop than write a macro
Wants to flex on their boss without actually learning Excel

The vibe: Copy the prompts. Paste into ChatGPT/Claude. Get formula. Paste into Excel. Profit. That's it. That's the whole thing.

Chapter 1: How to Talk to AI (Without It Being Useless)

AI is like that friend who's really smart but needs very specific instructions or they'll bring you the completely wrong thing. "Get me food" = chaos. "Get me a spicy chicken sandwich from Chick-fil-A, no pickles" = perfect.

The Formula for Not Getting Trash Results

Every prompt needs these 4 things:

CONTEXT: What's in your spreadsheet? Column names, what kind of data
GOAL: What do you actually want? Be specific bestie
EDGE CASES: What could go wrong? Empty cells? Zeros? Weird data?
OUTPUT: Do you want a formula, instructions, or VBA code?

The Copy-Paste Template (Use This Every Time)


My spreadsheet has: - Column A: [what's in it] - Column B: [what's in it] - Column C: [what's in it] - Data starts in row [X] I need a formula that: [exactly what you want] Handle these situations: [empty cells, zeros, errors] Give me: [formula / step-by-step / VBA macro]

Literally just fill in the blanks. It's like a Mad Libs but for productivity.

Chapter 2: Formulas (The Bread & Butter)

Time to actually learn how to make AI write formulas. Each section has the exact prompt you copy and what AI will spit back. It's giving... cheat codes.

Adding Stuff Up (But Make It Conditional)

The Situation: You have sales data. You only want to add up sales from the North region.

Your Prompt:


Column A has Region (North, South, East, West) Column B has Sales Amount Data is in rows 2-500 Write a formula to sum only the sales where Region = "North"

AI Says:


=SUMIF(A2:A500, "North", B2:B500)

That's it. That's the formula. You just saved 20 minutes of Googling.

Multiple Conditions (When One Isn't Enough)

Your Prompt:


Sum sales where: - Region (Column A) = "North" - Product (Column B) = "Widget" - Sales amount is in Column C Rows 2-1000

AI Says:


=SUMIFS(C2:C1000, A2:A1000, "North", B2:B1000, "Widget")

Calculating Percentage Change (Very Corporate, Much Wow)

Your Prompt:


Column B = This year's sales Column C = Last year's sales Calculate year-over-year % change in Column D If last year was 0, show "N/A" instead of an error

AI Says:


=IF(C2=0, "N/A", (B2-C2)/C2)

Format the column as percentage and you're golden. Main character behavior.

Commission Calculator (Money Money Money)

Your Prompt:


Calculate commission based on sales in B2: - $0-10,000: 5% - $10,001-50,000: 7% - $50,001-100,000: 10% - Over $100,000: 12%

AI Says:


=IFS(B2<=10000, B20.05, B2<=50000, B20.07, B2<=100000, B20.10, TRUE, B20.12)

Chapter 3: Lookups (Finding Stuff)

Lookups are for when you have data in one place and need to match it with data somewhere else. Like matching names to phone numbers, or order IDs to customer info. This is where people usually give up and cry. But not us.

VLOOKUP (The Classic)

The Situation: Sheet1 has Order IDs. Sheet2 has Order IDs AND Customer Names. You want to bring customer names into Sheet1.

Your Prompt:


Sheet1 Column A: Order ID Sheet1 Column B: I need Customer Name here Sheet2 Column A: Order ID Sheet2 Column B: Customer Name Write a VLOOKUP for Sheet1 Column B. If order isn't found, show "Not Found" instead of error.

AI Says:


=IFERROR(VLOOKUP(A2, Sheet2!A:B, 2, FALSE), "Not Found")

XLOOKUP (The Upgraded Version)

If you have Excel 365 or 2021+, XLOOKUP is literally just better. Less confusing syntax.

Your Prompt:


Use XLOOKUP to find a product's price. I'm searching for Product ID in cell E2 Look in Column A for Product IDs Return value from Column C (Prices) If not found, return 0

AI Says:


=XLOOKUP(E2, A:A, C:C, 0)

See? Way cleaner. XLOOKUP ate and left no crumbs.

Chapter 4: Data Cleaning (Fixing Messy Garbage)

Real talk: 80% of spreadsheet work is cleaning up other people's mess. Inconsistent formats, duplicates, random spaces... it's chaotic. Let's fix that.

Fixing Names (The Capitalization Struggle)

The Problem: Column A has names like "JOHN SMITH", "mary jones", "bOB wiLsOn"

Your Prompt:


Column A has names with random capitalization. Give me formulas for: 1. ALL CAPS 2. all lowercase 3. Proper Case (First Letter Caps)

AI Says:


ALL CAPS: =UPPER(A2) lowercase: =LOWER(A2) Proper Case: =PROPER(A2)

Splitting Names (First & Last)

Your Prompt:


Column A has full names like "Taylor Swift" Write formulas to split into First Name and Last Name

AI Says:


First Name: =LEFT(A2, FIND(" ", A2)-1) Last Name: =RIGHT(A2, LEN(A2)-FIND(" ", A2))

Removing Duplicates

Your Prompt:


How do I remove duplicate rows in Excel based on email address in Column B? Give me step-by-step instructions, not a formula.

AI Says:


1. Select your entire data range 2. Go to Data tab → Remove Duplicates 3. Uncheck all columns EXCEPT the email column 4. Click OK 5. Excel will delete duplicate rows, keeping the first one

Cleaning Phone Numbers

Your Prompt:


Column A has phone numbers in chaos: (555) 123-4567 555-123-4567 5551234567 555.123.4567 Standardize them all to: 555-123-4567

AI Says:


=TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( A2," ",""),"-",""),"(",""),")",""), "###-###-####")

That formula looks unhinged but it works. Just trust the process.

Chapter 5: Pivot Tables (Actually Not That Hard)

Pivot Tables sound intimidating but they're literally just a way to summarize data. AI can't create them FOR you, but it can tell you exactly what buttons to click.

Basic Pivot Table Setup

Your Prompt:


I have sales data: Date, Salesperson, Region, Product, Amount I want to see total sales by Region and Product. Tell me exactly how to set up this pivot table, step by step.

AI Says:


1. Click any cell in your data 2. Insert → PivotTable → New Worksheet → OK 3. In the PivotTable Fields pane on the right: - Drag "Region" to ROWS - Drag "Product" to COLUMNS - Drag "Amount" to VALUES 4. Done! You now have a summary table. To format as currency: - Click the dropdown on "Sum of Amount" - Value Field Settings → Number Format → Currency

Grouping Dates by Month

Your Prompt:


My pivot table shows every single date and it's chaos. How do I group dates by month instead?

AI Says:


1. Right-click any date in your pivot table 2. Click "Group" 3. Check "Months" (and "Years" if you want) 4. Uncheck "Days" 5. Click OK Your dates now show as Jan, Feb, Mar etc. 🎉

Chapter 6: Charts (Making Data Pretty)

Charts are how you make your work actually presentable. AI can recommend the right chart type and tell you how to make it.

Picking the Right Chart

Your Prompt:


I have monthly sales for 4 regions over 12 months. What chart should I use to show trends over time AND compare regions? How do I create it?

AI Says:


Use a LINE CHART with multiple series. 1. Select your data (including headers) 2. Insert → Charts → Line Chart → "Line with Markers" 3. Each region becomes a colored line 4. X-axis shows months, Y-axis shows sales This shows both the trend AND comparison clearly.

Quick Chart Reference


You Want To...	Use This Chart
Compare categories	Bar Chart / Column Chart
Show trends over time	Line Chart
Show parts of a whole	Pie Chart (but like... sparingly)
Show build-up/breakdown	Waterfall Chart
Show correlation	Scatter Plot

Chapter 7: VBA Macros (Coding Without Coding)

VBA is Excel's programming language. It sounds scary but AI can write it for you. You literally just copy-paste. Zero coding skills required.

Auto-Format a Report

Your Prompt:


Write a VBA macro that: 1. Selects all data (A1 to last used cell) 2. Makes the header row bold 3. Adds borders to everything 4. Auto-fits column widths

AI Says:


Sub FormatMyReport() Dim lastRow As Long, lastCol As Long lastRow = Cells(Rows.Count, 1).End(xlUp).Row lastCol = Cells(1, Columns.Count).End(xlToLeft).Column 'Bold headers Rows(1).Font.Bold = True 'Add borders Range(Cells(1,1), Cells(lastRow, lastCol)).Borders.LineStyle = xlContinuous 'Auto-fit Columns.AutoFit End Sub

How to Actually Use the Code


1. Press Alt + F11 (opens VBA editor) 2. Click Insert → Module 3. Paste the code 4. Close the editor 5. Press Alt + F8 → select your macro → Run Or just press F5 while in the VBA editor. Done.

Chapter 8: Fixing Errors (When Excel Throws a Tantrum)

Excel errors look terrifying but they're usually easy fixes. Here's how to ask AI to debug.

#REF! Error


Prompt: My VLOOKUP gives #REF! error. Formula is: =VLOOKUP(A2, Sheet2!A:C, 5, FALSE) What's wrong? AI: You're asking for column 5 but your range (A:C) only has 3 columns. Change 5 to 2 or 3, or expand your range to A:E.

#VALUE! Error


Prompt: =A2+B2 gives #VALUE! but both look like numbers? AI: One cell has a number stored as TEXT (probably imported data). Fix: Select the cells → Data → Text to Columns → Finish Or use: =VALUE(A2)+VALUE(B2)

#N/A Error


Prompt: VLOOKUP returns #N/A but I KNOW the value exists! AI: Common causes: 1. Extra spaces: Use =TRIM() on both lookup value and source 2. Different data types: One is text, one is number 3. Typo in the lookup value To hide the error: =IFERROR(VLOOKUP(...), "Not Found")

Chapter 9: Copy-Paste Prompts for Real Life

Here are prompts for actual work situations. Just modify for your data.

🛒 E-Commerce: Calculate Profit Margin


Column A: Product Column B: Revenue Column C: Cost Write a formula for Column D (Profit) and Column E (Margin %) Handle division by zero.

📊 Marketing: Campaign ROI


Column A: Campaign Name Column B: Ad Spend Column C: Revenue Generated Column D: Conversions Calculate: Cost Per Conversion, ROI %, Revenue Per Dollar Spent Highlight campaigns with ROI > 100% in green

💰 Finance: Budget Tracker


Column A: Category Column B: Budget Column C: Actual Spend Create formulas for: - Variance (Actual - Budget) - % of Budget Used - Flag if over budget (show "OVER" in red)

👥 HR: Employee Tenure


Column A: Employee Name Column B: Start Date Column C: (blank - need formula) Calculate years and months of tenure as of today. Format: "2 years, 5 months"

📦 Inventory: Reorder Alert


Column A: Product Column B: Current Stock Column C: Reorder Point Create formula for Column D: - If stock <= reorder point: "ORDER NOW" - If stock <= reorder point + 10: "Low Stock" - Otherwise: "OK"

The TL;DR (For Real)

Don't memorize formulas. Just describe what you want to AI.
Be specific in prompts. Column names, row numbers, edge cases.
Copy-paste is valid. That's literally the point.
Always test formulas. AI is smart but not perfect.
IFERROR is your bestie. Wrap formulas to avoid ugly errors.

Save this doc. Use these prompts. Touch grass.