Spreadsheet Formula Prompts: Explain, Debug and Test Your Logic
Describe the result you need, diagnose a formula and test it on representative rows. Eight prompts help you work with formulas without treating a plausible answer as a verified workbook.
By LifestyleGlide · Published · 7 min read
A useful formula request includes more than the formula text. Explain what each column means, show a small anonymised sample and state the expected result for at least one row. Specify your spreadsheet application and relevant version or locale so the suggested syntax can be checked.
Work on a copy or a small test sheet before applying changes to a shared workbook. An assistant cannot inspect a file you have not supplied or verify the result merely by explaining its own formula. Preserve the original and compare actual outputs with independently chosen expectations.
Start with this workflow.
Define the rule in words
State the calculation, units, missing-value policy and expected results before choosing functions.
Test a small sample
Include an ordinary row, a blank, a zero and any other case relevant to the rule. Keep private or identifying data out of the example.
Verify in the actual workbook
Check syntax, references and calculated results in your application. Review the effect on dependent cells before expanding the change.
WORKED EXAMPLE
An explicit rule for a zero denominator
Illustrative example: A2 holds completed items and B2 holds working hours. The chosen rule is to show blank when hours are zero; an ordinary test row has A2=30 and B2=2.
STARTING POINTUse =A2/B2 everywhere and wrap every error in an unexplained zero.
AFTER CHECKINGFor this chosen zero-hours rule, an Excel test formula is =IF(B2=0,"",A2/B2). The ordinary test row should return 15 items per hour; a zero-hours row should return blank. Test blanks and text separately and adapt syntax to the actual application and locale.
What changed The example identifies the intended behaviour instead of concealing every possible error. It demonstrates two test cases, not a verified production workbook.
Make your own working brief.
Application, version and locale:
Column meanings and units:
Rule in words:
Formula:
Expected sample results:
Blank and zero policy:
Observed test results:
Dependent cells to check:
Original file or rollback reference:
Choose a task from this guide, add your details and check the answer before using it.
Browse the prompts below.
Spreadsheet formulas ↗
Turn a requirement into a testable rule
Turn a requirement into a testable ruleOpen the prompt +
INPUTS
Desired result, column meanings, units and missing-value policy
PROMPT
Help me define this spreadsheet calculation [result] using these columns [meanings and units]. Missing-value policy: [policy]. Write the rule in plain language and list ambiguous cases I need to decide. Do not choose a business rule for me or write a formula until the important ambiguities are visible.
Draft a formula from a small sampleOpen the prompt +
INPUTS
Application, version or locale, sample rows and expected results
PROMPT
Suggest a formula for [rule] in [application and version or locale]. Columns: [layout]. Anonymised sample rows: [sample]. Expected results: [results]. Explain references and functions, flag any compatibility uncertainty and give a small test procedure. Do not claim you tested the workbook or invent a missing rule.
Explain a formula without changing itOpen the prompt +
INPUTS
Formula, column meanings and application
PROMPT
Explain this formula [formula] in [application]. Column meanings: [meanings]. Describe the calculation and how references behave when copied. Mark assumptions about inputs or named ranges that are not supplied. Do not claim the formula is correct for our task merely because its syntax looks valid.
INPUTS
Formula, error text, sample inputs and expected result
PROMPT
Help diagnose [error text] from this formula [formula]. Application and locale: [details]. Sample inputs: [inputs]. Expected result: [result]. List plausible causes in the order I can check them, distinguishing confirmed evidence from hypotheses. Do not hide the error with a generic fallback before understanding why it occurs.
INPUTS
Original formula, copied destination and fixed values
PROMPT
Review [formula] copied from [original cell] to [destination]. Layout: [layout]. Values that should remain fixed: [values]. Explain which references should move and which may need to stay fixed, then propose a test. Do not assume that a dollar sign belongs on every reference or that the copied result was checked.
Choose a deliberate blank or zero policyOpen the prompt +
INPUTS
Calculation rule, zero cases, blank cases and approved policy
PROMPT
For [calculation], help me implement this approved policy [policy] for zero and blank inputs. Current formula: [formula]. Application: [details]. Explain how the proposed formula treats each supplied case and what it may conceal. Do not substitute blank, zero and unavailable for one another without a stated reason.
INPUTS
Rule, formula, allowed inputs and expected behaviour
PROMPT
Create a test checklist for [rule] and [formula]. Allowed inputs: [inputs]. Expected behaviour: [behaviour]. Include relevant boundary, missing-value, duplicate and text-format cases. Separate cases with known expected results from decisions still needed. Do not fabricate a successful test report.
Document a verified formula changeOpen the prompt +
INPUTS
Old formula, new formula, actual test results and affected cells
PROMPT
Write a change note for [old formula] replaced by [new formula]. Actual test results: [results]. Affected cells and dependencies: [cells]. Explain the reason, verified behaviour, remaining limitations and rollback reference. Do not say all rows were tested if only a sample was checked.
These are original, editable starting points. A prompt does not complete an action outside the conversation. Use approved inputs and review facts, source material and commitments before using a result.
