A really quick tip today. Some of you may find when inserting a pivot table, that your numeric values are counted rather than summed:
Why? Well usually, this is down to missing data – blank values in your numeric columns. Since
(blank) can’t be summed, Excel reverts to counting the values that are available.You can see the source problem in my bogus data here:
This is relatively easy to sort – we just need to change the “Summarise Values By” option to Sum, and we’re off – Excel wil do the rest for you. However, when there are a lot of columns to run through, this can be time consuming, and not just a little bit boring:
One handy tip is to do this with a combination of the keyboard and mouse. Using the “Summarise Values By” option in the Pivot Table Tools Options context menu in the ribbon is much quicker, and works on the active field in your table. Utilising this function, and the arrow keys to switch between fields makes the process much quicker.
Just a short hint, but I hope you find it useful!