10 ways to error-proof Excel files when working from home

Updated: Mar 20, 2023

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 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

Look for #DIV/0, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!.

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 the “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 finance or reporting departments need VBA developers or Excel macro programmers at some point.

Check here why VBA is probably the easiest programming language there is.

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.

Follow us on LinkedIn to see more articles like this one and if you are struggling to choose the right training for your employees, check how to do it here.

Author:

Michael Sena. The founder of Senacea - Analytics Consulting & Process Improvement Company headquartered in London, UK, operating worldwide. 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.

482