How to Create a Pivot Table Automatically (With and Without AI)
To create a pivot table automatically in Excel, select your data and use Insert → PivotTable (or Recommended PivotTables) and Excel suggests a layout. To skip the setup entirely, an AI tool like Excel AI App reads your columns and builds grouped summaries automatically — you just pick the metric.
Pivot tables are the fastest way to answer “what’s the total by category?” — and you don’t have to build them from scratch. Here’s the quick manual route, then the fully automatic one.
Insert → PivotTable (or Recommended PivotTables) auto-suggests a layout. To skip the setup entirely, an AI tool reads your columns and builds the grouped summary for you.The fast manual route
- Click any cell inside your data.
- Go to
Insert → Recommended PivotTablesand pick a suggested layout, orInsert → PivotTableto build your own. - Drag a category to Rows and a number to Values. Excel defaults to SUM — change it to Average or Count from the field’s dropdown.
Tip: press Ctrl+T to format your source as a Table first. Then rows you add later are included automatically the next time you refresh the pivot.
The automatic (AI) route
Upload the file to Excel AI App and it detects your categories and numeric fields and builds grouped summaries automatically — you choose the metric instead of dragging fields. It’s useful when a sheet has more columns than you want to reason about by hand.
Why a pivot “isn’t updating”
Pivots don’t recalculate live. Right-click → Refresh, or base the pivot on a Table so new rows are captured on refresh. When you need a running total that does update live, a SUMIFS formula is the better fit.