Excel’s PivotTable has always been a powerful tool for data analysis, but with the introduction of the Recommended PivotTable feature, it has become even more accessible and intuitive to unearth valuable insights from your data. This post serves as a short introduction to this feature using the mpg dataset:
Availability and how it works
Recommended PivotTables leverage the same technology as Analyze Data in Excel, making them accessible in both the 365 versions of Excel and Excel for the web. For AI beginners, it’s advisable to start with this feature before delving into Analyze Data, as it provides additional guardrails to the process.
How to insert a recommended PivotTable
The experience will closely resemble the regular PivotTable process. Simply click on your dataset, then go to Insert > Recommended PivotTables. A menu will pop up to the right of your data, displaying various potentially interesting configurations for the PivotTable:
Keep in mind that, being AI-driven, the results might vary over time due to the probabilistic nature of the algorithm.
Of the presented options, I like the idea of viewing weight by mpg and cylinders. So I’m going to insert it into a new sheet:
This feature is quite impressive, but it’s not all-knowing. For instance, the PivotTable currently summarizes mpg by taking its sum, whereas an average would be more meaningful. However, you have the flexibility to address this by adjusting the value field settings in the PivotTable, just like with any other PivotTable.
In fact, you can continue iterating on this to explore various possibilities, such as breaking down cylinders by origin:
Recommended PivotTables are best viewed as starting points. While they can guide you in the right direction, they won’t handle everything.
Pros and cons of Recommended PivotTables
This feature is beneficial for users less familiar with PivotTables and are looking for a jump-start or those seeking quick analysis without delving deep into PivotTable creation. It’s also useful for those hesitant about more advanced AI involvement.
However, assuming the algorithm is always 100% correct is risky. For instance, the aggregation type for mpg in this PivotTable wasn’t optimal—it was summed, which didn’t make much sense. You may need to take a more interventionist approach when using AI. That’s where Analyze Data comes in, enabling you to query the data more effectively using natural language.
For now, compare your results with the file below:
Did you receive the same recommended PivotTables as shown in this demo? Which ones did you find most insightful and which ones were the most misleading? Share your thoughts in the comments.
Leave a Reply