Top 6 Excel headaches and heartbreaks (from an ex-merchandiser)

I’ve never personally attempted to send a text message through a Microsoft Office product. But, as a former retail merchandiser, I have experienced many, many moments of personal anguish — one or two ‘dilemmas’ of my own — because of the limitations of spreadsheets.

Yes, just like Kelly Rowland couldn’t imagine her life without Nelly, there was a time where I couldn’t imagine my life without spreadsheets. Many merchandisers and planners today still feel this deep attachment. Spreadsheets are such an important part of our daily lives — but why do they hurt us so often?

In this article, I’m diving into the archives to run you through five of the most common spreadsheet headaches and heartbreaks that I endured as a merchandiser.

1. Unresponsive Excel? You’re not alone, Kelly

Workbooks weighed down with multiple tabs of data and complex recalculating formulas, with each sheet drawing data from another. This often makes workbooks unstable and unreliable.

Made a tiny formula error? Have you accidentally pasted data in the wrong format? Had the nerve to let several people work at the same time on a spreadsheet? Congratulations! You’ve won a one-way trip to Excel Hell.

Excel wasn’t made to handle the amount of data merchandisers need for their job, so we’d often push it past its limits. This can lead to the nightmare of every Excel user: an unclickable error message that says “Microsoft Excel is not responding”.

I often found myself in Kelly Rowland’s position, tearfully looking out the window as I tried to cope with unresponsiveness in the face of a spreadsheet’s limitations.

I remember one particularly challenging night. It was 10pm, the night before I was expected to submit a new weekly sales, stock, and intake (WSSI) budget and plan. With my attention focused on the task, I neglected to regularly save my workbook. Then I pasted some data into the sheet, and saw the dreaded error message.

There was no turning back. All I could do was terminate Excel, knowing in that moment in the late evening, I had lost every bit of my hard work.
Merchandisers, who spend hours carefully managing data, hate having to deal with software that breaks down without warning. They need stable software that won’t throw away hours of work for no reason.

2. Caught in a bad row-mance

Who needs more than 1,048,576 rows of data in a single sheet? A merchandiser, that’s who. Sadly, for Microsoft Excel 1,048,577 rows is one row too many. 

Excel’s row limit is bad news for merchandisers who have got portfolios of products, multiple channels, and more online data than most companies know what to do with. 

That means merchandisers find themselves crying over a patchwork quilt of sheets pulling data from other sheets. Some of the sheets are made by former employees, so when things go wrong, there’s no one around who knows how to fix the problem.

One of the best things about Excel is its versatility; it can be applied to many use cases, but that doesn’t mean it’s the optimal solution for every task.

I recently read an article about a Japanese artist called Tatsuo Horiuchi who created works of art using Excel — that’s amazing. But you expect art to take a long time and as much as I’d like to think there’s an art to merchandising, it’s an enterprise that needs speed and stability. 

Merchandisers need software that is made to handle the huge amounts of data they manage, not workarounds for software that doesn’t.

3. All dump, no pump

One of the things that used to bug me most about Excel was the fact that it couldn’t stream data automatically.

Excel’s all dump, no pump approach causes issues when you’ve got data across multiple sheets in a workbook too, as the linked data often isn’t live and needs to be refreshed — which itself can often lead to an “Excel is not responding” meltdown.

My life as a merchandiser was a disco of data dumps. Maintaining up-to-date data in spreadsheets took me away from the work where I could really add value.

By the time I’d updated my data across the myriad spreadsheets covering the products I managed, the data I’d put in would often already be out of date. I spent more time in cells than most criminals.

This was bad enough during periods of more predictable demand, but these days merchandisers are expected to manage demand against a backdrop of uncertainty, whether that’s a global pandemic or economic downturn.

It’s just not realistic to expect people to face these tactical challenges effectively when so much of their day is spent in spreadsheets, which are really only a small step forward from using a pen and paper.

In an age of cloud data and web apps, Excel fails on the fundamental need of merchandisers: to get a live view of their data.

4. Spreadsheets never forgive

We’re all going to make errors in our work. That’s because we’re human, but Excel isn’t, and it’s got no time for your mortal mistakes.

The problem is that Excel leaves the door to human error wide open. Every single cell is an opportunity for you to input the wrong data, format the data incorrectly — or worse, for Excel to go into one of its moods and shut down.

For merchandisers, spreadsheets aren’t designed to just keep records. They use them to make decisions on a daily basis. The scope for error in Excel is worrying for us, because all it takes is for one thing to go wrong and suddenly we’re making decisions using dodgy data.

For new merchandisers who are less familiar with Excel, the risk of error is even greater. The role is fast-paced and has a steep learning curve, so it leaves them vulnerable to little errors that can mess up their work and (possibly) their career.

As an example, a member of my team at another company once made a small mistake in Excel. The result? She sent 10,000 jumpers to the Middle East, where we weren’t able to sell them, and it was too expensive to bring them back to the UK. So the value of the stock was lost. It was a small error, but with big consequences.

People will always make mistakes. But merchandisers should expect a software solution for their role that minimizes the risk and potential damage of mistakes.

5. A very particular set of skills…

For all its failings, Excel is pretty powerful if you have a very particular set of skills. But most of us aren’t blessed with mad Excel skills.

Advanced formulas, pivot tables and conditional formatting are great ways to transform and visualize data. But not many people know how to use them.

Often, one person on a merchandising team knows exactly how to navigate the maze of Excel sheets, resulting in a single point of failure as a business. If that one person leaves the company, the rest of the team is left scratching their heads, trying to figure out what went wrong.

Sadly, this problem is only getting worse. Retailers are getting more complex, they’ve got more channels and more products to manage — that means more data and (yes) more spreadsheets. Merchandisers are expected to adapt this, but who has time to learn macros? Not merchandisers.

Merchandisers need a very particular set of skills, but extensive Excel knowledge shouldn’t have to be one of them.

6. Hide and sheet

Another Excel limitation that can hold merchandisers back is the way files are  stored.

While Microsoft’s Office 365 version of Excel has helped overcome some of these challenges by using cloud storage, many businesses are still using classic Excel, which stores files on a shared or local drive.

This often means spreadsheets are stored all over the place, and are often siloed in one drive away from the rest of the business. For instance a merchandising team might manage products in a spreadsheet saved to their shared drive, which other teams (e.g., buying) can’t access.

This siloing can can result in a duplication of effort, with two or more teams trying to maintain different views on the same products.

It also means that there’s no one version of the truth on that data. This can mean, because they are making decisions using different, teams can sometimes find their actions don’t line up, and sometimes work against one another. This is hardly a formula for success.

Merchandisers need a solution that centralizes all their data and gives teams the opportunity to collaborate using a single version of the truth.

There is another way (honestly)

If you’re a merchandiser or planner, I imagine that at least a few of the above headache-inducing moments sound all too familiar. For a number of years I found my brain was, on a daily basis, completely flooded by spreadsheets and spreadsheet-related information. Rows and rows, columns and columns. Formulas. Tabs. Error messages. Data.

So. Much. Data.

And I wasn’t alone. I’d go as far to say that spreadsheets have become a part of merchandising and planning in the same way that Adobe has become a part of design; they’re a fundamental part of how the job gets done, and a very useful (yet at times overwhelming) tool.

For years, I simply couldn’t imagine doing the job without Excel, and nor would I want to. Yes, spreadsheets have their limitations, so what is the alternative?

What’s the solution?

In recent years, I’ve been on a journey in my career, moving away from moments of spreadsheet-related merchandising madness and transitioning into a role as Product Intelligence Lead at Peak.

Here, I work really closely with our customers — merchandisers and planners at some of the world’s leading retailers — to help them rethink their approach and introduce them to the benefits of Products, one of our AI-powered applications.

Products acts as a single source of truth for merchandisers, planners and buyers, giving them a holistic view of product performance down to an individual SKU-level. From here, teams can use it as a base to action AI-driven, instant decisions around rebuy, markdown and replenishment to name a few.

The purpose of the Products application is to alleviate the strain on time-stretched teams who want to spend fewer hours with their heads number-crunching in spreadsheets and more time on strategy and creativity. It combines siloed data, surfaces key information and performance metrics about your current trade, and helps you review masses of lines effortlessly — in one handy, all-encompassing dashboard.

I don’t expect you to take my word for it, but I do encourage you to see the magic for yourself. Products is a game-changing proposition for merchandisers and planners and I wish it had been around during my time in the industry!