How Can I Quickly Zap the Formulas in a cell range?

When I was working as a Reporting Analyst, I was responsible for generating the monthly EMEA Delivery Management scorecards, whose results would get discussed in a day-long meetings between all senior management members One of the major issues with these scorecards when I inherited them was that there was no consistent way of regularly feeding … Continue reading How Can I Quickly Zap the Formulas in a cell range?

How Can I validate if a user is authorized to view certain data or not?

25th May is approaching fast and so is GDPR. For those of you who are not yet aware - GDPR is a EU-wide regulation on the handling of privacy data A potential implication for all data analysts is that they may have to take extra precautions as to who can access the data they provide … Continue reading How Can I validate if a user is authorized to view certain data or not?

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 re-filter only selected Pivot Tables in my workbook?

I had an interesting case a couple of years back, which resulted in quite a bit of head-scratching on my part. I was working as a reporting analyst and one of my key deliverables was to produce a monthly analysis of the hours that our consultants had logged on different projects. The analysis was to … Continue reading How do I re-filter only selected Pivot Tables in my workbook?

How Do I Quickly Refresh Pivot Tables that have different Pivot Caches with VBA?

As I've mentioned before - if you're working with a single dataset, the ideal scenario would be that your spreadsheet doesn't contain pivot tables with different pivot caches. The ideal scenario is not always possible, however. Sometimes you can't avoid pivot tables that have different pivot caches as your analysis might need to draw on … Continue reading How Do I Quickly Refresh Pivot Tables that have different Pivot Caches with VBA?

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?