Pivot Tables and Summaries With AI as Your Guide

A pivot table summarises thousands of rows in seconds: sales by customer, expenses by month, stock by warehouse. Many people avoid pivot tables because the field list looks confusing. With AI explaining what to drag where, they become easy.

Prepare the data first

Pivot tables need clean, simple data:

• One header row, with a unique name for each column
• No blank rows or columns in the middle
• No total rows inside the data
• Dates as real dates, numbers as real numbers (see lesson 4)

Select any cell in the data and press Ctrl+T to make it a table. A pivot table built on a table picks up new rows when you refresh.

Let AI plan the pivot

Describe your data and the question, and ask for the layout:

I have an Excel table of sales: Date, Region, Customer, Product, Qty,
Amount (about 5,000 rows). I want to see total Amount by Region and
month, with months across the top. Tell me exactly what to put in Rows,
Columns, Values and Filters in the pivot table, and how to group dates
by month.

The AI will answer something like: Region in Rows, Date in Columns (grouped by Months and Years), Amount in Values as Sum.

Build it

1. Click inside your data, go to Insert → PivotTable, choose New Worksheet, click OK.
2. In the field list on the right, drag fields into Rows, Columns, Values and Filters as the AI suggested.
3. If dates do not group by month automatically, right-click a date in the pivot → Group → select Months and Years.

Not sure where to start? Insert → Recommended PivotTables shows ready-made suggestions for your data.

Ask follow-up questions as you go

Keep the chat open while you work. Typical questions:

• "How do I show Amount as a percentage of the column total?" (Right-click a value → Show Values As → % of Column Total.)
• "How do I sort regions from highest to lowest sales?"
• "How do I show the top 10 customers only?" (Row Labels filter → Value Filters → Top 10.)
• "Why does my pivot show Count of Amount instead of Sum?" (Usually because some Amount cells are blank or text. Fix the data, then change Value Field Settings to Sum.)

Always refresh

A pivot table does not update by itself. After changing the data, right-click the pivot → Refresh, or use Data → Refresh All. Forgetting this is the most common reason a report shows old numbers.

Check the totals

The pivot's grand total should match the total of the original data:

=SUM(Sales[Amount])

If they do not match, ask the AI what could cause the difference. Common reasons are rows outside the source range, filters left on in the pivot, or text values in the Amount column.

Summaries without a pivot

For a small summary that should update instantly, formulas can be simpler. In Excel 365 you can ask:

Give me a formula that lists each unique Region from Sales[Region] and,
next to it, the total Amount for that Region, updating automatically.

The AI will typically combine UNIQUE with SUMIFS. These functions need Excel 365 or 2021+.

Practice task

Use a sales or expense sheet with at least 100 rows. Ask the AI to plan a pivot that answers one real question your manager asks every month. Build it, add one "Show Values As" percentage, refresh it after adding a test row, and check the grand total against SUM of the original data.

Key takeaways

• Clean, table-formatted data makes pivot tables reliable.
• Ask the AI what to put in Rows, Columns, Values and Filters for your question.
• Use Group for months, Show Values As for percentages and Value Filters for top 10.
• Refresh after data changes and check the grand total against the source.