Skip to main content
MethodScience & Data AnalysisData analysisStarter

Build spreadsheet formulas and models

Working Excel or Google Sheets formulas, explained, with a test case and error handling.

Use this task 3 quick questions, then open it in ChatGPT, Claude or Gemini.

This is a practical, copy-paste process for you know what you want the sheet to do but not the formula. Use it when you want a clear structure, then add your own details.

Published by Deepstory Research. Reviewed Sep 2026.

How to use this method

  1. 1. Copy or downloadCopy the prompt below, or download the method file and attach it to your chat.
  2. 2. Paste into any AIChatGPT, Claude, Gemini or Copilot. Replace each [PLACEHOLDER] with your details, or let the AI ask you.
  3. 3. Add your materialAttach your own files, data or brief so the AI works from your facts, not guesses.

The prompt

Paste it as-is. The AI will ask for anything still in [BRACKETS].

Download .md
# Build spreadsheet formulas and models
Method by Deepstory Research: deepstoryresearch.com/method/spreadsheet-formula-builder

You are an Excel and Google Sheets power user. Follow the method below step by step. Goal: Working Excel or Google Sheets formulas, explained, with a test case and error handling.

## My inputs
- What the sheet should do: [WHAT_THE_SHEET_SHOULD_DO]  (example: look up price by SKU and region)
- Sheet layout: [SHEET_LAYOUT]  (example: column names and example rows)
- Tool: [TOOL]  (example: Excel 365 or Google Sheets)

Before you start: if any input above is still in [BRACKETS] or missing, ask me for it in one short numbered list and wait. Do not guess. If I attached files (data, briefs, Deepstory evidence files), treat them as the primary source.

## Steps to follow
1. Restate the logic.
2. Write the formula (prefer XLOOKUP/FILTER/LET where available).
3. Explain each part.
4. Show a worked test case.
5. Add error handling and an alternative for older versions.

## Deliver the output in this structure
- Formula
- Explanation
- Test case
- Error handling
- Alternative

## Quality rules
- Match the exact column references I gave.
- Label every number as FACT (with source and date), ESTIMATE (show the calculation) or ASSUMPTION.
- If a Deepstory evidence file is attached, use its figures as the reference values and cite its source references; tag claims that are not from it as [UNVERIFIED].
- End with: the 3 assumptions that matter most, what would change the answer, and the single best next step.

What you need to give the AI

InputPlaceholderExample
What the sheet should do[WHAT_THE_SHEET_SHOULD_DO]look up price by SKU and region
Sheet layout[SHEET_LAYOUT]column names and example rows
Tool[TOOL]Excel 365 or Google Sheets

Use it when

  • You know what you want the sheet to do but not the formula.

What you get

  • Formula
  • Explanation
  • Test case
  • Error handling
  • Alternative

The procedure, step by step

Read this before you run it, so you can check the AI actually followed the method.

  1. 1

    Restate the logic.

  2. 2

    Write the formula (prefer XLOOKUP/FILTER/LET where available).

  3. 3

    Explain each part.

  4. 4

    Show a worked test case.

  5. 5

    Add error handling and an alternative for older versions.

Quality checks built into the prompt

  • • Match the exact column references I gave.
  • • Every number labelled FACT, ESTIMATE or ASSUMPTION; claims outside attached evidence tagged [UNVERIFIED].

Evidence for this method

This method runs on your own material: your files, brief or code. If your task needs market or economic numbers, attach a sourced context file from the evidence library alongside the prompt.

Browse evidence

Run next

Methods that chain well after this one.

Deepstory methods are process guidance for AI tools. Review AI output before relying on it; legal, tax, medical and investment decisions need a qualified professional.