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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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