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?
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?
• 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?
What is unused range? The best way to explain it would probably be "a residual redundant space" - Excel simply remembers the last cell in which you ever had data even after that data is long gone. How do I check if my workbook has unused ranges? Click on a random cell in your sheet … Continue reading How Do I Quickly Get Rid of Unused Range with VBA?