Excel is Still Not an Enterprise Reporting Solution


At a recent conference, I had a frustrating conversation. The person I was speaking with was frustrated also. Her manager was an avid MS Excel user. Even with better tools readily available, he insisted on storing data and reports in Excel. Even worse, he forced his employees into the same low standard.

My "Excel victim" related how many data issues occurred could have been easily avoided. A co-worker did not understand how to use the formulas correctly, which resulted in the wrong totals being reported. The list continued and most of it was related to lack of skill or attention to detail. Even when my Excel victim would plead with her manager to use the better tools freely available within the organization for an automated and accurate process, he refused. Instead he clings to MS Excel as if it were some savior -- using it as an excuse to avoid learning or just feel some level of control. At this point, I was rolling my eyes because I thought everyone knew that Excel was not an enterprise reporting solution.

(Image: echoevg/Shutterstock)

(Image: echoevg/Shutterstock)

I fantasized about phoning the manager to enlighten him about Excel. Scold him for not trusting his employee's advice. Clarify how he could improve his organization -- this month! In my mind, it ends with him apologizing and shedding remorseful tears. Aren't all criminals sorry when they are caught?

I suspect the real conversation would end differently. Many smaller organizations or departments within larger organizations do not understand the issues surrounding Excel. It is flexible, powerful, and readily available. However, if you want to make a data issue worse - give users Excel for tracking. Suddenly everyone in the department is tasked with managing a tiny database. The data accuracy depends on the creator's skill and attention to detail. I get it -- some things can be managed by a spreadsheet. But even I agree that list is so short it could be tracked with Excel.

If everyone has a personal database, then how do you know which one is the truth? Who in the office has the most accurate count of widget sales or complaints? If you are keeping a running total of something, then how do you manage change? Say you sell 1,000 widgets and a third are returned. How do you investigate that? How do you prevent the AUTOSUM being in the wrong column? When does the data become too much to manage? And talk about duplication of effort.

When organizations do get serious about data and want to treat it as a managed asset, the fun begins. As a consultant, I witness "how the sausage is made" a lot. I am privy to internal discussions that range from compelling to exhausting. A common one is how do we measure <blank>. When individuals or departments have been autonomous for too long, different rules develop. Their counting rules show a lack of skill and aren't based on real business reasoning. "Just don't count x because we don't always see the email about it." If another department finds x crucial or did solve the issue, then you have an epic argument. It's not fun to watch the passions collide in a meeting.

When asked to settle a dispute, I am always on the same side. Have a repeatable process that produces an accurate answer and serves your end user. Departments must agree on counting rules and data item names. It is that simple. Spreadsheets then are used for their intended purpose of tracking short term situations or ad hoc analysis.

Tricia Aanderud, Senior SAS Consultant for Zencos

Tricia Aanderud, Director, Data Visualization Practice at Zencos Consulting, provides SAS Consulting services to organizations that need assistance understanding how to transform their data into meaningful reports and dashboards. She has co-authored three books with her most recent title "Introduction to SAS Visual Analytics". She regularly shares data visualization tips and SAS knowledge through her BI Notes blog (http://www.bi-notes.com). Tricia has a background in technical writing, process engineering, and customer service. She has been an enthusiastic SAS user since 2002 and has presented papers at the SAS Global Forum and other industry conferences.

Predicting Traffic in the Sun's Shadow

How do you predict the popularity of a one-time event? A look at different ways to forecast road traffic along the path of the eclipse.

Where Sharks Attack in Florida

As Shark Week approaches, we've got some data visualizations to help you determine where it's safe to get in the water and where you will need a bigger boat.


Excel's role has changed
  • 1/5/2017 11:11:28 AM
NO RATINGS

Most managers don't realize that plugins and other tools have morphed excel's role as being more than a spreadsheet, yet definately not a replacement for a database. Tools like Neo4j are also overlooked - the interfaces on those tools are more people-friendly.  I think the interface on excel is what draws people - it's a spreadsheet and familiar to the most unsavvy professional. Says something about innovation of professionals - it's low - but also attest to how good that interface was.

Re: Excel's role has changed
  • 1/6/2017 9:01:52 AM
NO RATINGS

I think there are two issues here.  The first is using Excel as a database, the second is using it as a reporting tool.  The larger of the two issues is using it as a database and it is one of my biggest pet peeves when I see data duplicated into a hand entered Excel file.  Less frightening is Excel as a reporting tool.  Using data connectors to get your data from an actual database eases that issue but as noted in the article some times the crazy formulas used in Excel can make a real mess.  I instruct people that if they find themselves doing weird math tricks that the more appropriate method is building a view on the database server to do that math.  That way there's no fiddling with things and trying to make data sets match up.  No offense intended to the data professionals who work outside of an IT team but you really should have a great relationship with your database guys, learn to speak their language and you'll be amazed at how helpful they'll be.  They live for this kind of thing even you're in an organization that is filled with red tape I'm sure they would rather give you the data you need in a format you can use than watch you try hammering square pegs into round holes. 

 

Change resistant
  • 1/5/2017 2:26:57 PM
NO RATINGS

It's not a perfect analogy, but an accountant I worked with years ago had a bookkeeping program she treasured that used MS-DOS. Which even in the summer of 2001 when she insisted I use it was a bit retro.

Our brains are resistant to change; I see my own tendencies here ("Another social media platform to learn/post to?" grumble grumble grumble). A function of age? Technology fatigue? probably some combo.

Re: Change resistant
  • 1/5/2017 4:20:04 PM
NO RATINGS

True, sometimes professionals are resistant to change.  We have to know our tools are reliable and fit our process to tackling tasks. Excel has been around long enough to build resistance to more advanced tools, even if those tools are really not as complaex as they used to be.  It's like assuming a car still has a carburetor and warms up slowly, when really fuel injection and better engine management is actually in place (as well as better experience).

Re: Change resistant
  • 1/9/2017 8:41:36 AM
NO RATINGS

IN this case the organization had some extremely advanced tools  and could have bypassed anyone doing work in the spreadsheet and simply had the end result output to XLS format.  It would have resolved the issues of calculating in the wrong columns, bad formulas, etc.

I guess it is resistance to change because why would you drink the magic elixer over continuing to suffer. 

Re: Change resistant
  • 1/9/2017 4:15:16 PM
NO RATINGS

@Tricia,

I'm frustrated just reading about this manager!  My first approach would have been to do the actual analysis is a better tool and drop the results out for presentation in a set of XLS charts.

Sounds like the manager was not only ignorant, but a micro-manager and would not have been happy to find out that Excel wasn't the actual source of the analysis.

Time to look for another position elsewhere. Given that the organization already has better tools, you might not have to go too far.

Re: Change resistant
  • 1/10/2017 10:12:47 AM
NO RATINGS

@Tricia. Misusing Excel in terms of collecting numerical data, customer names and the like is bad enough. Where I've developed headaches over the years is when someone uses Excel to collect just text-based forms data. So there might be 200 or more rows, each with columns for name, address, company, but then longer-form text entries of several hundred words. So, you end up reading what amounts to articles as someone describes a learning experience, an issue with their manager, or a problem with a product, etc.

When people are completing qualitative surveys some of those rows extend from "A" and "B" to "ZZ". They expect us to read that way!

Re: Change resistant
  • 1/11/2017 11:59:18 AM
NO RATINGS

> Misusing Excel

There's the key in all of this.  Misuse.

Excel certainly isn't the best tool for everything, or even most things, but it can be a darn useful tool at times.  Additionally, all those tales of woe of people misunderstanding or screwing something up can be fixed by training and even by technology solutions (for instance, there are many ways to lock formulas and prevent editing).

For the sake of argument, I would posit that *one* advantage Excel offers (other than the obvious: being ubiquitous and low cost) is that -- unless you're lazy or not paying attention (which, unfortunately, is most people a lot of the time) -- it tends to compel understanding if you want to do anything useful with it or glean anything insightful from it (albeit not necessarily in the most efficient or effective way).

Conversely, a lot of GUI-based, "plug-and-play" solutions tend to impede true understanding when it comes to the "behind the scenes" of data reporting and data analysis -- as I've written about for A2 previously, here: allanalytics.com/author.asp?section_id=1408&doc_id=241161

Re: Change resistant
  • 1/30/2017 11:31:46 AM
NO RATINGS

Good point Joe about misuse. It speaks to the idea about how tools can be misused by being mistrained on their usage. Excel is not for every situation, but like many spreadsheets, its value depends on the user intent and information contained in the file. It is possible to poorly train people on how to use the full capabilities of a software and extract value. 

Cultural resistance
  • 1/6/2017 10:46:36 AM
NO RATINGS

In this particular case, I wonder if this is a matter of cultural resistance to giving up control. The manager is using Excel and forcing his employees to use it. What would it take to persuade him that a different approach would be better? Would he need to have control (and mastery) over that other platform? Or does he need to experience the pain of what goes wrong when you use an inadquate tool before he would consider alternatives?
Has anyone out there succeeded in this kind of persuasion before?

Re: Cultural resistance
  • 1/6/2017 4:33:42 PM
NO RATINGS

I am guessing that this was all that was in his comfort zone. No amount of logic will move someone who just doesn't understand the technology. A move could only make him look bad ( or worse, ignorant).This is a real barrier in some organizations.

Re: Cultural resistance
  • 1/8/2017 9:41:59 AM
NO RATINGS

I think that is the main problem - how do you move managers in particular out of their comfort zone? It is amazing to me that the person doesn't hold himself accountable for having accurate data. 

I feel like some kind of zealot.  Lol!

Re: Cultural resistance
  • 1/14/2017 10:42:01 AM
NO RATINGS

Habit and attempts to save money are not easy to overcome even when shown it's fighting against us. The managers who keep the old ways even when in the case of Excel, will eventually see the real problems in getting accruracy around the department and among departments when everyone has there own data collecting and recording favorite method.

Re: Cultural resistance
  • 1/29/2017 12:30:28 PM
NO RATINGS

@Tricia In my experience, it's the boss's boss who calls him out. Not learning technology that the company has decided to use is a perfirmance issue.

Re: Cultural resistance
  • 1/9/2017 8:27:27 AM
NO RATINGS

You make a good point @Jessica Davis.   The person describing this situation had pleaded with him to make the right decision. She was asking my advise on how to pull him toward a better tool. Every arguement we brainstormed she had already tried.

I believe the guy is stubborn. The only way you combat that situation is with upper management issuing a directive.  I don't think the person I spoke with thought that would provide any career enhancement for her.

Sad.

Re: Cultural resistance
  • 1/26/2017 11:14:45 AM
NO RATINGS

@TriciaAA: At the end of the day, I believe that you'll inherently have more trouble getting companies to switch to a different tool -- which will require added investment and training -- than in getting companies to implement more/better training on the tools they're already using.

And then, if you're especially clever, assuming you do a good job on the training, you can drop hints in that training process and the planning and post-mortem processes as to how other tools can do even more and/or do things better...and then get interest that way.

Re: Cultural resistance
  • 1/26/2017 4:10:28 PM
NO RATINGS

@Joe. I wonder if it's the company that clings to Excel as a reporting tool or the individuals who have used for everything over the years and can't let go.

Re: Cultural resistance
  • 1/28/2017 12:31:13 AM
NO RATINGS

Hey @Joe, how about we just not load Excel onto people's computers from day 1. If they don't have it, they can't use it, no?

Re: Cultural resistance
  • 1/9/2017 9:03:31 AM
NO RATINGS

I run into this not only as comfort but as "it's not broke, don't fix it" which is a tad bit short sighted because it's broken, they just don't realize it until way down the line.  In this case what the manager probably needs is a handful of examples that show how often the data is wrong and the problems that it causes.  These examples need to be brought along with the system/processes to prevent these errors.  Without those pieces the manager will likely continue down the path of "it's working, stop messing with it."

Re: Cultural resistance
  • 1/14/2017 2:58:02 PM
NO RATINGS

@SaneIT: Without championing Excel, there are some problems with this argument.

For instance, Tricia writes:

"A co-worker did not understand how to use the formulas correctly, which resulted in the wrong totals being reported. The list continued and most of it was related to lack of skill or attention to detail."

So...the problem was other people's stupidity, poor training, and inattention.  Let's not blame the hammer because the carpenter bashed it against his head instead of the 2x4.

And to your point: "it's working, stop messing with it" -- well, if I was an Excel-using manager watching my employees constantly screw it up, I'd take that attitude too!

> " In this case what the manager probably needs is a handful of examples that show how often the data is wrong and the problems that it causes."

That certainly works for some people, but let's remember, of course, that anecdotal evidence is not the plural of science.

Excel is a tool with its uses.  There are far more advanced tools that can do really nifty things and are more user-friendly, to be sure, but let's not dump all over Excel simply because certain people don't know how to read a formula.





Re: Cultural resistance
  • 1/22/2017 9:21:03 AM
NO RATINGS

But Joe, if users commonly have problems with a tool, isn't it an indication that the tool isn't easy to use, and if so, not such a good tool? If a tool is meant to be used widespread --- and Excel is on nearly every computer at work --- shouldn't it be designed for ease of use?

Re: Cultural resistance
  • 1/25/2017 10:45:46 AM
NO RATINGS

@Broadway: As the old saying goes, leave it to man to blame the problems of his feet on his shoes.

Pardon me for saying so, but it seems to me that, by that logic, whenever somebody does something stupid and isn't paying attention and accidentally hurts themselves in a workshop with a bandsaw or other power tool, we should start seriously discussing a ban on power tools in workshops.

Tools of any kind require knowledge and a certain degree of training (even if it's just a few minutes of "DO IT LIKE THAT" and "NEVER DO THIS").  If you don't have training on, say, R (which is super powerful and nifty and open source and all that fun stuff, but also is--I'm sorry--NOT intuitive to use) or SAS or whatever, you're gonna screw that up too.

FWIW, I remember being taught how to use Excel in high school.  The teacher, walking behind us once, stopped behind me, looked at my screen and what I was doing, and marveled at how far I had gotten in an assignment -- and by doing things that he had not taught us yet.

He asked me how I knew how to do what I had just done.

I said, somewhat sheepishly, "I didn't.  I just clicked on Help."

At that point, he stopped the entire class, got everyone's attention, and spent the next minute or two emphasizing the importance of what I had just done -- clicking on Help.

Sorry, but you won't convince me that this stuff is hard.

And Microsoft Office is ubiquitous.  So there's really no excuse for not learning the fundamentals if you're going to work in an office environment.

Moreover, MSFT Office technology hasn't changed too much since my high school days.  On a side note, therefore, I'd humbly suggest that we'd do better to have better computer and technology training in the public-school classroom than spend so much time focusing on the standard stuff that nobody ever needs to know (absent it being a key part of their profession) and everybody forgets in a year anyway.

Re: Cultural resistance
  • 1/29/2017 12:40:13 PM
NO RATINGS

Now that you mention it, Joe, I realize that learning how to draw ERDs might actually be more valuable than learning program logic. Tools like Airtable make it easier to build databases for practically anything, but what use would the tool be if the user can't design a database structure? On the other hand, while I love the intellectual rigor of programming, it's hardly ever useful to a person who doesn't do it for a living.

Re: Cultural resistance
  • 1/30/2017 1:01:31 AM
NO RATINGS

When  I worked for major tech company the scenior director who was in charge of user experience and had been with the company for 25 years did not even have the basic understanding of Excel.  

Now let's talk about smaller companies where that is just the norm.  Sadly, the most advance analytical solution is the lowest common demonitor of expertise. 

Re: Cultural resistance
  • 1/30/2017 12:04:19 PM
NO RATINGS

Not unusual for directors to not know how to use the software that employees are suppose to know and be SME on.  You touch upon a critical aspect - that small business is really impacted by leadership with limited practical knowledge on software essential for tactics and strategy.

INFORMATION RESOURCES
ANALYTICS IN ACTION
CARTERTOONS
VIEW ALL +
QUICK POLL
VIEW ALL +