HomeCasa Pivot table guide built around the question you need answered
Learn to build a pivot table in Excel or Google Sheets from your own columns: data prep, fields in rows, columns and values, grouping dates, extra views and common errors fixed. Designed for ChatGPT, Claude, Gemini and Microsoft Copilot. It includes 4 customizable variables and you can copy it for free in one click.
Beginner · Text · Pending editorial review
Prompt
Act as an Excel and Google Sheets trainer who teaches pivot tables to people who have never used them, with concrete steps and no fluff.
My case:
- Tool and version: {{tool}}
- My data columns (with an example value for each): {{columns}}
- The question I want to answer: {{question}}
- Approximate number of rows: {{rows}}
If the question cannot be answered with these columns, tell me and suggest what data is missing.
Deliverables:
1. A data check first: a single header row, no merged cells, no blank rows or subtotals, one data type per column. How to turn the range into a table so the pivot table grows with the data.
2. The exact steps to create the pivot table in {{tool}}, noting that menu names can vary by version and language.
3. The layout for my question: which field goes in rows, columns, values and filters, and why.
4. Value settings: sum, count or average; show as a percentage of the total; number format.
5. Grouping dates by month, quarter or year, and sorting or filtering to the top 10.
6. Two or three extra pivot tables that would help me dig deeper into the same question.
7. Slicers or interactive filters and a recommended pivot chart.
8. Common errors and fixes: count instead of sum because numbers are stored as text, new data not showing after refresh, dates that will not group, double-counted totals.
Constraints:
- Do not invent results: describe what I will see, not specific figures.
- Use my column names exactly as given.
- Explain each step in one line.
Format: numbered steps, a pivot layout sketch (rows, columns, values, filters) and a troubleshooting list.How to use it
Copy your column names with one example value each and write the question in plain words. Follow the steps with your file open, then try the extra pivots once the first one works.
Usage example
Tool: Excel for Microsoft 365 · Columns: date, store, product, category, units, revenue · Question: which category sells the most each month in each store?
Sample output
1. Before you start: check the data
- One header row, no merged cells, blank rows or subtotals.
revenueandunitsstored as numbers (right-aligned),dateas a real date.- Turn the range into a table: Ctrl+T, so the pivot table grows with the 8,000 rows and any new ones.
2. Create the pivot table
- Click anywhere inside the table.
- Insert → PivotTable → New Worksheet (menu names may vary by version and language).
3. Layout for your question
| Area | Field | Why |
|---|---|---|
| Filters | — (optional: product) | To focus on a single item |
| Rows | store, then date grouped by Years and Months | Each store, month by month |
| Columns | category | Compare categories side by side |
| Values | Sum of revenue | Sales in dollars |
4. Value settings
- Value Field Settings → Sum (not Count).
- Show Values As → % of Row Total: you will see each category's share within each store and month.
- Number format: currency, no decimals.
8. Common errors
- You see "Count of revenue": numbers are stored as text → convert them and refresh.
- Dates will not group: a blank or text cell in
date. - New data missing: PivotTable Analyze → Refresh (or Ctrl+Alt+F5).
…
Key facts
- Category
- Data management · Data analysis
- Output type
- Text
- Level
- Beginner
- Designed for
- ChatGPT, Claude, Gemini and Microsoft Copilot
- Verified in
- Pending editorial verification
- Variables
- Tool, Data columns, Question to answer and Approximate rows
- Language
- English
- Price
- Free, no sign-up
- Updated
- Nov 7, 2026
Frequently asked questions
Why does my pivot table count instead of sum?
Usually because a cell in that column contains text or the numbers are stored as text. The prompt includes how to spot and fix it.
Does it work for Google Sheets?
Yes. Choose Google Sheets in the variable and the steps adapt to its pivot table editor, which is laid out a little differently from Excel's.
What is this prompt for?
Learn to build a pivot table in Excel or Google Sheets from your own columns: data prep, fields in rows, columns and values, grouping dates, extra views and common errors fixed.
Which AI tools does it work with?
It is designed for ChatGPT, Claude, Gemini and Microsoft Copilot. We have not verified it in these tools yet, so results may vary between them.
How do I customize the prompt?
Click “Customize” and fill in the variables (Tool, Data columns, Question to answer and Approximate rows). The text updates instantly and you can then copy it in one click. Values are stored only in your browser.
Do I need experience to use it?
No. It is a beginner-level prompt: just fill in the details and paste it into your AI tool.
Is this prompt free?
Yes. Every HomeCasa Prompts prompt is free, with no sign-up or subscription. You can also use them in professional projects.
Can I trust the AI output?
Treat it as a high-quality draft: always check data, figures and important claims before publishing or making decisions.