There are many things that almost everyone, including myself, did when we first started using excel that are massively counter productive. Hopefully you are reading this article early in your Excel adventures and can avoid making the same mistakes I did.
Mistake 1: Shared Workbooks
If you want your life to quickly descend into a dismal chaotic abyss then set yourself up a shared workbook. Prepare yourself for sharing conflicts, limited user capacity, huge file sizes and angry looks from your colleagues as they slowly will your hard worked spreadsheet more and more into nothingness.
A far better alternative to shared workbooks is to use Microsoft Access which is specifically designed to work with multiple users. It is easy to learn if you already understand the basics of excel and comes included in many office packages. You can still access data you have stored in Access through Excel using Visual Basic if you really want to go for an ultimate user experience.
Mistake 2: Absolute V Dynamic Referencing
It is really frustrating to watch someone manual write the same formula in multiple cells over and over again simply because they don’t understand how referencing works. Microsoft has their cell referencing absolutely spot on but unfortunately not many people have learnt how to use it properly. Watch this video if you want to learn more about Referencing correctly.
Mistake 3: Conditional Formatting
Conditional formatting is without a doubt, fantastic. It creates, very quickly, easily readable content which highlights key areas that you want your user to read. However it is commonly mis-used by those that don’t understand it properly. The most common reason I find when people can’t understand why there spreadsheet is running so slowly is because they have a horrific amount of conidtional formatting. What a lot of people don’t realise is that Excel stores a formula for every cell that has conditional formatting. This means if you have highlighted a column and added conditional formatting to all of it, you will have over 60,000 formulas being calculated every time your spreadsheet changes. If you have done this to 10 Columns, thats over half a million calculations that in the most case, you won’t have needed. It will also store these in your save file making for a massive file size.
Mistake 4: Autofilter
Auto-filter is really good, until you learn pivot tables, at which point auto-filter becomes obselete. Watch this video if you don’t know how to use pivot tables and prepare to have your productivity boosted 100000000000%. (Warning: May cause too much productivity which will leave you with lots of spare time in which your boss will think you are being “lazy”)
Mistake 5: Unique References
If people don’t include a unique reference on a spreadsheet they want me to add data to, I refuse to do anything with it. There is nothing worse than having to work with data that has no field that can be matched to your existing data. The most common reason for this is because included is people names as a field in your data. This is not a unique reference and never will be. So many people have different names and so many people have multiple formats of there names. For Example I have Matt, Matty and Matthew. None of which you would be able to match up without manually going through and matching up data. Which is fine if you have an endless amount of time to spend boring yourself witless.