There are a couple of ways you could approach it: Option 1: Get rid of the default "sum of", "count of" that gets automatically inserted when you're building a pivot table Do comment out the irrelevant lines, though! For example, if you do not have count fields or average fields, make sure that those are … Continue reading How Do I Quickly Change a Pivot Value Field Name with VBA?
Is there a way I could change the number formatting of all my pivot fields without having to spend forever on it?! Sure! The VBA snippet below has some of the most common number formats, which you can directly re-use in your code! PS. Do make sure to un-comment the formats that you do not … Continue reading How Do I Quickly Change a Pivot Field Number Formatting with VBA?
I had an interesting case a couple of years back, which resulted in quite a bit of head-scratching on my part. I was working as a reporting analyst and one of my key deliverables was to produce a monthly analysis of the hours that our consultants had logged on different projects. The analysis was to … Continue reading How do I re-filter only selected Pivot Tables in my workbook?
• You’ve got to change a bunch of value fields that have all been automatically added as “Count” instead of “Sum”? • You need to "average" rather than "sum"? • You’ve suddenly realized that you should be using “Count Numbers” instead of “Count” in a dozen of your spreadsheet pivot tables? Cheer up! You can … Continue reading How Do I Quickly Change a Pivot Table Summary Function with VBA?
As I've mentioned before - if you're working with a single dataset, the ideal scenario would be that your spreadsheet doesn't contain pivot tables with different pivot caches. The ideal scenario is not always possible, however. Sometimes you can't avoid pivot tables that have different pivot caches as your analysis might need to draw on … Continue reading How Do I Quickly Refresh Pivot Tables that have different Pivot Caches with VBA?
The check for multiple pivot caches has shown that your workbook has a couple of duplicated ones? You need to change the source data of all your pivot tables from a dataset stored in your workbook to an ODBC/OLEDB connection? Don't feel like doing it manually? That's completely understandable! As you see above, doing … Continue reading How do I quickly align all my Pivot Tables to the same Pivot Cache?
Okay, I know that I should generally avoid having multiple caches in a workbook... But I've got some Excel reports I inherited from a colleague... How can I check if they're duplicating caches? The easiest way would be to check the source data of each pivot table. This, however, may not be the quickest option, especially … Continue reading How can I check if I’ve got different pivot caches in my workbook?