What Happens in Excel, Stays in Excel

Imagine you are several months into your new role as a data manager, responsible for worldwide marketing coordination. You indirectly oversee approximately 60 sales representatives scattered across the globe, grouped into the familiar regions of the Americas, EMEA, and APAC. You're all set for a two-day conference and have marked your calendar as "Out of Office" for the upcoming Thursday and Friday.

However, on a Tuesday morning, your manager surprises you with a request for a presentation on sales forecasts for the upcoming year. They want this data categorized by the company's five traditional product categories. The catch? You need to collect estimates from all 60 sales representatives and have the presentation ready for review by Monday at noon, immediately upon your return from the conference. Furthermore, your manager has requested a Level 0 analysis.

But here's the kicker – you are an Excel expert, so this task should be a cakewalk, right? All you need to do is create an Excel template, listing all the sales associates grouped by region, breaking down the data by the five categories, and asking them to fill in the Q1, Q2, Q3, and Q4 sales figures. After that, place the template in a shared drive and send out an email with the link and instructions. It seems straightforward – people will access the template, input their 20 data points, save the files, and close Excel. Piece of cake!

In your thought process, it's clear – everyone will follow the pristine instructions in your email, and upon your return, the template will be filled with the data you need. You'll use this table to create a pivot report and have your presentation ready in no time. You're even excited about the opportunity to analyze the data and provide valuable insights. Additionally, you decide to provide current year sales figures by representative as a baseline.

You run your queries and swiftly set up the format, which appears something like this:

The day before your flight to the conference, you write the email, craft detailed instructions, and use the distribution list to send it to all associates. What could possibly go wrong, right? You log out, and your workday ends.

The Morning After:

On Monday morning, you arrive an hour earlier than usual, feeling excited about the stunning visualizations you've imagined to impress your audience with your Excel skills. The first thing you do is open the link in the email you sent to see how things have gone.

However, an odd prompt pops up as Excel asks if you want to open the file as read-only. The file is currently open exclusively for a user, who appears to belong to the APAC region. You hesitantly choose "open as read-only" and brace for what's on the screen.

To your dismay, your pristine template is beyond recognition. It's partially filled, and rows are highlighted with a mishmash of colors. The Q3 column for actuals is missing, and new columns have been added, including a comments column filled with poems and lengthy comments.

Furthermore, someone in Asia decided they weren't content with the five product categories and added rows to specify product names that don't align with your categories. This inspired other users to insert rows for their own product breakdowns. One representative even created over 20 new products!

But that's not all; you don't even have a third of the data you expected. As you open your email, emails start flooding in directly from the associates, each containing files named "MyFigures.xlms," "ForcastEMEA_final.xlms," "JohnSmith_Figures," and many more. Over 40 of these emails land in your inbox.

You decide to check the shared drive where you placed the file, and to your astonishment, you find a slew of files, each a replica of the original but with various names like "format_emea_final," "emea_final_final," "final_with_comments," and so on.

Panic sets in; it's 8 AM, and your crucial meeting is just four hours away. There's no way you can make sense of this chaos in such a short time, as you're unsure what you have and what's missing. You may need to go through email by email or file by file, trying to piece together the puzzle.

Your reputation and the initial impression of your ability to handle a simple task are at stake. You requested something straightforward, and your pristine template has been turned into a creative mess by a horde of users.

You open your original file, the one that was locked, hoping to find answers. You'd like to know who deleted columns, who added new ones, and who initiated the chaos by adding products to the categories. You do know who locked the file, but there's no way to find all your answers because, as they say, "What happens in Excel stays in Excel!"

Postmortem:

Does this situation sound familiar? There is a way to perform this activity without any chance of deviation, a way to track who did what and when, and a method to limit the visibility of records to the user entering data. This can even provide immediate results in Power BI or a Pivot table. All of this can be accomplished without relying solely on Excel.