5 super handy things to remember if you’re an MS Access newbie

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

10 Best Practices of Spreadsheet Management

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

How do I quickly align all my Pivot Tables to the same Pivot Cache?

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?

How can I check if I’ve got different pivot caches in my workbook?

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?

How Do I Quickly Get Rid of Unused Range 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?