← Writing & Content Creation

Excel · Spreadsheets · Formulas

How to Use AI to Write Excel Formulas

For most people, Excel formulas exist in a narrow band between 'things I know' and 'things I Google for twenty minutes.' AI collapses that gap entirely. You can describe what you're trying to do in plain English — 'I want to look up a value in column A and return the corresponding value from column C' — and get the exact formula with an explanation of how it works. People use it to write VLOOKUP and XLOOKUP formulas, build nested IF statements, create dynamic named ranges, write SUMIF and COUNTIF combinations, and tackle formulas they'd never have attempted alone. Crucially, you can also paste a formula that's not working and ask AI to diagnose the error — which saves enormous amounts of frustration.

5 Best Prompts for Excel Formulas to Ask Claude or ChatGPT

Copy any prompt below and paste it directly into your AI of choice.

  1. Prompt 01 · Describe what you need in plain English

    "I have a spreadsheet where column A has [description] and column B has [description]. I want to [what you want to calculate or find]. Can you give me the exact Excel formula to do this, put it in a code block, and explain what each part does?"

    Best for: translating a business need into a working formula without knowing where to start.

  2. Prompt 02 · Fix a broken formula

    "This Excel formula is giving me [error / wrong result]: [paste formula]. My data looks like this: [describe columns and sample values]. Can you tell me what's wrong and give me the corrected version?"

    Best for: the formula that made sense when you wrote it but produces #REF!, #VALUE!, or just wrong numbers.

  3. Prompt 03 · Build a nested IF

    "I need a formula that checks multiple conditions. If [condition 1], return [value 1]. If [condition 2], return [value 2]. Otherwise return [value 3]. My data is in [describe columns]. Can you write this as an Excel formula and explain the logic?"

    Best for: conditional logic that would take you a long time to nest correctly on your own.

  4. Prompt 04 · Lookup and reference

    "I want to look up [value] in [column/table] and return [corresponding value] from [another column]. Sometimes the value won't exist and I want it to return [blank / 'Not found' / 0] instead of an error. What's the best formula for this in modern Excel?"

    Best for: the classic lookup problem — done properly with error handling.

  5. Prompt 05 · Aggregate with conditions

    "I want to [sum / count / average] values in column [X] only where column [Y] equals [condition] and column [Z] is greater than [value]. Can you write the formula and explain when to use SUMIFS vs SUMIF vs other options?"

    Best for: the kind of conditional aggregation that takes spreadsheets from data storage to actual analysis.