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?
You know that annoying bit when every time you’ve refreshed your pivot table, you also need to re-size your columns? This is how you can prevent it from happening Do you see #DIV/0! errors in your pivot tables? No worries, Excel is just reminding you that you cannot divide by 0 Tip: You can also type the value/text you … Continue reading 5 Default Pivot Table Settings You Should Reset
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?
I still very vividly remember my first ever lecture as an MSc student - it was a "Business Intelligence and Data Management" class and the teacher was explaining about the major discrepancies in data formats that can emerge in large-scale organizations... Wow! I've actually seen each one of them when I was working as an … Continue reading 7 most common data integrity problems
Let's face it - few analysts are lucky enough to work with clean data, obtainable from a single database. I certainly wasn't among the lucky few when I was working as a Reporting Analyst, which meant that before I was even able to produce a standard Excel report, required on an ongoing basis, I had … Continue reading How Do I Quickly Update User Paths?
Can you give me a list of all the product information that we hold for these products? As a Business Analyst, that is a fairy simple question to answer - just a matter of a couple of joins and a where-clause. What could make it annoying and unnecessarily time-consuming, however, is the necessity to use … Continue reading How Do I Quickly Enclose String in Quotes?
I want to remove all the hyperlinks in my spreadsheet That's super easy - just right click on the column with the hyperlinks and click "Remove Hyperlinks". A problem arises when you want to add them quickly back in, as there is, unfortunately, no option of doing so, other than right-clicking on a link after … Continue reading How Do I Add/Remove links with VBA?