9 Things you should never do in Excel

Excel rules the roost when it comes to real-world management information. It’s easy to knock it, but millions of people find it quick and painless to use. There are some things you just should not do in Excel. Here’s my highly subjective list.

Never….

 

  1. Use Excel as a database. OK, I know you are going to ignore this. We all know how it goes. First it’s a “tracker”, then we stick some graphs on it, then more field and before you know it your humble spreadsheet has turned into a business critical application. The nightmare comes when the data becomes corrupted, gets lost or multiple people tinker with the spreadsheet. Maybe it is time to scare yourself, imagine that your most critical workbooks are corrupted and start to think about implementing that SQL database that you have been putting off.
  2. Use Excel’s default charts. They are horrible (slightly less so with the 2007 version) because they break lots of good design rules, making them harder to understand “in a blink”. When you put a number of them in dashboard it becomes a blizzard of extra colour and lines that makes it slower and harder to read.
  3. Merge cells. People often do this to create text entry boxes. Merged cells destroy your ability drag-copy and copy-paste properly. They also muck up a whole host of other functions.
  4. Overwrite data. I’ve worked with a number of organisations that will input weekly or monthly data over the top of the previous period’s data. This destroys the chance to do longer-term analysis and extract any real insight from the data.
  5. Chain-link data between spreadsheets. Links between sheets can make things unreliable. Multiple links in a chain between multiple sheets can be a nightmare. Other people have sheets open, files get moved between directories and people find it difficult to follow the trail. The more links there are, the more likely things are to go wrong. If you have more than 1 level of links then you should seriously look at the design of you analysis model.
  6.  Using Pie charts. I’ve got a real problem with pie charts. For the full rant about why pie charts should not be used follow the link, but in short they are an inefficient use of space, don’t trend, can’t show targets and are poor at showing a big spread of data values.
  7. Turning off “automatic calculation”. This is less of an issue nowadays as PCs are much more powerful. Switching off automatic calculation is a certain way to trip up the unsuspecting user, so think very carefully before you do it.
  8. Cheerfully repeating the same tedious cut and paste activities, week after week, year after year. With some basic VBA knowledge it should be possible to automate most things. If you can’t automate it then you should question whether the workflow is properly set up in the first place.
  9. Use lots of colours and clipart to make a spreadsheet look “more professional”. Good design is all about restraint and effective use of visual cues. Any line, colour or graph that does not add information or insight is clutter and will make the document harder to understand.

Do you have any “pet hates” I’d love to hear them

 

6 Comments

  1. Avatar James Lawther on January 29, 2012 at 07:59

    On a similar theme, but more of a powerpoint rant

    Using branded corporate colours in charts. Once you have seen one shade of purple you have seen them all. Is that data the mid purple, or more of a mid light purple?

    James

  2. Avatar Larry Raymond on April 10, 2012 at 20:54

    Too many colored cells can really do a number on the eyes especially if the colors are not chosen correctly. The wrong combination can quickly give a viewer a headache.

  3. Avatar Mike Adamson on September 21, 2012 at 14:59

    When using colors in graphs or cells, I always try to print a sample on a black & white printer. Quite often, colors that are distinctly different on screen look exactly the same when printed. The same rule applies to fonts within colored cells (which, to Larry’s point should be used very sparingly). I’ve seen many spreadsheets with unreadable cells when printed.

  4. Avatar Warren Anderson on June 5, 2013 at 20:14

    I’m interested in your comment that “They [merged cells] also muck up a whole host of other functions”. Can you cite some examples please?

    • Avatar Bernie on June 6, 2013 at 06:40

      Hi Warren. Examples of two simple functions that get messed up are:

      – Summing a column (the sigma button) – Excel will force you to sum all the columns that the merged cell straddles.

      – Copying and pasting becomes very difficult if you pasting from an area with a merged cell to a sheet that does not contain a merged cell.

      Bernie

  5. Avatar Marty Preuss on June 21, 2013 at 14:44

    Good article. I agree with everything, with the possible exception of number 5. Single level file linkage can be a very useful tool for displaying comparative performance levels in an organization with multiple locations performing similar tasks. When the files are setup for a dedicated purpose, like a performance metric, you can “lock them down” using web collaboration software to avoid the “moved between directories” issue. However, linked files do become “clunky” and I agree with the avoidance of linking more than one level.

Leave a Comment