Build an Excel Formula from a Plain-English Description of What You Need
You get a working Excel formula, versions for older Excel and Google Sheets, a one-row worked example, and a table of the blank and error cases it handles.
Prompt
You are a spreadsheet specialist who writes Excel formulas for people who are not developers.
Your job: turn my plain-English description into a formula I can paste and use. Before you write anything, ask me these questions in one message, then wait for my answers:
1. What should the formula do, in your own words?
2. Which columns hold your data? Give the column letters, headers, and one sample row.
3. Do you use Excel 365, an older Excel such as 2016 or 2019, or Google Sheets?
4. What should appear when a cell is blank or a value is not found?
Rules:
- Use real cell references from my layout. Do not invent columns I did not mention.
- Explain each part of the formula in one plain sentence, with no jargon.
- Give a version for Excel 365, one for older Excel without XLOOKUP, LET or FILTER, and one for Google Sheets. Say so if two versions are identical.
- Run the formula on my sample row and show the math step by step.
- Wrap the formula so blanks and lookup failures show a clean result instead of an error.
Output format:
1. Formula (Excel 365)
2. How it works (numbered parts)
3. Older Excel version
4. Google Sheets version
5. Worked example (one row)
6. Edge cases table: Situation | What the formula returns
7. Where to paste it and how to fill it down
End with one line listing any assumption you made about my data.Example output
Tips and common edits
Why this works
- Asking for your column layout and a sample row first means the formula uses your real cell references, not generic A1:B10 guesses.
- Separate older Excel and Google Sheets versions stop the common failure where XLOOKUP or LET breaks on another setup.
- The worked example and edge-case table let you check the logic before you trust the formula on hundreds of rows.
Use this in ChatGPT, Claude or Gemini when you know what you want a spreadsheet to calculate but not the formula. Have your column letters, headers, one sample row and your Excel version ready.
Common edits
- Ask for a version that uses a named Excel Table so the references grow as you add rows.
- Add a rule to skip the step-by-step explanation if you only want the formula and edge cases.
Prompt details
- By
- Prompts-GPT editors
- Published
Did this prompt work?
Keep exploring
More like this
Write a Google Apps Script to Automate a Repetitive Google Sheets Task
You get a commented Google Apps Script built around your sheet names, columns and timing, plus install steps, a test plan on a copy, and undo steps.
Check If You Can Scrape a Website and Plan a Polite Scraper First
You get a Go or Don't verdict based on robots.txt, terms and privacy risks, a list of API or export alternatives, and a step-by-step polite scraping plan.
Batch Rename Files in a Folder with a Script You Can Preview First
You get a PowerShell or Bash rename script that shows every old-to-new name before changing anything, writes an undo log, and includes plain run steps.
Combine and Clean Multiple CSV or Excel Files into One Table with Power Query
You get a header mapping table, numbered Power Query clicks, commented M code that fixes mismatched columns and duplicates, and refresh steps for new files.
Plan a Zapier or Make Workflow from a Manual Process You Describe
Get a step-by-step automation plan that maps each manual task to a trigger, filter or action, flags paid-plan needs, and includes failure alerts and a test checklist.
Write an Excel VBA Macro That Cleans and Formats Your Recurring Report
You get a commented VBA macro built from your own column headers, with backup steps, install instructions and a post-run checklist.