Creating Pivot Tables and Summary Reports
Pivot tables are one of the most powerful features in Excel and Google Sheets, letting you summarize thousands of rows into a compact, reorganizable report in a few clicks. They are also one of the most intimidating features for people without a technical background, because the menu of options, rows, columns, values, filters, is not self-explanatory the first time you see it. This lesson shows how AI can guide you through building a pivot table even if you have never made one before.
What a pivot table actually does
Before diving into the mechanics, it helps to have a plain language grasp of what a pivot table is for. A pivot table takes a long list of raw data and groups it by one or more categories, then calculates a summary, usually a sum, count, or average, for each group. If you have a list of every individual sale made this year, with columns for date, region, product, and amount, a pivot table can instantly show you total sales by region, or total sales by region broken down further by product, without you writing a single SUMIFS formula. If that description still feels abstract, asking an AI tool to explain pivot tables using your own actual column names, rather than a generic example, often makes the concept click much faster.
Getting AI to plan the pivot table before you build it
The single most useful thing AI can do here is help you decide what should go where, before you open the Insert PivotTable dialog. Describe your columns and your goal, for example: “I have a sheet with columns Date, Region, Salesperson, Product, and Amount. I want a summary table showing total Amount for each Region, broken down by Product, with an overall total. What should I put in the pivot table’s Rows, Columns, and Values areas?” AI tools reliably handle this kind of layout planning question well, typically suggesting Region in Rows, Product in Columns, and Amount, set to Sum, in Values, and explaining that a Filter field could be added if you also want to look at one Salesperson at a time.
Using in-app AI to build the pivot table directly
Both Microsoft Copilot in Excel and Gemini in Google Sheets can go a step further and build a suggested PivotTable or PivotTable-style summary directly from your data when you ask, for example “create a summary showing total sales by region and product.” Excel’s own Recommended PivotTables feature, available even without Copilot, also analyzes your selected range and proposes a few sensible starting layouts you can pick from and then adjust. Whichever route you take, treat the first version as a draft: check that the total in the summary matches a total you calculate independently, for instance comparing the grand total in your pivot table against a simple SUM of the whole Amount column, to confirm nothing was accidentally excluded.
| Pivot table area | Plain language meaning |
|---|---|
| Rows | The categories you want listed down the left side, one row per group, for example each Region |
| Columns | A second layer of categories spread across the top, splitting each row further, for example each Product |
| Values | The number being summarized for each group, and how, such as Sum of Amount or Count of Orders |
| Filters | A dropdown that lets you view the whole report for just one category at a time, without rebuilding it |
Common pivot table adjustments AI can help with
- Changing the summary calculation. If your pivot table shows Count instead of Sum, or you want Average instead of Sum, ask AI how to change the value field setting, since the click path differs slightly between Excel and Google Sheets.
- Grouping dates. A common frustration is a pivot table listing every individual date as its own row. AI can explain how to group dates into months, quarters, or years, which is usually a right-click option on a date field in Excel, or a manual grouping step in Google Sheets.
- Filtering out unwanted rows. If test data, cancelled orders, or blank rows are skewing your totals, AI can suggest adding a filter field or cleaning the source data first, whichever is more appropriate to your situation.
- Formatting for presentation. Pivot tables often need number formatting, such as currency symbols or percentage signs, applied after creation, and AI can talk you through exactly where those formatting options live.
From pivot table to written report
Once your pivot table is built and checked, you can paste a copy of its summarized numbers, which is now a small, manageable table rather than thousands of raw rows, into a standalone AI chat tool and ask for a short written report describing the key findings. Because the pivot table has already done the heavy lifting of aggregation, the AI’s job at this point is much simpler and more reliable: describing a compact, verified table in plain English, rather than trying to interpret an enormous raw dataset on its own.
