Prompt library Civil servants, Intermediate, Across government, Analysis
Get a spreadsheet formula explained step by step
Describe a calculation in words and get an Excel or Google Sheets formula with a plain explanation of each part, without sharing your real data.
Last reviewed: . Reviewer: Muhammad Asadullah.
Before you paste anything: replace names, CNIC numbers and other personal details with placeholders and never use classified material. Use only AI tools your department allows.
When to use it
You know what you want a spreadsheet to calculate but not how to write the formula. For example, you need totals by district, days between two dates or a count of pending cases. You can describe the layout with column letters instead of pasting real data.
The prompt
Write a [Excel / Google Sheets] formula for the calculation below and explain it step by step.
Layout of my sheet:
- Row 1 has headings. Data starts in row 2 and ends around row [number]
- Column [A] contains [what, for example district name]
- Column [B] contains [what, for example date received, in DD/MM/YYYY]
- Column [C] contains [what]
[Add more columns if needed]
What I want to calculate: [describe in plain words, for example "the number of cases in column C marked Pending for each district in column A"]
Where the result should go: [cell or column]
Give me:
1. The formula, ready to paste
2. A step-by-step explanation of each part in plain language
3. Three or four made-up sample rows, with the result the formula should give for each, so I can test it
4. Common mistakes that would make it give a wrong answer, such as blank cells or dates stored as text
Use only functions that exist in [Excel / Google Sheets]. If my description is unclear or could mean two things, ask me before writing the formula or mark the assumption [CHECK].How to fill it in
- Describe the columns, not the content. Do not paste real names, CNIC numbers or case details.
- Say which program you use and, if you know it, which version. Some functions work in one but not the other.
- Describe what you want in a full sentence, including what should happen with blank or zero values.
Before you use the result
- Test the formula on the sample rows first, then on a few real rows where you already know the right answer.
- Check what it does with blank cells, text in a number column and dates in the wrong format.
- Keep a short note beside the sheet explaining what the formula does, so that a colleague can check it later.
Related guide: How to extract data from documents.