1.Excel for MAC does not support PowerPivot. Consequently, any pivot table based on the Excel Data Model will be unfilterable if the spreadsheet is opened by a MAC user. So will be any pivot table, that was created via the "Import from Microsoft Access Database" option as Excel for MAC does not support OLEDB connections, either. It … Continue reading 6 Major Differences between Excel 2016 for Windows and Excel 2016 for MAC
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
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
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
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?
I shudder at the sight of merged cells in a spreadsheet. Sure, they may make Excel scorecards look neat and pretty... They also make them a pain to maintain since merged cells interfere with sorting and pasting as values There's nothing more annoying than being unable to paste as values on top of cells that … Continue reading How Do I Quickly Un-merge cells with VBA?