Amazed as I am at how much time has passed since I first wrote the article below and how much more varied experience I've been able to gather since then, I've yet to discover a case where most of these tips wouldn't prove relevant It has now been 6 months since I started studying at … Continue reading The DO’s of a Reporting Analyst
My dataset is too big for Excel to handle efficiently As I've mentioned before, Excel is not a storage place! If you're having problems processing the sheer volume of your data with it, you might need to look into consolidating monthly compilations into a centralized place. Assuming you have no DB access and no ETL … Continue reading 5 super handy things to remember if you’re an MS Access newbie
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
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?
If Excel is your main play tool, more often than not, you will find yourself analysing data by summarizing it in pivot tables. It is important to make sure that all the pivot tables based on the same dataset also share the same pivot cache. But why would I want to do that? You are … Continue reading Why should I align my Pivot Caches?
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?