Spreadsheets & Excel prompts

Write a spreadsheet formula.

SOFTWARE: [Excel / Google Sheets / other] and VERSION: [if Excel, which version - it determines available functions]

MY DATA LAYOUT:
"""
[PASTE A FEW ROWS INCLUDING HEADERS, WITH COLUMN LETTERS]
"""

WHAT I WANT TO CALCULATE: [PLAIN DESCRIPTION]
WHERE THE FORMULA GOES: [CELL]
WILL I DRAG IT DOWN OR ACROSS: [yes/no, and which]

Produce:

1. THE FORMULA - with correct absolute and relative references for how I said I will fill it.

2. HOW IT WORKS - broken into its parts, inside out, in plain words.

3. WHAT IT ASSUMES about my data: no blanks, consistent types, sorted order, exact matches. Say which assumptions it relies on.

4. WHERE IT WILL BREAK - blank cells, text stored as numbers, trailing spaces, duplicate lookup values, inconsistent date formats, and merged cells. Say which apply to my data and how to guard against them.

5. THE ERROR HANDLING VERSION - wrapped so it returns something useful instead of #N/A or #DIV/0!, and say what it should return instead.

6. THE ALTERNATIVE - if there is a better function for this in my version (XLOOKUP over VLOOKUP, FILTER over array formulas, LET for readability), show it and say when each is preferable. Note if a function I might see suggested elsewhere is not available in my version.

7. A WORKED EXAMPLE - the formula applied to my sample rows, with the result, so I can verify it before trusting it.

Rules:
- Never use VLOOKUP with a column index number if XLOOKUP or INDEX/MATCH is available; column insertion silently breaks it.
- Avoid whole-column references in large sheets.
- If the task would be better done with a pivot table or Power Query than a formula, say so.

Help me clean this data.

DATA:
"""
[PASTE A REPRESENTATIVE SAMPLE - INCLUDE THE MESSY ROWS]
"""

SOFTWARE: [Excel / Sheets]
ROW COUNT: [HOW MANY]
WHAT I NEED TO DO WITH IT AFTERWARDS: [ANALYSIS, IMPORT, REPORT]

Produce:

1. THE PROBLEM INVENTORY - everything wrong with this data. Check for:
   - Inconsistent formatting: dates in mixed formats, numbers stored as text, trailing and leading spaces, inconsistent capitalisation
   - Merged cells, blank rows, headers repeated mid-data, totals rows inside the data
   - Multiple values in one cell
   - Inconsistent naming for the same thing ('Ltd', 'Limited', 'LTD')
   - Missing values, and whether blank means zero or unknown - these are different and conflating them corrupts every calculation
   - Duplicates, exact and near
   - Mixed data types in one column
   - Currency symbols, thousands separators, percentage signs embedded in numbers

2. THE FIX FOR EACH - as a formula, a Find and Replace, a Power Query step, or a manual instruction. Give the exact steps.

3. THE ORDER - some fixes must happen before others. Give the sequence.

4. THE DANGEROUS ONES - cleaning steps that could destroy data: removing duplicates that were legitimately different, trimming something meaningful, or converting text that looks numeric but is not (leading-zero codes, phone numbers, IDs). Flag each and say how to check first.

5. NEVER OVERWRITE THE ORIGINAL - the cleaned data goes in new columns or a new sheet. State this as step zero.

6. WHAT NEEDS A HUMAN DECISION - ambiguities only I can resolve. For each, the question.

7. THE VALIDATION - after cleaning, the checks to confirm it worked: row count unchanged, totals matching, no remaining text in numeric columns, date range plausible.

8. HOW TO PREVENT THIS - if the data comes from a form, an export, or manual entry, what would stop it arriving messy next time.

Help me build a spreadsheet model.

WHAT IT MODELS: [THE SITUATION]
WHAT I WANT TO WORK OUT: [THE OUTPUT]
INPUTS I HAVE: [KNOWN VALUES]
ASSUMPTIONS I NEED TO MAKE: [WHAT IS UNCERTAIN]
TIME PERIOD: [IF IT PROJECTS FORWARD]
WHO ELSE WILL USE IT: [AUDIENCE]

Produce:

1. THE STRUCTURE - three separate areas, and explain why this separation matters:
   - INPUTS sheet: every number someone might change, each in its own labelled cell, colour-coded as an input
   - CALCULATIONS sheet: the workings, one step per row, each row labelled
   - OUTPUT sheet: the results and any charts
   No hardcoded number may appear inside a formula. Every one lives in the inputs area. This is the single rule that makes a model auditable.

2. THE INPUT LIST - a table: Input | Value | Unit | Source or basis | Confidence (known / estimated / guessed)

3. THE CALCULATION CHAIN - each step: what it computes, the formula, and the row it depends on. Someone should be able to trace any output back to inputs by reading down.

4. THE FORMULAS - written out, with cell references based on the layout you propose.

5. SENSITIVITY - the three inputs that most affect the output. Build a simple table showing the output at low, base and high values for each. This is usually more informative than the model's headline answer.

6. THE CHECKS - built-in validation: totals that must reconcile, values that cannot be negative, percentages that must sum to 100. Put these in a visible checks row that shows OK or ERROR.

7. WHAT THE MODEL CANNOT TELL YOU - its structural limitations. A model projects the logic you built; it does not predict reality.

8. DOCUMENTATION - a notes area stating what this models, who built it, when, and every assumption. Models outlive the memory of the person who built them.

Rules:
- No hardcoded numbers inside formulas, ever
- No circular references
- One calculation per cell; do not nest six operations into one formula
- Label every row

My spreadsheet is giving the wrong answer. Help me find why.

WHAT IT SHOULD SHOW: [EXPECTED RESULT]
WHAT IT SHOWS: [ACTUAL RESULT]
THE FORMULAS:
"""
[PASTE THE KEY FORMULAS WITH THEIR CELL REFERENCES]
"""
SAMPLE DATA:
"""
[PASTE SOME ROWS]
"""
SOFTWARE: [Excel / Sheets]

Work through the common causes in order:

1. THE RANGE - does the formula cover all the rows it should? Ranges that stop short after rows are added is the most common spreadsheet error by a wide margin. Check whether the range matches the data extent.

2. TEXT THAT LOOKS LIKE NUMBERS - SUM silently ignores text. A column with some numbers stored as text produces a total that is plausibly wrong rather than obviously wrong. Say how to check.

3. HIDDEN OR FILTERED ROWS - SUM includes them, SUBTOTAL may not. Which am I using, and is that what I want?

4. ABSOLUTE VS RELATIVE REFERENCES - dragged formulas where a reference should have been locked. Check every $ in the formulas I pasted.

5. LOOKUP PROBLEMS - approximate match instead of exact, trailing spaces preventing a match, case sensitivity, duplicate lookup values returning the first match only, and a lookup column that is not the leftmost.

6. DATE AND TIME - dates stored as text, two-digit year interpretation, time zone, and date arithmetic treating a date as a serial number unexpectedly.

7. ROUNDING - display rounding versus actual value. Numbers that look like they should add up but do not, because each is rounded for display only.

8. ORDER OF OPERATIONS - missing parentheses.

9. CIRCULAR OR STALE - a circular reference, or calculation set to manual so the sheet is showing old values. Worth checking early; it produces baffling symptoms.

For each: does it apply to my formulas? Quote the evidence.

Then: THE MOST LIKELY CAUSE, how to confirm it, and the fix. Plus the check to add so this error is visible if it recurs.

Help me summarise this data.

DATA STRUCTURE:
"""
[PASTE HEADERS AND A FEW ROWS]
"""
ROW COUNT: [HOW MANY]
SOFTWARE: [Excel / Sheets]
WHAT I WANT TO KNOW: [YOUR QUESTIONS]

Produce:

1. IS THE DATA READY - pivot tables need clean tabular data: one header row, no blank rows or columns, no merged cells, no totals inside the data, and consistent types per column. Assess my sample and list anything to fix first.

2. FOR EACH QUESTION - the pivot configuration: what goes in rows, columns, values and filters, and which aggregation (sum, count, average, distinct count). Say what each would show.

3. THE STEP-BY-STEP - how to build it in my software, with the actual menu path.

4. THE AGGREGATION TRAP - flag where the obvious aggregation is misleading. Averaging an average is almost always wrong. Counting rows is not counting customers if customers appear more than once. Summing a percentage column is meaningless. Say which applies here.

5. THE CALCULATED FIELD - if a question needs a ratio or a derived measure, show how to add it, and warn that a calculated field computes on the aggregated totals rather than row by row, which changes the answer for ratios.

6. WHAT THE SUMMARY WILL HIDE - pivot tables average away the interesting cases. Note where the distribution matters more than the total, and what to look at alongside it.

7. THE ALTERNATIVE - if a formula approach (SUMIFS, COUNTIFS, or QUERY in Sheets) would be better because the output needs to update or feed another calculation, say so and give it.

8. THE SANITY CHECK - after building it, how to verify the pivot is right: does the grand total match a separate SUM of the source column?

Always include point 8. A pivot table that silently excludes rows is easy to build and hard to notice.

Help me combine these datasets.

DATASET A:
"""
[PASTE HEADERS AND SAMPLE ROWS]
"""

DATASET B:
"""
[PASTE HEADERS AND SAMPLE ROWS]
"""

[ADD MORE IF NEEDED]

WHAT I WANT: [a single stacked list / matched side by side / a summary across all]
THE COMMON FIELD: [WHAT LINKS THEM, if anything]
SOFTWARE: [Excel / Sheets]
SIZE: [ROWS IN EACH]

Produce:

1. THE STRUCTURE COMPARISON - a table showing each column in each dataset, whether it exists in the others, and whether the format matches. Flag: same data under different column names, different date or number formats, different units, and different granularity (one row per order versus one row per order line).

2. THE MATCHING KEY - if joining, assess the common field. Is it truly unique in each dataset? Are there formatting differences preventing a match: trailing spaces, case, leading zeros stripped, different ID conventions? This is where joins silently fail.

3. THE JOIN TYPE - state explicitly what should happen to rows that exist in one dataset and not the other. Keep all, keep only matches, or keep all from one side. This decision changes the result and is usually made by accident.

4. THE DUPLICATION WARNING - if the key is not unique on one side, a join multiplies rows and every subsequent total is inflated. Check my sample and say whether this risk applies. This is the most common and most damaging error in combining data.

5. THE METHOD - for my software and data size: formulas (XLOOKUP), Power Query, or a manual approach. Recommend one and give the steps. For anything over a few thousand rows or a repeating task, Power Query is almost always the right answer.

6. THE PREPARATION STEPS - what to fix in each dataset before combining.

7. THE VALIDATION - after combining: expected row count versus actual, count of unmatched rows from each side, and totals of key numeric columns compared before and after. If the row count is higher than expected, stop - you have duplication.

8. WHAT TO DO WITH UNMATCHED ROWS - do not silently drop them. They usually indicate a real data problem worth understanding.

Help me design a spreadsheet for others to use.

WHAT IT IS FOR: [PURPOSE]
WHO WILL USE IT: [ROLES AND SPREADSHEET SKILL LEVEL]
WHAT THEY WILL DO WITH IT: [enter data / look things up / run calculations / all]
HOW OFTEN: [FREQUENCY]
WHAT DATA IT HOLDS: [DESCRIPTION]
SOFTWARE: [Excel / Sheets] and SHARED HOW: [file share / cloud / emailed copies]

Produce:

1. THE LAYOUT - separate sheets for: instructions, data entry, reference lists, calculations, and output. Never mix entry and calculation on the same sheet; users will type over formulas.

2. THE INSTRUCTIONS SHEET - what this is, who maintains it, how to use it, and what not to change. Put it first. Assume nobody reads it, but its absence guarantees misuse.

3. DATA ENTRY DESIGN:
   - Clear visual distinction between cells to fill and cells not to touch
   - Data validation on every entry cell: dropdowns for categories, date pickers, number ranges. Free text is where consistency dies.
   - Protect every cell that is not an input
   - Sensible column widths and frozen headers
   - No merged cells anywhere; they break sorting, filtering and formulas

4. THE VALIDATION AND ERROR CHECKS - a visible status area showing whether the data is complete and consistent. Highlight missing required fields and impossible values as they are entered.

5. WHAT WILL GO WRONG - predict the misuse: people typing 'n/a' into number columns, inserting rows in the middle, sorting one column independently and scrambling the rows, copying and pasting values over formulas, and emailing copies so five versions exist. For each, the design change that prevents or survives it.

6. THE VERSION PROBLEM - if it will be emailed around, say plainly that you will end up with divergent copies, and what to do instead.

7. GROWTH - how it behaves when the data doubles. Use structured tables or dynamic ranges so formulas extend automatically.

8. THE HANDOVER NOTE - what the next owner needs to know.

9. THE HONEST QUESTION - is a spreadsheet the right tool? If several people enter data simultaneously, if it needs an audit trail, or if it is becoming a database, say so and name what would suit better.