1) PowerPivot Pivot Tables allow you to mix and match data from different tables (provided relationships between them have been created in the data model) 2) Summary Functions: PowerPivot Pivot Tables lack the "Product" and "Count Numbers" summary functions that standard pivot tables offer. But they do have the handy "Distinct Count" 🙂 3) Subtotals: Switching … Continue reading 10 differences between a PowerPivot Pivot Table and a Standard one
I've got to convert all those pivot tables into slides and it's taking ages! No worries! It's not all doom and gloom! I did write about a similar technique recently. This one is a little bit trickier because you've got to deal with multiple pivot tables per workbook rather than just one based in a … Continue reading How Do I Quickly Export Multiple PivotTables to PowerPoint Slides?
Can you also make PowerPoints of your analyses for us, so that we can directly use them for our meetings? As much as I like getting new responsibilities, it would be a stretch to say I was jumping with joy at the prospect of spending a good hour or two on the mind-numbing activity of … Continue reading How Do I Quickly Re-filter and Export My Pivot Table Views to PowerPoint Slides?
Resetting the default settings for all my pivot tables takes forever! No worries! Here's a piece of VBA code that makes resetting these a breeze! Why should I bother adjusting, though? Whilst most of these settings are more of a "visual best practice", they can have an impact on how people perceive your analysis Unfamiliar … Continue reading How Do I Quickly Reset a Pivot Table Default Settings with VBA?
If you happen to be retrieving the data for your analysis by directly connecting Excel to a database, you might often encounter fields that have no spaces between their distinct words. Whilst this is indeed best practice when writing your SQL query (having to deal with spaces in SQL aliases can be a very annoying … Continue reading How Do I Quickly Insert Blank Space Between Upper Characters in a Pivot Field Title?
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?