Project managers set up spreadsheets to record and organize data in a manner that makes sense to them. No one has ever developed universal standards or best practices when using this tool for this type of work, and since time is such a huge constraint, most likely no one would have any free time to learn them either. So for decades, creating a spreadsheet system has been very much an on-the-go, improvisational thing. Everything from naming conventions to how data sets are organized is decided by the user. This also means co-workers, team members, and upper management would need a substantial amount of time to make sense of such documents if project managers shared them.
If you’re only using one spreadsheet that’s shared with one or two other people, that isn’t much of an issue. But if you need to analyze the progress of, say, 82 projects, which are being supervised by five managers, and each aspect of the project, such as costs, task lists, milestones, meeting notes, and invoices is on a separate spreadsheet, there’s no way to get that information yourself. You’ll have to go bug the project manager to give you the information you need: “Hey Bob, I need the expenses for project 23, 51, and 54 for May 01–15, 2020”.
Now imagine if Bob came down with the flu for a week, or maybe he decided to quit. Where does that leave you and the project? It’s not a great feeling, but this is a common occurrence.
Imagine you have a bunch of marbles marked with spots, stripes, or zig-zags. These patterns are done in either red, blue, or yellow. They’re kept in a marble bag. Now imagine you also have a bunch of easter eggs with spots, stripes, or zigzags, and like the marbles, these are also in red, blue, or yellow. You store these in the fridge so they won’t spoil.
Now, let’s break down the analogy: your bag and your fridge are two spreadsheet files, and the software has basic filters managers can use to find the specific information they need. They can pull up all the yellow eggs. They can pull up all the spotted marbles. Managers can even pull up all the red-spotted marbles. But what if they need to see all your red, spotted objects? You’ve hit a roadblock because the software can’t apply filters to multiple documents.
So what do most project managers do in this all-too-common situation? They would create a new master spreadsheet and manually copy and paste the data from both spreadsheets. Annoying, but straightforward enough, right?
What if you have to collate data from not two, but 12 files? Let’s compound the problem further: What if your data is housed on different pieces of software, such as accounting, email, and GIS, which aren’t necessarily designed to work with each other? You would wind up having to manually aggregate data into one place before you can get the answers you need. Again, this is an all-too-familiar problem confronting project managers in a variety of fields.
As you might guess by now, the limitations of spreadsheets get in the way of the work project managers were originally hired to complete. Instead of reporting, decision making, auditing, planning for the project at hand, managing spreadsheets becomes their primary preoccupation. Because it’s such a pain to aggregate data regularly, people don’t do it as often as they should. That means they’re often not working with current information and more mistakes may be made. And let’s face it, people do weird things; some vital piece of information could be stored in Notepad instead of the designated spreadsheet because the manager didn’t have the time or couldn’t be bothered to fire up the program and scroll down to an empty cell to input the data. Two months later, that person has quit right in the middle of the project, and the new hire has no idea where that information is, so she has to rebuild the document from scratch before the deadline.
Now, many companies have recognized the limitations posed by spreadsheets. We can’t just use software like the kitchen junk drawer. We need to make sure it remains accessible and understandable to anyone. We have to plan a way of collating the information we collect that takes into account all the different ways we might sort through our data. And we need to come up with a solution or a protocol to make different applications work with each other.
These companies have tried various solutions to work around them. Some may decide to create a system by adopting a few familiar software platforms and consolidating them according to the departments within the company. The sales and marketing people decide to use the same CRM. The operations and construction departments choose to go with a particular billing platform. However, just like putting your marbles in the fridge with your eggs, or keeping your eggs and marbles in the same bag, these systems eventually have to make uncomfortable compromises.
Other companies may choose to have an in-house system built from scratch by hiring software developers who specialize in this type of end-solution, while still more may purchase powerful enterprise solutions that can solve the scalability issues previously described. However, the expense of maintaining such software long-term is often underestimated. As hardware requirements, operating systems, file formats, and data security continue to evolve, these companies are either forced to pay for expensive upgrades or adapt by adopting a less expensive third-party application to cover the difference and jump between the two platforms for work. Data silos reemerge.
Organizations small and large face one additional problem: while they know their work and they know their data, they are not data architects. Without purchasing additional training and on-call support, many companies would have to learn how to use the software on their own.
At some point, companies realize they need a real solution. Something powerful and yet easy to learn to use. A software platform that has the power to itemize every single piece of data. A place where new information can be easily added without becoming disorganized and files updated regularly without having old versions clutter everything up. What does this system look like?
If your organization has realized that using spreadsheets for project management has meant a loss of money, time, and good people, it’s never too late to start looking for solutions that will turn things around. What you need is a guide, in the form of an experienced data management consultant or provider, who will analyze your workflows, and help your organization understand why it’s not achieving the level of productivity expected. Then they’ll offer a solution customized to your company’s needs, taking the time to make sure you understand how it will benefit your workflow, as well as answer any questions you may have.
A good data management service provider will also allow you to thoroughly test their products before you decide to fully implement them. Lastly, their professional support staff will be there to make sure you feel confident in adopting and maintaining the new platform.