Tag: pivot tables

Posted on

Does Groupby replace pivot tables?

Example of GROUPBY to pivot data

Does GROUPBY replace pivot tables?

The new functions GROUPBY and PIVOTBY are now available in the current channel of Microsoft Excel, and I’ve been playing with them to try to answer this question.

My conclusion is that it isn’t a simple yes/no answer. What is more useful to think about is “when” or “how” GROUPBY should be used instead of a pivot table – and vice versa.

Continue reading “Does Groupby replace pivot tables?”
Posted on

Why does my pivot table default to Count?

If your pivot table defaults to Count instead of Sum, then you have inconsistent numerical data.

I found this out on a webinar about five years ago, and it still seems to be a mostly unknown feature.

And yes, I call it a feature, not a bug, because it is ultimately helping you.

A pivot table showing a Sum of Amounts column.
Pivot table showing a Sum of Amounts column. If this defaults to Count, watch out!

What it is doing is giving you an immediate message that your numerical data has gaps, or perhaps has numbers formatted as text, or other errors. If you ignore this, you might produce inaccurate analysis.

So – if your pivot table defaults to Count, don’t do what I used to do, which was to tut and manually change it to Sum.

Investigate your data carefully until you’ve found the inconsistencies and then try it again until it defaults to Sum. Here’s a link to some suggestions for converting text to numbers.

I love training finance teams in Excel and Power Query – have a look here at what I can do for you.