10 ways to error-proof Excel files when working from home
Updated: Apr 28, 2020
All Microsoft Excel specialists know that working from home is a great time to dive deeper into the spreadsheets, no matter if you work for a consultancy or are a finance expert. In the home office environment you can avoid or at least manage the distractions. The constant inflow of new invoices or face to face requests for ad hoc reports from programme managers or upper management won't stop you this time. Here are ten easy steps to diagnose, repair and future-proof your spreadsheet templates and financial models and become a Microsoft Excel expert in your (home) office.
1. Find and correct all #-errors
2. Revise lookup range references
Expand reference ranges to full columns if needed.
3. Check and adjust units
EUR or GBP? 1’000s or units? It might not always be so obvious.
4. Document procedures and write manuals
Act as you saw the file for the first time. At a minimum, write: who, when & how. And be accurate. Cell C217 of “2019 Q1 quotations” worksheet is better than “That 10k-ish payment from that coding company”.
5. Remove or hide tabs you don’t need to reveal
No ambiguous notes or complex formulas for non-technical end-users allowed!
6. Organise folders with periodical reports
Remove partial versions, corrupted files and unnecessary copies to help other Excel users.
7. Introduce version control
Add a list of versions, updates and changes. That’s crucial for remote, collaborative work.
8. Create a list of all your periodical Excel reports for months ahead.
Every Excel specialist plans their work early on.
9. Learn VBA, at least the easy stuff.
It’s one of the most accessible programming languages and lets you automate spreadsheets with macros. Most of finance or reporting departments need VBA developers or Excel macro programmers at some point.
10. Check for transposition errors.
Sense check all the metrics and ratios. Start with the most important ones that the management or client will analyse.
Let us know which one's your favourite and if you work in Accounting or Finance, check our 20 improvements for your department.
Michael Sena. The founder of Senacea – MS Excel consultancy & training services provider, based in London, operating in the entire UK. If you are looking for MS Excel Experts or VBA developers to automate and improve your spreadsheet work or help you repair Excel files, contact us now.