Prompt Library

ChatGPT Prompts for Pivot Tables

20 copy-paste prompts

20 copy-paste ChatGPT prompts for pivot tables: design, calculated fields, slicers + filters, pivot charts, and the workflows that turn raw data into instant analysis.

Pivot Design

4 prompts

Pivot from Question

1/20

Build pivot table for [analytical question]. Data: [describe columns]. Output: rows, columns, values (with aggregation), filters, slicers. Question-first design > data-dump.

Designs pivots from questions.

💡

Pro tip: Pivot design starts with question, not data. "Sum of revenue by month by region" = clear pivot. "Show me data" = bad pivot. Question first.

Source Data Prep

2/20

Prep source data for pivot table. Output: tabular structure (headers in row 1), no merged cells, consistent data types per column, no totals/subtotals in source. Pivot fails on bad source.

Preps data for pivots.

💡

Pro tip: Pivots need clean tabular data. Merged cells, multi-level headers, blank rows = pivot breaks. Spend 10 min on source prep; saves 100 min of debugging.

Multi-Level Pivot

3/20

Multi-level pivot (rows hierarchy: Year → Quarter → Month). Output: drag fields, expand/collapse behavior, totals at each level, design considerations. Hierarchy = drilldown.

Builds multi-level pivots.

💡

Pro tip: Drag time-related fields to rows in hierarchy = automatic group/drill. User expands/collapses. Same pivot, multiple zoom levels. Underused; powerful.

Pivot Refresh Strategy

4/20

Refresh strategy for pivot. Output: when source changes, manual refresh vs auto, dynamic ranges (table source recommended), consequences of stale pivot. Stale pivot = wrong decisions.

Plans pivot refresh.

💡

Pro tip: Pivot doesn't auto-refresh on source change. Build pivot from Excel Table = source expands automatically. Right-click → Refresh after data change. Discipline matters.

Prompts get you started. Tutorials level you up.

A growing library of 300+ hands-on AI tutorials. New tutorials added every week.

Start 7-Day Free Trial

Calculated Fields + Aggregations

4 prompts

Calculated Field

5/20

Add calculated field to pivot. Example: profit margin = (revenue - cost) / revenue. Output: formula syntax in pivot, field name, where it appears, alternative (calc in source). Use cases.

Adds calculated fields.

💡

Pro tip: Calculated field = derived metric in pivot. Profit margin, growth rate, ratios. Cleaner than calculating in source for analysis-only metrics.

Show Values As — Power

6/20

Use "Show Values As" options. Output: % of total, % of column, % of row, % of parent, difference from previous, running total, rank. Built-in calculations underused.

Uses Show Values As.

💡

Pro tip: "Show Values As" = built-in advanced calculations. Most users only use "Sum" or "Count." % of total = instant share. Difference from previous = period-over-period. Click and explore.

Multiple Value Fields

7/20

Multiple value fields in pivot. Example: revenue + count + average per row. Output: drag multiple to Values, choose aggregation per, formatting per field. Multi-metric pivot.

Builds multi-value pivots.

💡

Pro tip: Single value field = simple metric. Multiple = comprehensive view. Revenue + Count + Avg = volume + frequency + size. One pivot, multi-dimensional view.

Custom Aggregation

8/20

Custom aggregation in pivot. Default: Sum, Count, Average, Min, Max, etc. Output: when to use each, when calculated field needed instead. Match aggregation to question.

Selects pivot aggregations.

💡

Pro tip: Most pivots use Sum or Count. Min/Max/Average reveal distribution. Different question = different aggregation. "Total revenue" = Sum. "Average deal size" = Average. Match.

Slicers + Filters

4 prompts

Slicer Setup

9/20

Add slicers to pivot. Output: which fields slice (categorical), display layout, multi-pivot connection, slicer styling. Slicers = visual filters.

Sets up slicers.

💡

Pro tip: Slicers > drop-down filters for dashboards. Visual + clickable + multiple-selection. Connect to multiple pivots = dashboard interactivity. Standard for Excel dashboards.

Timeline Slicer

10/20

Timeline slicer for date-based pivot. Output: enable, granularity (year/quarter/month/day), connecting to pivot, range selection. Better than date filter for time analysis.

Sets up timeline slicers.

💡

Pro tip: Timeline slicer = visual date range. User drags + sees pivot update. Better UX than date drop-down. Available when pivot has date field.

Filter Strategy

11/20

Pivot filter strategy. Output: report filter (top of pivot, single value), slicers (visual), column/row filters (in pivot table), value filters (top N, conditions). Match filter type to need.

Plans pivot filters.

💡

Pro tip: Different filter types = different needs. Report filter = single context (e.g., one region). Slicer = interactive multi-select. Value filter = top 10. Combine for sophisticated dashboard.

Top 10 / Bottom 10

12/20

Show top 10 rows in pivot. Output: value filter → Top 10, options (top/bottom, count/percent, by which value field), display. Top performers visible.

Filters top 10 in pivots.

💡

Pro tip: Pivot showing all rows = noise for big data. Top 10 by sum of revenue = focus on what matters. Discipline of filtering = readable analysis.

Like these prompts? There are full tutorials behind them.

Learn the workflows, not just the prompts. 300+ easy-to-follow tutorials inside AI Academy — and growing every week.

Try AI Academy Free

Pivot Charts + Reports

4 prompts

Pivot Chart

13/20

Pivot chart from pivot. Output: chart type matching data (bar for compare, line for trend), chart from existing pivot vs new, formatting tied to pivot, refresh behavior. Charts = visual analysis.

Builds pivot charts.

💡

Pro tip: Pivot chart = pivot visualized. Updates automatically when pivot updates. Bar/line/pie depending on question. Most users build separate charts; pivot charts faster + auto-updating.

Pivot-Based Dashboard

14/20

Dashboard from multiple pivots. Output: pivot per metric, slicers connecting, layout, refresh strategy, design. Multiple pivots + slicers = interactive dashboard.

Builds pivot dashboards.

💡

Pro tip: Single pivot = analysis. Multiple pivots + connecting slicers = dashboard. Clicking slicer filters all pivots. Beats Power BI for: small data, occasional users, simple metrics.

Pivot Report Layout

15/20

Configure pivot report layout. Output: compact / outline / tabular (each different), repeat item labels, blank rows, totals on/off, grand totals. Layout matters.

Configures pivot layouts.

💡

Pro tip: Default compact layout = packed. Tabular layout = readable + exportable. Repeat item labels = pivot acts like flat table. Different layouts for different uses.

GETPIVOTDATA

16/20

Use GETPIVOTDATA for [scenario]. Output: when useful (referencing pivot from elsewhere), formula syntax, alternative (regular cell reference), pros/cons.

Uses GETPIVOTDATA.

💡

Pro tip: GETPIVOTDATA references pivot cell by content (not position). Survives pivot restructure. Power-user for dashboards built on pivots; most disable by default.

Common Issues + Fixes

4 prompts

Pivot Field Not Showing Up

17/20

Pivot field missing. Common causes: source range doesn't include field, field has all-blank values, pivot cache stale (refresh fixes), field type wrong. Output: troubleshooting steps.

Troubleshoots missing fields.

💡

Pro tip: Missing pivot field = check source range. Often new column added to source after pivot built; pivot doesn't auto-include. Refresh + check source range.

Date Grouping Issues

18/20

Pivot date grouping not working. Causes: dates stored as text (need conversion), mixed formats, blank cells. Output: diagnosis + fix. Date issues = #1 pivot problem.

Fixes date grouping issues.

💡

Pro tip: Dates as text = pivot treats as text (no grouping). DATEVALUE / Text-to-Columns to convert. Verify with ISTEXT formula. Single bad row breaks grouping.

Pivot Performance

19/20

Slow pivot performance. Causes: huge data (>100K rows), volatile formulas in source, multiple pivots same source. Output: optimizations (Power Pivot for large data, single source, refresh manual).

Optimizes pivot performance.

💡

Pro tip: Pivot on 1M rows = slow. Power Pivot (Excel data model) = millions of rows fast. Or: aggregate in source first; pivot on aggregated. Tool match matters.

Drill-Down Behavior

20/20

Pivot drill-down. Output: double-click a value cell → new sheet with rows that aggregated. Use cases (verifying numbers, exploring outliers). Drill-down = quick exploration.

Uses pivot drill-down.

💡

Pro tip: Drill-down = double-click any pivot value. Excel creates sheet with underlying rows. Verify numbers, explore outliers, debug. Underused power feature.

Frequently Asked Questions

Pivot tables: in-spreadsheet analysis, simple, fast for small-medium data. Power Query: data transformation pipeline. Power BI: dashboards + sharing + governance. Most analysts use all three; different layers.

AI describes pivot setup; you drag in spreadsheet. ChatGPT can't click. Microsoft Copilot in Excel can build pivots. AI as instructions; spreadsheet as execution.

"Show Values As" options (% of total, running total, etc.). Most users only Sum/Count. Built-in advanced calculations = the productivity unlock.

Pivot: instant, visual, flexible exploration. SUMIFS/COUNTIFS: in-cell, formula-driven, dashboard-friendly. Pivot for ad-hoc; formula for repeatable reports. Both useful.

Dates stored as text. Pivot can't group text. Convert: Text-to-Columns or DATEVALUE. Verify with ISTEXT. #1 pivot problem; quick fix.

Prompts are the starting line. Tutorials are the finish.

A growing library of 300+ hands-on tutorials on ChatGPT, Claude, Midjourney, and 50+ AI tools. New tutorials added every week.

Start 7-Day Free Trial Explore AI for Data Analysts

7-day free trial. Cancel anytime.

Browse All Prompt Collections