You are using an Excel spreadsheet directly or as a template for your task in various business projects? Every Excel spreadsheet and Excel template in operative business – a.k.a. production or operations – needs the same quality and due diligence measures as all software tools do.
The serious treatment of Excel spreadsheet templates when used for operative business is what separates a professional from someone hacking (sic!) in a local gardening association (no offense to gardeners!).
This post is available in German, too: Excel-Tabellen im operativen Betrieb – es ist Software!
Excel spreadsheets are everywhere
Excel, critically acclaimed as the second best tool for everything, is used everywhere and for everything. While some people and organizations go crazy and use its Visual Basic scripting engine to create massive giants of functionality on top, many more use Excels formulas, filters, and pivots for just about everything from finance to planning to engineering to reporting.
As a versatile tool, Excel is being used in every business area that I’ve seen so far. Taking that as a fact, there is no denying of its pervasiveness in today’s office landscape.
Many uses of Excel spreadsheets and Excel templates in particular are driven by individuals, taking their Excel tool into wider use, handing them out to colleagues for everyone’s benefit. At least thinking so.
Excel spreadsheets are business
Ever so often an Excel sheet starts out as a quick personal calculation or analysis of some sorts, but then persists as a tool for business decisions, used by many people and living for many business cycles.
This longevity is what makes it a risk for your business. Because a quick hack in operations might have serious downfalls with people relying on it.
What works for you, with your specific way of putting in things, might as well produce wrong results when putting in data in a different way, or with different assumptions about how your formulas work. Wrong results then lead to wrong decisions, wrong decisions then enable business risks.
Excel spreadsheets are software
For sure Excel as a program is software, but think further: Excel sheets that analyze or calculate something based on inputs are software themselves.
What you do when you hack together a few formulas: you create a program. Software. You might not see yourself as developing software, but the minute you are creating Excel formulas and using them repeatedly, you are developing software.
And if you use its results repeatedly for decision making in your business, you are using Excel sheets as software in operations.
Excel spreadsheets in operations
Software in operations needs special care and consideration.
You would not ship out software to customers and let them find out how reliably it works, without verification and validation. The same holds true for Excel sheets in production.
If you are making business decisions based on Excel sheets, you need software quality procedures for your Excel sheets.
Once you move over that fine line from your personal spreadsheet artistry to a spreadsheet in production, the following aspects become necessary:
1. Versioning. How can you find out what formulas you had seven weeks ago in that spreadsheet?
2. Releases. Once you are confident on the version you just finished working on, how do you make this a release for wider use?
3. Deployment. If people are using your spreadsheet as a template for different projects with different data , how do you get the file into their hands? And how do you get an updated release into their hands?
4. Updates. Excel templates that are deployed might need an update. How do you do that effectively? Is it even possible in your specific case, or are data entry, formulas, and results presentation all integrated into one table?
5. Tests. How do you ensure the Excel spreadsheet does what it is supposed to do, and nothing else? You need test cases, both to verify and falsify behavior. Try to break every formula with nonsensical inputs to your Excel sheet.
6. Code review. Have at least someone else but yourself looking into the formulas and verify their understandability. It helps no one if you are the only person on the planet that knows how this spreadsheet works. How readable are your template’s formulas?
7. Usability. Are the users of your Excel template actually able to use it for its purpose? Are things self-explanatory? What level of Excel familiarity is required for use?
8. Simplicity. How can you keep it simple? Some things are better left unautomated, some corner cases are better excluded early on rather than working around them in your core algorithm.
9. Documentation. You might think your Excel sheet is pretty simple and obvious to use, but ask someone else: It might not be. How will you document your software?
10. Support. How are you going to support that application? How much time do you need to allocate to user questions, problems, and bug reports?
This list of ten aspects holds some more stuff in it.
Excel spreadsheets as software in operative business – details
With the brief list above, there is a bit more nuance to each of the aspects to be given. Feel free to disagree, but also acknowledge much of this might just come directly from any software engineering textbook.
1. Versioning
No, just having your template sitting in some local folder or on some shared drive will not do. If your Excel spreadsheet template is used in production, you need to be able to recover versions that you have shared with colleagues months ago.
Implicit versioning like in Confluence or SharePoint is a great method, but you may really want to consider a versioning software like Subversion or Git. SharePoint, in most installations, has a limit on the number of versions it keeps, maybe a few hundred. Sounds a lot, but is not when having autosave on in Excel. That makes SharePoint’s implicit versioning perfectly fine for going back a few days or weeks, but really not suitable for software development – which is what you are doing here.
2. Releases
There is a difference between making a few tweaks to your Excel spreadsheet template and getting interrupted before finishing up, and actually handing that file over to colleagues – users – for use in operations. You need to consider the term release. Once your application has its set of features ready and implemented, making it a release is the next step. Especially points 3, 4, 5, 6, and 9 are relevant for this.
It is not important which versioning scheme you are using, simply go with the Subversion number for the version, or with the Git version hash.
If you are still actively working on your Excel app, do consider regular releases, e.g. every four weeks. Having recurring cycles –a takt– makes things predictable for your users. Of course, whether it makes sense to have regular releases depends heavily on how you can update existing instances in the field (see point 4).
3. Deployment
You will need some sort of deployment mechanism. Absolutely do not send the template around via email. Never. Ever.
You may e.g. create an intranet page for your Excel app, alongside documentation (see point 9).
Also, do include the exact version number or reference of your template in the deployment mechanism, so that people know which version they got.
4. Updates
This one can be really hard to achieve, and it is one of the hard downsides of Excel templates. Once you deployed an Excel template, it requires manual labor to update its logic from a new version of the template, because data and code are integrated into the very same file.
You may end up needing to write an update documentation, with the exact steps needed to update an existing sheet. That of course will require code reviews to be done for every instance of the used template.
So you may also end up not being able to update templates in the wild. People may have to stick with what they got and only use the new version for new projects. Not ideal, but pragmatic.
5. Tests
If you are writing software, you need to test that software. And yes, that means filling your template with numerous different sets of values and see how it behaves. You need to verify your software.
Save those test case files in your versioning system, and of course note in the Excel sheet which version of the template you are basing your test on.
Have someone else test your Excel sheet, and document in the same way. You will find data entered that you did not anticipate.
6. Code review
Code that only you understand is a huge business risk. Don’t do that, even if you feel flattered by the fact that you are the only one being able to maintain. Excel formulas can be easy to understand or really cumbersome and hard to read.
Ask an Excel crack for a code review. Ask them how long it took them to understand what your spreadsheet is doing and why. Ask them to hypothetically change part of the algorithm, and ask them how hard your code made it for them.
Your Excel sheet does not need to be newbie maintainable. A rock solid understanding of Excel’s formula engine is fair to assume, but don’t dive into Visual Basic for Applications (VBA).
7. Usability
Your Excel spreadsheet should be for human colleagues to be used. Also, you might not want to be the maintainer for years and years to come. Think before handing out the template. What will people struggle with? Do a small field test with a colleague, or better with two colleagues: The Excel crack you asked for code review, and the more basic user.
Usability is big, it is a make-or-break for your software. If people cannot use it correctly because it is hard to use at all, then you are running the risk of people making wrong assumptions and getting wrong results for their decision making.
Usability though is directly linked with the next aspect: simplicity.
8. Simplicity
The dichotomy of intermediate results: Just because you technically can have everything calculated in one single cell with a few hundred characters of formula text does not mean you should do it. On the other end of the spectrum, just because you can have a separate column for each intermediate result does not mean you should do it. Strive for simplicity.
The dichotomy of sheets: Just because you can have everything on one giant sheet does not mean it is a good idea to do so. On the other end of the spectrum, just because the database normal form indicates you should create dozens of sheets in your Excel template does not mean you really should do it. Strive for simplicity.
The dichotomy of automation: Just because you can highlight everything with conditional formatting does not mean you should do it, because maintaining that is really hard. On the other end of the spectrum, just because you do not like formatting at all does not mean you should leave your Excel application bare naked.
9. Documentation
Write something, best either directly in your Excel file as a separate sheet, or in an accompanying intranet page. For documentation updates and the possibility to ask questions, do yourself a favor and go for the intranet solution.
Keep the documentation up to date. Especially when your update procedures do not allow instant updates in the field, you will have multiple versions of your spreadsheet in production simultaneously. This also means you need to have the documentation for each of those versions available. New bugs might become apparent for older versions, which you will need to make a part of the old versions’ documentation, too.
So do factor in serious time effort for documentation.
10. Support
Good intent does create Excel applications, but serious time invest supports them. Plan for enough capacity on questions, bug reports, setup issues from your users. This might also influence your decision on whether or not to put this into production in the first place.
Also, who will support the Excel spreadsheet or template when you are on vacation? When you are on a business trip? When you are sick? Every maintainer of a software in production needs at least one substitute that can fully cover for them. Which means that your lonesome project can no longer be so lonesome.
Conclusion: A conscious decision
Excel is not just the second best tool for everything, it also sounds like a pretty cheap tool for your business logic, because you already have it. Think it through, this might hold true for the license cost, but not for total cost of the application, because it will require not just your time, but also time of others helping you, and even time of users. If not done properly, you are creating a business risk. If done properly, you will have to consider and invest time in a lot of aspects.
You want to take a conscious decision about whether really investing further time into your Excel spreadsheet solution is the way to go, or whether you are doing your organization a greater favor by looking for a specific software solution that in itself is suitable to be deployed into operations, that has standard documentation, updates and support provided by a vendor. It is worth asking whether creating and maintaining this piece of software is really what you and your organization should be doing, whether it really is in line with your mission.
Using Excel in operations is not always the best idea, for exactly the reasons above. Yet, if this post helps you to make Excel work in your operative context, it has done its purpose. Leave your comments below on what you think about Excel in operative business.
This post is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation.
Icon credits: Merge by Marko Fuček from the Noun Project | release by Adrien Coquet from the Noun Project | Deploy by Razmig Getzoyan from the Noun Project | update by shashank singh from the Noun Project | test by Nithinan Tatah from the Noun Project | code review by Danil Polshin from the Noun Project | usability by Adrien Coquet from the Noun Project | simple by Marc Torrada from the Noun Project | documentation by Viktor Vorobyev from the Noun Project | support by Adrien Coquet from the Noun Projec. The Excel logo is a trademark of Microsoft.
Leave a Reply