In this article, I want to share the ups and downs of using Microsoft Excel in the enterprise, what to watch out for, and how to reduce the risks it may create.
History of Excel in the Enterprise
This week, I had breakfast with a gentleman who retired from working in various accounting roles over his career. We discussed how he had started in paper ledgers and then went to accounting systems and spreadsheets. Spreadsheets have been around for a long time. The first was Dan Bricklin’s VisiCalc, followed by Lotus 1-2-3, and then Microsoft Excel, with a few others thrown in the mix. These were the original “killer applications” that drove PC sales because business users could see computers’ business value. Since then, Microsoft Excel has been the standard for 25 years or more and is almost synonymous with business productivity. You can’t review the applications used in a business without seeing it in multiple areas.
Why is Microsoft Excel in the enterprise so prevalent?
Excel is the darling of business users for many reasons. The first is that it is fast and flexible. It allows data to be structured while not overly strict about how it is structured. This flexibility allows a business to model almost any scenario, like financial projections, inventory allocation, employee bonuses, email lists, and more. If you can type, it can be in a spreadsheet.
Additionally, Excel is robust and generally well understood, placing that power in the reach of users. It is also ubiquitous. You can share a spreadsheet with others in or outside your domain because Excel is a standard part of many business technology stacks or can open it in Google Sheets. It is even available as a web application. And where Excel does not offer what a business needs, many add-ins extend it beyond the out-of-the-box experience.
So Excel is excellent, right?! Yes…but…
Problems with Excel in the Enterprise
There are some problems with using Excel in a business enterprise; let’s consider them.
Because it is so flexible, it can allow for models that look good but may hide errors; this is particularly true of formulas, which can be very convoluted yet not very visible. “Business rules” are also hidden behind values in a cell and are not documented. If a business rule is wrong, nobody may catch it for a significant time. And because a lot of the data in the model is user entered, it can have wrong inputs, so the garbage in / garbage out rule applies. These problems are hard to see in a large model with multiple sheets, or heaven forbid, linked sheets!
Also, because it is so flexible, a user can edit the sheet to manipulate the results. Don’t like the quarterly numbers? A quick formula edit can fix that right up.
Even when completed, spreadsheets all too often emailed around. Jennifer creates a perfect new business expansion model in Excel and emails it to the team. The others review it and tweak it, creating multiple versions of the truth, and the incorrect one is used to make a decision.
And finally, because a departmental subject matter expert often creates these sheets, support also leaves if the creator leaves the organization. Usually, power users do not build spreadsheets using the same requirements for standards and documentation as software applications, reducing the long term support capabilities.
If you have experienced these issues (or you will), you are not alone; the Excel bug has bitten many large and prestigious organizations, including a recent one that tracks the Covid-19 cases in England.
The relationship between IT and business users
Excel became so popular out in the business, away from IT for many reasons, some listed above. But it also goes to the relationship between a formal IT function responsible for the integrity and long term value of data in the organization and users trying to reach organizational and departmental goals.
One of the biggest reasons is users won’t have to wait for IT. When a new software tool is needed, IT, by default, has some necessary steps to undertake, but users do not want to wait for them to understand requirements, do design, build, test cycles, etc. They want the capability now, and it is within their reach to do so. And users have the business knowledge – if you want a fish, why tell someone else how to fish when you can grab the pole and fish? And with all of Excel’s flexibility, they can get going right now and tweak the model later.
Understanding the different perspectives between IT and the business, we will need to account for that in whatever guidelines or standards we create.
How to effectively use Excel in the Enterprise
First of all, you should know my philosophy about IT leadership in the enterprise: There is no reason to tell the business “no” – but you may suggest or show them how to meet their objective with better long term results.
So, we acknowledge the value of Excel in the organization, help the business get the most value from it, and reduce the errors that may occur like those listed above. Here are some guidelines for the best use of Excel in the enterprise.
- Store and share files from one location – do not store files in file shares or email them around as attachments; this creates multiple versions and switches accountability and control from the creator to many people. The best practice here is to use SharePoint, which works exceptionally well with Excel and adds two additional important features:
- Version Control – Make sure the technology team configures your SharePoint document library with version control; this will create a new version of each change so that if something gets changed, you can go back to a previous version and see who changed it. Change history helps with training and understanding why someone changes it.
- Shared editing – Multiple users can open SharePoint-based Office documents, like Excel and Word simultaneously, with all reviewing or editing at the same time. Combining shared editing and a Microsoft Teams meeting to allow video conferencing will increase effectiveness.
- Establish standards to apply colors and formatting to understand which cells are a formula versus user-entered values, etc. In Excel, there are themes to apply color-coding to cells to indicate what a user should expect; this makes it easier to review and audit a spreadsheet for errors. You can see when a formula was expected, but user entry has overridden it. An organization should standardize for all departments, so everybody has a quick way to understand how to use the spreadsheet, even if created somewhere else.
- Excel has always had security or protection capabilities to lock down the file and prevent changes unless one has the password. It is not perfect but can protect the formulas, limit input to specific cells, hide sheets or rows and columns with sensitive data, etc. And when combined with the color-coding above, the model is more intuitive, and users begin to understand how to use it more effectively.
- Offer IT Help – IT should partner with the business experts who build these tools; it amplifies the effectiveness of all and leads to a more agile and proactive organization. Some things IT can help the business with:
- Review the spreadsheets for errors, standards, and better ways to do things. The business can build the tool and then work with IT to review it.
- Provide secure integration with other systems, like ERP or CRM, etc. One of the problems stated above is user data entry errors; you can reduce them using data integrations from central systems to load the spreadsheet and refresh as needed.
- Training – Provide general training on Excel for users, which can be done by an IT staff member, a power-user from the business, or an outside training firm or service.
You do not need to apply these rules to every Excel file the organization creates, but a good starting point is any tool repeatedly used as a critical part of the business process. Examples of these include an estimating tool or an organizational budget tool used annually to collect budgets from departments.
And finally, we recommend a regular review of spreadsheets used extensively for the possible development of applications or the purchase of additional tools that may be a better fit for an organization’s long-term needs.
Want more help?
BizTechSherpa has some predefined standards and approaches for reliable, dependable spreadsheets that will support an organization’s long-term needs. We also have tools to assess the users to see what training may help them. Contact us for these and other ideas of empowering users and getting the most out of the tools available!