PivotTables in Excel are a fast, user-friendly way to summarize data and gain quick insights. However, they’re not formula-driven, which can limit control and flexibility compared to dynamic options like GROUPBY() or PIVOTBY() functions that embed logic directly into your analysis.
If you’re counting on Copilot to craft formula-based GROUPBY() or PIVOTBY() functions, you might be let down—it struggles beyond the simplest instructions.
The good news? Copilot shines at generating Python code for PivotTables, letting you streamline the process and build them fast with generative AI. This post highlights quick wins for creating Python-driven PivotTables in Excel using Copilot.
For this exercise, we’ll use the popular student dataset of penguin measurements. We’ll leverage Copilot’s Advanced Analysis with Python features in Excel. If you’re unfamiliar with this tool or how to activate it, see this post:
We’ll begin with a record count. Excel PivotTables struggle here, lacking a built-in unique identifier for distinct counts, often needing manual workarounds.
Generate a PivotTable that counts the number of penguin records for each species.
In contrast, Pandas DataFrames in Copilot/Python natively support unique identifiers with functions like nunique() or, in this case, value_counts(), making counting unique entries simpler and more reliable.
Nice job! That’s one quick win. Now, let’s tackle a more complex traditional PivotTable in Excel. We’ll recreate the PivotTable that Copilot couldn’t handle with GROUPBY():
Find the average bill length and average bill depth grouped by species and island.
Sure enough, Copilot nails it with Python’s pivot_table() function. (You can inspect the Python code in the formula bar of Excel.)
Well done! Now, let’s step it up. The “Pivot” in PivotTable often means breaking data down across columns by category—something the PIVOTBY() function was built for in formula-driven Excel. Let’s create a PivotTable with one category on the rows and another across the columns:
Find the mean and standard deviation of bill length and bill depth. Place species along the rows and sex across the columns.
Sure enough, it works—and with Python and Pandas making stats a breeze, adding the standard deviation was, as expected, no issue! Since this PivotTable is larger, Excel only previews it, requiring expansion into a new worksheet. This highlights why understanding the Python-in-Excel environment yourself is key!
Let’s keep the momentum going! In PivotTables, showing values as percentages isn’t hard but can feel clunkier than it should. With Copilot, we just say it and it’s done! Let’s display everything as percentages of the total:
Count penguin records by species and island. Show each value as a percent to total.
Last but not least, Python and Pandas often shine over Excel in crafting quick measures and calculations on the fly for a single artifact. For instance, let’s calculate the proportion of each species’ average body mass relative to the total body mass.
Create a PivotTable that, for each species, displays both the total count of penguins and the average body mass. Then, compute the proportion of each species’ average body mass relative to the overall average body mass.
And just like that, it’s done!
And on it goes. To be fair, just like regular Copilot, Advanced Analysis can sometimes falter, and I’ve found that restarting your workbook and Copilot session can be useful in those cases.
You may also need to tweak your prompts slightly. Instead of asking Copilot to “create a PivotTable,” you may need to adjust it to “create a summary” or “create a report.” Instead of asking for “the number of penguins,” try “record count” or “the number of observations.”
But in general, thanks to Python’s flexibility and the massive training data behind it, this approach works very well—especially compared to trying to get Copilot to spit out Excel-based, formula-driven PivotTables.
By leveraging Python and Copilot, you’ve now got a slick way to create PivotTables that can save you time and impress your colleagues. But why stop there? You can take this setup to the next level by adding interactive elements like dropdowns, checkboxes, or slicers. These tools let you filter and explore your data on the fly, making your PivotTables not just dynamic but downright user-friendly. Imagine handing this off to a colleague or client and watching them interact with your analysis effortlessly.
And here’s the best part: you don’t have to figure it all out solo. Copilot is there to assist, ready to tackle questions across Python, Excel, and beyond. Need help coding those dropdowns or wiring up a slicer? Just ask and it’s got you covered.
What questions do you have about creating PivotTables with Python and Copilot, or about the Advanced Analysis feature in general? Let me know in the comments.
