We must update the header logos of our reports to reflect the latest corporate rebranding! Sure! But doing it manually for 50+ worksheets spread across 5+ workbooks is not exactly a quick update... Nor does it fall under the value-adding category to justify the time spent on manual effort. There is a quick and … Continue reading How Can I Quickly Add My Logo to the Top Corner of All My Worksheets?
Anything more annoying than an over-zoomed Excel tab with visible gridlines? Hating the thought of having to adjust those manually for every single tab in your workbook(s)? No worries! The solution is as simple as 10 lines of super versatile VBA code 😉 Happy VBA coding!
Analysis is finished! You've got all your pivot tables in place, now all you need to do is prep your spreadsheet for your audience - i.e. people should not be able to view your calculation tabs and edit your analysis tabs 🙂 Easy, peasy! Just hide and protect! Yet, if you have a couple of … Continue reading How Do I Quickly Protect/Unprotect Worksheets in My Spreadsheet?
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?
One of my recent articles elaborated on how to change the name of a Value Pivot Field under the assumption that the change had to be applied on all fields in all the pivot tables in the spreadsheet. It may be very often, however, that you only need to change the names of only 1 … Continue reading How do I quickly change the Pivot Field Name of Only Specific Fields?
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?