Excel AI Practical Masterclass

EXCEL + AI

THE PRACTICAL MASTERCLASS

Copy-Paste Prompts, Step-by-Step Tutorials,

and Real-World Examples for Every Excel Task

Stop Googling Formulas. Start Prompting.

What You'll Learn

1. Formula Generation — Write any Excel formula using plain English

2. Data Cleaning — Fix messy data in minutes, not hours

3. Lookups & References — Master VLOOKUP, INDEX/MATCH, XLOOKUP with AI

4. Pivot Tables — Get AI to configure them for you

5. Charts & Visualizations — Create professional visuals with prompts

6. Financial Modeling — Build DCF, budgets, and forecasts

7. VBA & Automation — Write macros without coding knowledge

8. Error Debugging — Fix #REF!, #VALUE!, and circular references

9. Dashboards & Reports — Build executive-ready presentations

10. Real Use Cases by Department — Finance, Sales, HR, Marketing, Operations

Chapter 1: The Art of Prompting for Excel

Before we dive into specific tasks, you need to understand how to talk to AI about spreadsheets. The difference between a mediocre result and a perfect formula often comes down to how you ask.

The Golden Formula for Excel Prompts

Every great Excel prompt has four components:

1. CONTEXT: Describe your data structure (column names, data types)

2. GOAL: What specific outcome do you want?

3. CONSTRAINTS: Any limitations or special conditions

4. OUTPUT FORMAT: Formula, VBA, step-by-step instructions, or explanation

Sample Data Description Template

Copy and customize this whenever you need help:

My Excel sheet has the following structure: - Column A: [Name/Description] (data type: text/number/date) - Column B: [Name/Description] (data type: text/number/date) - Column C: [Name/Description] (data type: text/number/date) - Data starts in row [X] and has headers in row [Y] - Total rows: approximately [N] I want to: [specific goal] Give me: [formula / VBA macro / step-by-step instructions]

Chapter 2: Formula Generation

This is where AI shines brightest. Instead of memorizing 400+ Excel functions, describe what you want and let AI write the formula.

Basic Calculations

SUM with Conditions

Your Prompt:

Write an Excel formula to sum all values in column D where: - Column A contains "Electronics" - Column B is greater than 100 Data is in rows 2-1000.

AI Returns:

=SUMIFS(D2:D1000, A2:A1000, "Electronics", B2:B1000, ">100")

Percentage Calculations

Your Prompt:

Column C has current year sales, Column D has last year sales. Write a formula to calculate year-over-year percentage change. Handle cases where last year was zero.

AI Returns:

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

Commission Calculations

Your Prompt:

Calculate sales commission with these tiers: - 0-10,000: 5% - 10,001-50,000: 7% - 50,001-100,000: 10% - Over 100,000: 12% Sales amount is in cell B2.

AI Returns:

=IFS(B2<=10000, B2*0.05, B2<=50000, B2*0.07, B2<=100000, B2*0.10, B2>100000, B2*0.12)

Text Manipulation

Extract First/Last Name

Your Prompt:

Column A has full names like "John Smith" and "Mary Jane Watson". Write formulas to extract first name and last name into separate columns.

AI Returns:

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

Clean Phone Numbers

Your Prompt:

Column A has phone numbers in various formats: (555) 123-4567, 555-123-4567, 5551234567, 555.123.4567 Write a formula to standardize them all to format: 555-123-4567

AI Returns:

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

Chapter 3: Lookup Formulas (The Big Three)

Lookups are where most Excel users struggle. Here's how to get AI to write them perfectly every time.

VLOOKUP

Your Prompt:

I have two sheets: - Sheet1 has Order ID in column A, I need to add Customer Name in column B - Sheet2 has Order ID in column A and Customer Name in column B Write a VLOOKUP to bring the customer name from Sheet2 to Sheet1. Handle cases where the Order ID isn't found.

AI Returns:

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

INDEX/MATCH (More Powerful)

Your Prompt:

I need to look up a value but VLOOKUP won't work because my lookup column is to the RIGHT of the return column. - Column A: Product Name (what I want to return) - Column C: Product ID (what I'm searching for) Search value is in cell F2.

AI Returns:

=INDEX(A:A, MATCH(F2, C:C, 0))

XLOOKUP (Modern Excel)

Your Prompt:

Write an XLOOKUP formula to find the price of a product. - Search for Product ID in cell G2 - Look in column B (Product IDs) - Return value from column E (Prices) - If not found, return 0 - Use exact match

AI Returns:

=XLOOKUP(G2, B:B, E:E, 0, 0)

Chapter 4: Data Cleaning

Data cleaning eats up 60-80% of analyst time. AI can cut that dramatically. Here are the exact prompts for common cleaning tasks.

Remove Duplicates

Your Prompt:

I have a customer list with duplicate entries. Columns: A (Name), B (Email), C (Phone) How do I remove duplicates based on Email only, keeping the first occurrence? Give me step-by-step instructions.

AI Returns:

Step 1: Select your data range (A1:C1000) Step 2: Go to Data tab > Remove Duplicates Step 3: Uncheck all columns except 'Email' Step 4: Click OK Alternatively, use this formula in a helper column to flag duplicates: =IF(COUNTIF($B$2:B2, B2)>1, "Duplicate", "Unique")

Standardize Date Formats

Your Prompt:

Column A has dates in mixed formats: - 01/15/2024 (MM/DD/YYYY) - 15-Jan-2024 - 2024-01-15 (YYYY-MM-DD) - January 15, 2024 Write a formula or process to convert all to MM/DD/YYYY format.

AI Returns:

=TEXT(DATEVALUE(A2), "MM/DD/YYYY") If that doesn't work for all formats, use Power Query: 1. Select your data > Data tab > From Table/Range 2. Select the date column 3. Transform > Data Type > Date 4. Close & Load

Fix Inconsistent Capitalization

Your Prompt:

Column A has names with inconsistent capitalization: "JOHN SMITH", "mary jones", "Bob Wilson", "SARAH CONNOR" Write formulas for: all uppercase, all lowercase, and proper case (first letter capitalized).

AI Returns:

All UPPERCASE: =UPPER(A2) All lowercase: =LOWER(A2) Proper Case: =PROPER(A2)

Split Data from One Column to Many

Your Prompt:

Column A has addresses in format: "123 Main St, Boston, MA 02101" Split into separate columns: Street, City, State, ZIP

AI Returns:

Method 1 - Text to Columns: 1. Select Column A 2. Data > Text to Columns 3. Choose 'Delimited' > Next 4. Check 'Comma' as delimiter > Finish Method 2 - Formulas: Street: =TRIM(LEFT(A2, FIND(",", A2)-1)) City: =TRIM(MID(A2, FIND(",", A2)+1, FIND(",", A2, FIND(",", A2)+1)-FIND(",", A2)-1)) Method 3 - TEXTSPLIT (Excel 365): =TEXTSPLIT(A2, ", ")

Chapter 5: Pivot Tables with AI

AI can't create pivot tables directly, but it can tell you exactly how to configure them for any analysis you need.

Sales Analysis Pivot Table

Your Prompt:

I have sales data with columns: Date, Sales Rep, Region, Product, Quantity, Revenue. I want to create a pivot table showing: - Total revenue by Region (rows) and Product (columns) - Ability to filter by Sales Rep - Show as currency format Give me step-by-step instructions for the PivotTable Fields pane.

AI Returns:

1. Select your data > Insert > PivotTable > New Worksheet > OK 2. In PivotTable Fields pane: - Drag 'Region' to ROWS area - Drag 'Product' to COLUMNS area - Drag 'Revenue' to VALUES area - Drag 'Sales Rep' to FILTERS area 3. Click the dropdown on 'Sum of Revenue' in VALUES - Select 'Value Field Settings' - Click 'Number Format' > Currency > OK 4. Use the 'Sales Rep' dropdown at top to filter by rep

Monthly Trend Analysis

Your Prompt:

My pivot table has daily dates in the Rows area but I want to see data by Month and Year. How do I group the dates?

AI Returns:

1. Right-click any date in your pivot table 2. Select 'Group' 3. In the dialog box: - Check 'Months' and 'Years' - Uncheck 'Days' 4. Click OK Your dates will now show as Jan 2024, Feb 2024, etc. You can also add 'Quarters' for quarterly analysis.

Calculated Fields in Pivot Tables

Your Prompt:

My pivot table shows Sum of Revenue and Sum of Units. I want to add a calculated field for 'Average Price Per Unit' (Revenue / Units). How do I create this?

AI Returns:

1. Click anywhere in your pivot table 2. Go to PivotTable Analyze tab (or Options in older Excel) 3. Click Fields, Items & Sets > Calculated Field 4. In the dialog: - Name: 'Avg Price Per Unit' - Formula: =Revenue/Units 5. Click Add, then OK The new field will appear in your VALUES area automatically.

Chapter 6: Charts & Visualizations

AI can recommend the best chart type for your data and tell you exactly how to create it.

Choosing the Right Chart

Your Prompt:

I have this data and want to visualize it effectively: - 5 sales regions with their Q1, Q2, Q3, Q4 revenue - I want to show both the comparison between regions AND the trend over quarters What chart type should I use and how do I create it?

AI Returns:

Recommended: Clustered Column Chart or Line Chart with Markers For Clustered Column: 1. Select your data (including headers) 2. Insert > Charts > Clustered Column 3. Regions will be on X-axis, quarters as data series For combined view (Combo Chart): 1. Select data > Insert > Combo Chart 2. Choose 'Clustered Column - Line' 3. Set one region as line, others as columns Best Practice: Use columns for comparison, lines for trends over time.

Waterfall Chart (Revenue Bridge)

Your Prompt:

I need to show how revenue changed from 2023 to 2024: - Starting: $10M - Price increase: +$1.5M - New customers: +$2M - Churn: -$800K - Ending: $12.7M How do I create a waterfall chart in Excel?

AI Returns:

1. Set up your data: A B Start 10000000 Price 1500000 New Cust 2000000 Churn -800000 End 12700000 2. Select data > Insert > Waterfall Chart 3. Click the 'Start' bar > Format Data Point > check 'Set as Total' 4. Click the 'End' bar > Format Data Point > check 'Set as Total' 5. Format: - Right-click > Add Data Labels - Format positive bars green, negative red

Dynamic Chart Title

Your Prompt:

I want my chart title to automatically update based on a cell value. Cell A1 contains the selected region name. Chart title should say "Sales Report: [Region Name]"

AI Returns:

1. In any cell (e.g., B1), enter: ="Sales Report: "&A1 2. Click on your chart title 3. In the formula bar, type: =Sheet1!$B$1 4. Press Enter Now your chart title will update whenever A1 changes.

Chapter 7: Financial Modeling

AI can help you build sophisticated financial models, from budgets to DCF valuations.

Budget vs Actual Analysis

Your Prompt:

I have: - Column A: Expense Category - Column B: Budget Amount - Column C: Actual Amount Create formulas for: 1. Variance (Actual - Budget) 2. Variance % 3. Conditional formatting: green if under budget, red if over

AI Returns:

Column D (Variance): =C2-B2 Column E (Variance %): =IF(B2=0, 0, (C2-B2)/B2) Conditional Formatting: 1. Select Column D 2. Home > Conditional Formatting > Highlight Cell Rules > Less Than 3. Enter 0 > choose Green fill 4. Repeat with 'Greater Than 0' > Red fill For Variance %: - Format cells as Percentage - Apply similar conditional formatting

Loan Amortization Schedule

Your Prompt:

Build a loan amortization schedule: - Loan amount: $250,000 (cell B1) - Annual interest rate: 6.5% (cell B2) - Term: 30 years (cell B3) - Monthly payments Show: Payment #, Payment Amount, Principal, Interest, Remaining Balance

AI Returns:

First, calculate monthly payment in cell B4: =PMT($B$2/12, $B$3*12, -$B$1) Then create table starting row 7: A7: 1 (Payment #) B7: =$B$4 (Payment Amount) C7: =B7-D7 (Principal Portion) D7: =$B$1*($B$2/12) (Interest Portion - first month) E7: =$B$1-C7 (Remaining Balance) Row 8 and beyond: A8: =A7+1 B8: =$B$4 D8: =E7*($B$2/12) C8: =B8-D8 E8: =E7-C8 Copy formulas down for 360 rows (30 years x 12 months)

DCF Valuation Model

Your Prompt:

Help me build a simple DCF model: - 5 years of projected Free Cash Flow in row 2 (cells B2:F2) - Discount rate (WACC) in cell B5 = 10% - Terminal growth rate in cell B6 = 2% Calculate: Present Value of each year's FCF, Terminal Value, and Enterprise Value

AI Returns:

Row 3 - Discount Factor: B3: =1/(1+$B$5)^1 C3: =1/(1+$B$5)^2 (drag across to F3) Row 4 - Present Value of FCF: B4: =B2*B3 (drag across to F4) Terminal Value (cell G2): =F2*(1+$B$6)/($B$5-$B$6) PV of Terminal Value (cell G4): =G2*F3 Enterprise Value (cell B8): =SUM(B4:F4)+G4

Chapter 8: VBA Macros (No Coding Required)

AI can write complete VBA macros for you. Just describe what you want automated.

Auto-Format Report

Your Prompt:

Write a VBA macro that: 1. Selects the data range A1 to last used cell 2. Applies a table format with headers 3. Bolds the header row 4. Auto-fits all column widths 5. Adds borders to all cells

AI Returns:

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

Save Each Tab as Separate File

Your Prompt:

Write a VBA macro to save each worksheet in the current workbook as a separate Excel file. Save them to the same folder as the original file. Use the sheet name as the filename.

AI Returns:

Sub SaveSheetsAsFiles() Dim ws As Worksheet Dim savePath As String savePath = ThisWorkbook.Path & "\ For Each ws In ThisWorkbook.Worksheets ws.Copy ActiveWorkbook.SaveAs savePath & ws.Name & ".xlsx" ActiveWorkbook.Close False Next ws MsgBox "All sheets saved!" End Sub

How to Use VBA Code

1. Press Alt + F11 to open VBA Editor 2. Insert > Module 3. Paste the code 4. Press F5 to run (or close editor, then go to Developer > Macros)

Chapter 9: Error Debugging

AI is excellent at diagnosing and fixing Excel errors. Just describe what you're seeing.

Common Errors & Prompts

#REF! Error

Prompt: My formula =VLOOKUP(A2, Sheet2!A:C, 4, FALSE) returns #REF! error. What's wrong? AI: The #REF! error occurs because you're asking for column 4, but your range A:C only has 3 columns. Change 4 to 3 or expand your range to A:D.

#VALUE! Error

Prompt: My formula =A2+B2 gives #VALUE! but both cells look like numbers. AI: One or both cells probably contain numbers stored as text. Fix with: 1. Select the cells > Data > Text to Columns > Finish 2. Or use: =A2+0 to force number conversion 3. Or use: =VALUE(A2)+VALUE(B2)

Circular Reference Warning

Prompt: Excel says I have a circular reference but I can't find it. AI: Go to Formulas tab > Error Checking dropdown > Circular References. Excel will show you exactly which cell(s) have the circular reference. The issue is usually a formula that references its own cell or creates a loop through other cells.

Formula Not Calculating

Prompt: My formulas show the formula text instead of calculating. AI: Three possible causes: 1. Cell is formatted as Text: Select cells > Format Cells > Number 2. Show Formulas is on: Press Ctrl+` to toggle off 3. Manual calculation mode: Formulas tab > Calculation Options > Automatic

Chapter 10: Real Use Cases by Department

Finance

Prompt: Build a 13-week cash flow forecast template with: - Weekly inflows (receivables, other income) - Weekly outflows (payroll, rent, suppliers, other) - Running cash balance - Alert when balance goes below $50,000

Sales

Prompt: Create a formula to calculate win probability for deals: - If stage is 'Qualified': 20% - If stage is 'Proposal': 50% - If stage is 'Negotiation': 75% - If stage is 'Closed Won': 100% - Multiply by deal value to get weighted pipeline value

HR

Prompt: Calculate employee tenure and upcoming work anniversaries: - Start date in Column B - Show years and months of tenure - Flag anyone with anniversary in next 30 days - Calculate average tenure by department

Marketing

Prompt: Build a campaign ROI calculator: - Input: Ad spend, clicks, conversions, revenue - Calculate: CPC, conversion rate, cost per acquisition, ROI % - Compare across multiple campaigns - Highlight best and worst performers

Operations

Prompt: Create an inventory reorder alert system: - Current stock in Column B - Reorder point in Column C - Lead time days in Column D - Daily usage rate in Column E - Calculate days until stockout - Flag items needing immediate reorder

Chapter 11: Best Practices & Tips

The 5 Rules for AI-Powered Excel

1. Always verify AI output. Test formulas with known data before using on real data.

2. Be specific with prompts. Include column letters, row numbers, and exact requirements.

3. Iterate when needed. If the first result isn't right, explain what's wrong and ask for corrections.

4. Ask for explanations. Understanding why a formula works helps you modify it later.

5. Keep a prompt library. Save prompts that worked well for future use.

Common Prompt Mistakes to Avoid

• Too vague: "Help me with Excel" → Specify the exact task

• No context: "Sum column A" → Describe your data structure

• Assuming AI sees your screen: AI can't see your spreadsheet—describe it

• Not handling errors: Ask for IFERROR handling upfront

Your AI Excel Toolkit

Tools you can use today:

• ChatGPT: Free tier available, great for formulas and explanations

• Claude: Excellent for complex multi-step problems

• Microsoft Copilot: $30/mo, built directly into Excel

• Claude in Excel: Beta add-in with cell-level citations

• Excel's Analyze Data: Free, built-in, great for quick charts