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?
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?
But how do we get it there in the first place? There are a couple of options - choosing the best one for your case will very much depend on the volume of data you need to analyse and the data infrastructure that your company provides. So far I've used: 1. Import from text (a.k.a. … Continue reading Let’s crunch this data in Excel
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
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?
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?
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?