Are Excel Charts Hurting Your Business? 10 Mistakes You Should Avoid.

Are Excel charts, and Excel in general, a commodity, with no competitive advantage? Only if you want it to. Why? Because a vast majority of users:

  • Have the data analysis skills of a toddler (or less);
  • Can’t go beyond chart defaults;
  • Functions? They know how to click the SUM() button;
  • Don’t know what a dynamic chart is;
  • Think pivot tables are too complex;
  • VBA? No way!

I believe you can squeeze some competitive advantage out from Excel if you avoid some basic mistakes everyone else makes. Here is a very short list (some generic, some chart-specific).

#1. Assuming that Excel Can Do It.

Because a spreadsheet is such a loose environment, people often assume that all things numeric can be done using Excel and that no specific skills are needed. Using Excel as a database tool is an obvious example. If you can’t stop singing Ode to Joy every time you see one million rows in Excel 2007 that’s a clear sign that you are on the wrong track.

#2. Assuming that Excel Can’t Do It.

Most people aren’t aware of how powerful Excel is and use it almost as a pocket calculator. If you routinely have to manage quantitative data, learning a little more always pays off. As an example, you can use it to create complex executive dashboards or, at least, as a dashboard prototyping tool.

#3. Not Having a Go-To Person.

Not everyone needs to be an expert, but having someone that understands the business and proactively tries  to find better ways to perform common (or not so common) tasks should be a requirement.

#4. The Excel Islands.

If you have a data source you can connect your sheet to, do it. Don’t make the mistake of copy / pasting the data into your sheet. That’s a bad practice and it can undermine your organization’s entire information system.

If you connect your spreadsheet to the data source (via ODBC, for example) you are ensuring the integrity of your data and minimizing maintenance costs. If you are creating a new table, use a simple database tool like Access. And if you really, really, need to use Excel, make sure you create a table that can easily be exported and used by a different tool.

#5. The Uncharted Archipelago of Excel Workbooks.

Search for “book*.xls” in your hard drive. How many do you find? Get rid of all those useless files now.

#6. Bypassing IT.

We all have our little fights with IT, but we must understand their role and the reasons behind their actions sometimes make sense… Excel users often lack knowledge to understand data structures, data access, documentation, security (IT 101, basically). If your IT don’t want to conquer the world and actually helps users, you should work hand in hand with them.

#7. Not Using VBA.

I’m sorry to break this to you, but if you spend your time analyzing data and Excel is your primary tool, then you must use VBA. I’m not telling you should be a programmer, far from it. But you should be able to record a macro and make some simple tweaks. There are many repetitive tasks that can easily done using three lines of code.

#8. Excel is the only charting tool.

There are no neutral tools. They force upon you a certain way of doing things that you may not be comfortable with (or you shouldn’t). The Chart Wizard is one of the stupidest wizards I’ve ever met. If you need to spend time removing defaults there is something wrong with the defaults. Excel charts emphasize:

  • Stupid Defaults versus Cleaner Formatting Options
  • Static versus Dynamic
  • Marketing versus Insights
  • Disposable versus Reusable
  • One versus Multiple
  • Large versus Small
  • Island versus Continent
  • Presentation versus Discovery

I’ll detail this in a future post but, like PowerPoint, Excel charts do have their own cognitive style. If you want to do things differently you can, but you are choosing a path of endless pain. So, give some users the option to use a different charting tool or, as a bare minimum, use a better wizard.

#9. Forgetting the 3R’s.

Reduce, reuse, recycle. Yes, I’m talking about Excel charts. How many disposable charts are cluttering your hard disk? Go green when creating your charts:

  • Charts are typically much larger than needed, so you can safely reduce their size;
  • When you create a chart make sure that it can be reused when the data updates;
  • A chart for Market A is probably very similar to a chart for Market B. By recycling the chart from market to market you don’t need do create new charts and pollute your hard drive.

#10. Not Making Things To Work Together

Lists like this one are useless, unless you know how the basic system works. Try to see the big picture and it will be much easier to understand where each piece belongs to:

  • If a function needs a range address, you will know that another function can provide the address (first step to create dynamic charts);
  • If you are repeating the same stupid task, you’ll find a way to automate it;
  • etc.

Bottom line: make sure you know what you should use Excel for, find inefficiencies and give power users the option to use specialized tools.

13 thoughts on “Are Excel Charts Hurting Your Business? 10 Mistakes You Should Avoid.”

  1. Great post – so very very true!

    BTW – interesting that you’re using Tableau charts to illustrate Excel charts – I guess that’s because making a decent visualisation in Tableau takes seconds, whereas doing the same in Excel takes a long time!

  2. @ Andy: I just wanted to show things that can’t be easily done in Excel. I used images from Tableau, R and SpotFire.

  3. THis is a damn fine post.

    One thing I would add is #11: Not Using good enough data input queries (e.g. SQL queries or whatever database language/protocols) when importing data to excel in the first place – and not having a good enough overview of the data that your organisation collects and how/where it’s stored.

    As per your comment re VBA, if you spend your time analyzing data and Excel is your primary tool, then understanding (and being able to tweak) the queries that you rely on for your data gives you a much greater degree of certainty that your information is what you think it is, and that it is the best information available. In my job, SQL is more important than VBA, as once I get the right information into Excel Pivottables, then the pivottables allow me to do 80% of my analysis, with no VBA neccessary.

    But that is not to undermine the value of learning VBA – I’m just heading down that path, and I’m already reaping the rewards.

  4. I’ll second what Jeffrey said:
    – a damn fine post
    – creating the right data set is really an underestimated component of the process.

    And I REALLY liked the point about a go-to person. That’s a role I have tried to play for a while now – but having a manager who promotes that role is what has made a difference.

    A perceptive and thought-provoking post.

  5. There is no doubt that a ‘Go to Excel Person’ is a must for all companies, regardless of size.
    It is now common knowledge that many, probably out of ignorance, denigrates Excel and chooses other programs to reduce errors. But errors and mistakes are not stapled to the application but rather to the person who created the spreadsheet.

    Excel is here to stay, if you want it or not, toddlers can be good, but a dedicated person is necessary.

  6. So, so true Allan. Excel is still a mystery to a lot of people. I know some accountants who think Excel has no place in their office.

  7. Andy,

    Fantastic post. I both preach much of what you say and I’m “guilty as charged” at the same time. I’ve yet to yield to a 3-D pie or 3-D bar chart, but must admit to the “bypassing IT” error.

  8. Great Post.  Unfortunately, I don’t know of any software that works as well as SPSS when it comes to running frequencies, crosstabs, manipulating the data, etc. and Excel when it comes to macros, code and nice charts.  Now if they could just combine the two that would be great.  I would like to use the data in motion charts in Google but not gonna happen with IT so maybe I can use these pretty dashboards in Excel.
     

Comments are closed.

Bundle Tableau + Excel wordless instructions only $37.00 Dismiss