The existing Recommended PivotTables feature is now smarter in Excel 365 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 has 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.
Pivotby – Excel’s new faster PivotTables
How to Filter Data in a PivotTable
Faster PivotTables with the new Ideas or Insights feature in Excel
