~ 4 MIN READ
Excel Nightmares, Part III: Complexity, & Formatting & Template Issues—Oh My!
Are intricate systems and disorganization haunting your Excel reports? They did for this company—take a look.
If you’re involved to any degree in your organization’s reporting process, you’re surely aware of the ongoing battle between spreadsheet software like Excel or Google Sheets and reporting software. There have highly-publicized “nightmares” (like those mentioned in Excel Nightmares, Part I) associated with both of these competing reporting processes, but frankly, Excel has gotten a bad rap as a reporting solution for a reason.
Many organizations use spreadsheet software for reporting because they think it’s a less expensive, less complicated alternative to purchasing or building a software solution. But without fail, year after year, I’ve seen people make classic spreadsheet blunders that end up costing them time, money, and above all, their sanity—making the “less expensive, less complicated” thing completely untrue.
To illustrate this point, I’d like to share a string of communication (based on a true story) with you that show how these issues are playing out in organizations everywhere.
The Back Story
The following script is loosely based on an email conversation in a client organization a few weeks ago. The employees involved were in a place we (at ClearPoint) like to call “Excel hell,” and had reached out to me for some assistance.
In this story, which is loosely based on the email correspondence I read, Amy, Roger, Louise, Josh, Bob, and Linda are simply trying to create a monthly report for their managers using an Excel template. track their organization’s measures using an Excel template. Beneath each bit of communication, I’ve noted the primary issue with what they’re saying (which, you’ll see, includes a mix of issues like complexity, templating, version control, formatting, and customer service), and a brief synopsis from my own point of view.
I bet you’ll be able to relate to what a few of these individuals are saying (or dealing with)—take a look.
Amy: Roger and I have finished making the spreadsheet that tracks all of our key measures. I just wanted to institute some rules. First, never sort anything. You can use the “Filter” function but not “Sort”—it’ll mess everything up.
Issue: Complexity. Amy is asking everyone copied to this thread to simply remember not to sort any columns in the Excel file because it will break the file.
Roger: Thanks Amy! And let me mention, if you need to add anything, do not add it to the middle of the spreadsheet—add it to the end. NEVER use “Insert Row!”
Issue: Templating. Roger notes that adding something to the middle of the Excel file will mess up the template.
Amy: Oh yeah, I forgot to mention: Do not change any of the colors; they are automatically controlled.
Issue: Complexity. While Amy is trying to help the situation by automatically color coding results, she is adding yet another layer of complexity to the equation.
Louise: Wow, thanks everyone! But if someone could cross-check the table in the Word document with the roll-up in the spreadsheet, that would be wonderful. I’ve gone cross-eyed from trying to compare the two.
Issues: Version control and formatting. Louise had the best of intentions in trying to compare their former Word document and newly-created Excel spreadsheet, but now she’s created a version control issue. No one knows what she has changed (or if she’s changed anything at all) and what she hasn’t without parsing through both documents in their entirety.
Josh: By the way, I have put in a request for some training around using this spreadsheet, but so far, no one from the mysteriously-named “Jedi Training Team” has responded.
Issue: Customer service. This shows a lack of cohesion between these two departments. When you create your own reporting solution (even within Excel) instead of purchasing one, you have to handle any type of training (and any crisis) yourself. This is something to think about when making this decision.
Bob: Linda, thanks for cross-checking the spreadsheet. I recommend printing one-sided, as the deck and notes are confusing if printed on both sides.
Issue: Templating. Linda (who hasn’t been a part of the conversation until this point) must be confused about how to print the document. Bob directs her to print out the list of measures so it’s only on one side to prevent confusion with the Excel template (though I have a nagging suspicion that Linda might be confused regardless).
Josh: I just wanted to chime in and say that the heroes of the day are Amy and Roger, who formatted the tracker to automatically color code the results!
Issue: Formatting. It appears that Amy and Roger have saved the day with their workaround for color coding—hurrah! Everyone seems pretty excited. (Except for Amy and Roger, of course, who had to put all of the work into this color-coding system and waste a good chunk of their day writing a workaround for this Excel formatting faux pas.)
Louise [Urgent Message!]: Everyone, Linda has done a comparison and noticed that the numbers do not match across documents, so we will need to update the legend and double check the numbers in the notes as well.
Issue: Version control. It looks like even with two (or more) sets of eyes looking for errors, there were still a number of discrepancies between the two documents, which Amy asked Louise to fix. Louise is asking for someone to update the legend and double (triple?) check the numbers in the notes to ensure her changes haven’t messed anything up. This adds yet another layer of complexity.
Bob: We’re on it! The deck will be updated soon, and the color codes are fixed. Hopefully we will have it fixed in time for the meeting.
Issue: Complexity. Uh oh... it looks like things didn’t work out so well with the color coder! And this issue will leave the entire team scrambling to get their ducks in a row before their review meeting.
Josh: Oh, by the way, I know it’s last minute, but I have a few more changes in the attached memo; if someone could insert them into the spreadsheet, that would be great. I used the change tracker.
Issues: Version control and formatting. Auughhh! We just finished fixing the color coding from the overly complicated scheme that was implemented, Bob’s just fixed the errors the Linda has found, and now even more changes need to be done (after we first figure out how Josh “tracked changes” in his memo. Hopefully no one else is working on the same thing at the same time—or there could be more trouble!
Has anything like this ever played out at your organization? If you’ve dealt with any conversations that sound vaguely familiar to the script above, you should know that there is an easier, more effective, highly efficient way to handle reporting: with the help of software.
There are plenty of great reporting software options on the market which can prove to be invaluable tools for you and your team. Automation from software helps alleviate some of the night terrors that Excel reporting can cause (and will likely give you a pretty impressive return on investment, as well).