Microsoft 365, Microsoft Excel, Office 365 /

Smarter PivotTable recommendations coming to Excel

26 May 2022

The existing Recommended PivotTables feature is getting smarter in Excel for Windows with more options and flexibility.

For some time Excel has been able to suggest PivotTables based on the current Table. Recommended PivotTables is in Excel 365, Excel 2021, 2019, 2016 and earlier.

Now in Excel 365 for Windows, Recommended PivotTables is getting more flexibility and, hopefully, making better choices.

Better Flexibility

Recommended PivotTables is moving from a single (modal) window to a side-pane. #AboutTime

Scroll down the pane to see various suggestions for useful PivotTables. Choose the right one or the closest to what you need, then edit the PivotTable.

Change Source

It’s now possible to change the source data for PivotTable recommendations without leaving the pane. Click in the top Source field then either edit the range listing or simply choose another table in the workbook

Insert PivotTable where?

Unlike the old method, the destination for the new PivotTable can be changed anytime.

New sheet – adds a new tab to the workbook

Existing Sheet – click on a location in the current workbook, the PivotTable will be dropped there.

After insertion, all the usual PivotTable options are available.

Better PivotTable suggestions

The existing Analyse Data cloud-based feature is used to make better suggestions or ‘more intelligent’ as Microsoft puts it.

The source data should be ‘clean’ normalized Excel Tables with headers for each column on the top row only.

Double-row headers and merged cells confuse Microsoft’s system.

Complex or nested data should be simplified to a simple table (PowerQuery is good for this).

Limitations

Analyze Data / Recommended PivotTables is limited to 1.5 million cells (not rows).

If that’s a problem, filter the table down to something under that limit (again, PowerQuery is your friend). Once you’ve chosen a PivotTable, change the source to the full table/dataset.

Who gets it?

For starters, the new Recommended PivotTables is gradually appearing in Excel 365 for Windows, Insiders beta channel v2204 build 15128.10000) or later.

At first, it only works for English, French, Spanish, German, Simplified Chinese, and Japanese with other languages promised for the future.

How to Filter Data in a PivotTable
Faster PivotTables with the new Ideas or Insights feature in Excel