As an operations and procurement professional, Microsoft Excel is the most powerful tool in my software toolbox. I’ve been using it every workday without fail for over 20 years, and I cannot imagine my work life without it. From charts to pivot tables to macros that query data sources, Excel saves me incalculable time and enables me to do nearly every aspect of my job. During those same 20 years, however, I’ve come to understand the restrictions and challenges of using spreadsheet software (I see you, Google Sheets and Lotus 1-2-3!) for jobs they’re really not meant to do.
So, let’s look at some examples of “Out of Place Excel” and some alternative software choices you can consider to avoid the pitfalls.
The Data Firedrill
We’ve all been there. The boss has made a data request – maybe it’s the annual savings pipeline projection, a request for ongoing negotiations and their statuses, or a list of major contracts and expiration dates. The key here is that this data is *not* in one of your existing systems, and you can’t pull it out by a report. So, an enterprising colleague creates a 17-column spreadsheet and sends it out to the group. She asks everyone to enter their data and send it back to her, where she will compile it.
What’s the problem? First is distribution, the coordinator has to ensure everyone has the spreadsheet and she has to manage pulling it all together. Second is version control, after the first iteration of the spreadsheet, different team members or even the boss will ask for a new field, or a field displayed in a different way. She will resend the spreadsheet and ask everyone to copy/paste into the new format or have to spend time on the phone requesting and updating. Next, she will run into issues with data integrity, as people sort, shift and transform the spreadsheet versions. Finally, this data exists in a “point in time”; when the data request comes in later this year, the exercise will need to be revisited, likely from scratch. The fire drill substantially interrupts the department’s primary function, and often the boss is unhappy with the less-than-accurate results and the time it took to gather the data. Nobody is happy.
What to do instead? Collect your data using a web form or build the required fields into your core database. Short-term, Google Forms (if permitted by your IT policies) can provide a streamlined way to collect substantial amounts of data, which can then be transformed and stored as needed. If the data exercise is one that’s likely to be repeated throughout the year or in future years, consider creating a custom form in your ERP or P2P system, and creating a process to keep this data updated on an ongoing basis. Once it’s in a connected relational database, you can also use powerful analytics tools to provide deeper insights to the data, spending your time on analysis rather than data collection.
The Big Old List
This is a long list of something that you, or multiple people in your organization, maintain so you can look information up. Again, the key here is that this spreadsheet is the sole or primary source for the data. Maybe it’s a list of suppliers and their contact information, or part numbers and their associated details. It’s vital information for you to do your job, and it didn’t exist anywhere else, so you (or maybe your predecessor!) started to compile data into a big old spreadsheet, and now you can’t live without it.
What’s the problem? First of all, access. There are probably other people in your organization maintaining a list similar to yours, but with different information, and other people wishing that such a list existed. If you win the lottery or are abducted by aliens, this precious spreadsheet and its key data will be lost forever. Second, data integrity. One wrong sort without the right columns, one deleted cell where you shifted cells up instead of left, and the entire sheet could become corrupted – often beyond repair if you didn’t recognize the mistake right away. Finally, you can’t use this data for analysis or other functions in your existing software.
What to do? Big old lists deserve a place in your database. Determine who in your organization uses the data the most and has the best access to changes, then figure out who in the company needs to be able to see and edit the data. Use that information to update a system that probably already exists rather than keeping a spreadsheet. You may need to request some changes to make the system fit-for-purpose, but it will be worth the effort long-term.
The Attached Excel Form
We all know this one. There’s some request in your company that requires approval, and the way to get it is to fill out a formatted Excel spreadsheet, attach it to an email (or even to an existing database form) and send it along its way in the workflow. It’s an improvement over an email or a shouted request across the hall, but of all the Excel misuses, this is probably the most prevalent and the most frustrating.
Why is it a problem? Version control. Over time, the form changes, but distributing the new form is impossible. People save a version to their desktops, fill it out and submit it to choruses of “that’s the wrong form, use this instead.” The back-and-forth slows business down and frustrates everyone involved. There is also a lack of control over the data inside the spreadsheet because it’s tucked inside an attachment, fields can’t really be required and copy/paste errors abound. Most importantly, the data inside the forms can’t be compiled, analyzed and reported on. Presumably, the information in this excel sheet is important and has value to your organization, but it’s locked in there. Someone would have to open hundreds or thousands of these forms and create a Big Old List or a Data Firedrill to be able to see trends or report on the information contained in the form.
What to do instead? Put it in a database, ideally the core system you’re using the request to facilitate. If you’re attaching an excel form to a requisition or a purchase order, work with your systems team to incorporate the relevant fields into that system natively. Your users and your process will thank you.
