Pivot tables made easy with AI

Answers from hundreds of rows, in seconds

Start with your question, let the AI tell you where everything goes, and you'll stop being scared of the pivot table screen.

• Understand what a pivot table is and when to use one
• Turn hundreds of rows into a summary: totals by region, month, product or person
• Ask the AI which pivot table answers your question, and how to build it
• Filter, sort and drill into the numbers behind any total
• Get the same answers straight from the AI when you're in a hurry

Good for: anyone asked to "pull together the numbers", people who've heard of pivot tables but find them confusing, and team leaders preparing monthly figures.

What you need

Do it in six steps

1. Understand pivot tables in one minute

   Imagine 300 sales slips on a desk. A pivot table is like sorting them into piles (by region, say) and writing the total on top of each pile. You choose:

   • Rows: what to make piles of (Region)
   • Columns: an optional second split (Month)
   • Values: what to add up (Total sales)
   • Filters: what to leave out (only "Desk chair")

   You drag column names into those four boxes and Excel does the sums. Change your mind? Drag them somewhere else. Nothing in your original data changes.

   Still fuzzy? Ask the AI for an explanation that fits your job:

   Copy this promptCopy

   Explain what a pivot table is in Excel as if I've never used one. Use an example from my job: I work in [your job, e.g. a school office / a sales team / HR] and my spreadsheets usually have columns like [list a few headers]. Keep it short.

2. Add a column you'll need

   The practice file has Quantity and Unit Price but no total for each order. Add one first. In cell I1 type Total, in I2 type =F2*G2, then double-click the little square at the bottom-right of I2 to copy it down.

   (Not sure how to do something like this in your own data? That's what the formula guide is for.)

3. Ask the AI which pivot table answers your question

   Don't start with the pivot table screen. Start with your question:

   Copy this promptCopy

   I have an Excel sheet with these columns: Order ID, Order Date, Customer, Region, Product, Quantity, Unit Price, Sales Rep, Total. 300 rows.
   
   My manager wants to know: which region sold the most each month this year?
   
   Tell me exactly how to build a pivot table that answers this in Excel (Microsoft 365), step by step for a beginner: what to click, which field goes in Rows, Columns, Values and Filters, and how to show months instead of individual dates.

4. Build it

   Follow the AI's steps. In most versions of Excel:

   1. Click anywhere in your data.
   2. Go to Insert › PivotTable and choose New Worksheet.
   3. On the right you'll see the list of your columns. Drag Region to Rows, Order Date to Columns and Total to Values.
   4. Excel may group the dates into months on its own. If it doesn't, right-click a date in the pivot table and choose Group › Months.

   That's it. You've turned 300 rows into one small table that answers the question.

   Tip: double-click any number in a pivot table and Excel opens a new sheet showing exactly which rows make up that total. It's the quickest way to check a surprising figure.

5. Ask follow-up questions

   The real power is how quickly you can answer the next question. Try these with the AI:

   Copy this promptCopy

   Using the same pivot table, how do I:
   1. Show each region as a percentage of the total instead of in pounds?
   2. Sort regions from highest to lowest?
   3. Add a filter so I can look at one product at a time?
   4. Show the top 5 customers by total sales?
   Give me short steps for each.

   When the data changes, right-click the pivot table and choose Refresh. If you add new rows, ask the AI how to turn your data into an Excel Table (Ctrl+T) first, so the pivot table picks them up automatically.

6. The shortcut, let the AI summarise for you

   Sometimes you don't need a pivot table, just the answer. If your data is allowed to be uploaded (check your company's AI policy, or use the practice file), attach the file and ask:

   Copy this promptCopy

   Here's a sales spreadsheet. Please:
   1. Give me total sales by region for each month, as a table.
   2. Tell me the best and worst performing region overall, and by how much.
   3. Point out anything unusual, like a month that dropped sharply.
   4. Give me the same summary table as a downloadable Excel file.
   
   Show your working for the totals so I can check them.

   Check one number. Compare one of the AI's totals with your own pivot table. If they match, you can trust the rest more. If they don't, ask the AI to explain the difference.

   Which should you use? Use the AI shortcut for a one-off answer. Use a pivot table for anything you'll update every week or share with colleagues, because it stays connected to your data and anyone can check it.

Quick fixes

Use it on your own work

Start with the question, not the boxes

Pivot tables look scary because of all the boxes. Starting with your question and letting the AI tell you where things go removes the fear. After two or three you won't need the AI for the basics, and you'll be the person in the office who can "just pull the numbers".

What to build next