Assuming Excel is your play tool and that you need to handle data worth of 30,000 rows + in order to produce your analyses, below are some tips that could help improve and optimize your spreadsheet usage: 1. Do change the default csv connection properties! • Prompt for file on refresh: achieves nothing but render … Continue reading 10 Best Practices of Spreadsheet Management
• Do you need to provide monthly Excel stats to a bunch of external customers? • Do you need to refresh the data in 50+ workbooks every month? • Are you tired of mind-numbingly opening a workbook after workbook only to press "Refresh All"? There's a way to avoid all these! 1) Put the file … Continue reading How Do I Quickly Refresh All Files in a List?
I really, really dislike non-tabular datasets and systems that spew out data in a spreadsheet format! The only way to crunch any meaningful summary out of such dataset is through some extensive formula usage and the subsequent analysis is an incredible pain to maintain on a regular basis. On top of it, filtering can be … Continue reading How Do I Quickly Refilter all My Tables with VBA?
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?