If you work in a marketing agency, you see some horrific Excel abuses. Here are my top ten.
Do you do any of these? For the love of God, stop it. Just stop it, right now.
Typing numbers straight into a spreadsheet, with no hint of where they came from
Number 1 deadly sin. Anyone doing this deserves to lose a finger. Maybe their left hand.
Wonderful things happen in Excel when you type "=". You can add stuff together! You can multiply! The next person who comes along after you, can understand what you did! It's marvellous and you should definitely try it.
An ex-colleague used to use Excel like a piece of graph paper and work out all his sums separately with a calculator, then type the answers onto an Excel worksheet. This is second only to using Tippex on your computer monitor. If you type numbers straight into cells, instead of leaving a trail by working them out with a formula, you're just as bad.
Hiding cells
To be fair, this is sort of Microsoft's fault. The hide cells functions shouldn't exist, or if they must exist, it should be incredibly in-your-face obvious that something has been hidden.
Barclays offered to buy 179 contracts that they didn't actually want, from the bankrupt Lehman Brothers, due to hidden rows. You have been warned.
If you absolutely have to hide things, use Group. It's not so well known, but it's much more obvious what you've done.
Shrinking column widths, until the column disappears
A favourite of people who don't know how to hide cells. This is so monumentally stupid, you shouldn't be allowed to use Excel ever again.
Colouring in cells to represent data
Want to piss an analyst off? Do this. You've got a big list of something - maybe a list of customers - and you want to highlight some of them as being your best customers, so what do you do?
You could type "best customer", or even better "TRUE" in a new column next to the customer names. That would be good, because then you can filter them, or use that column in formulas, or pivot tables.
Or if you're evil, you could colour all the best customers in yellow, so that anybody who wants to work with only the best customers, has to do it by hand.
Guess which one most marketing people pick?
Using Excel's default charts
Grey and two shades of purple either screams "newbie", or "incapable". Which one would you prefer?
Using Excel's 'exotic' charts
Step away from the 3D pie charts. Here's why.
Using loads of named ranges
In moderation, named ranges are mostly ok. Excel has a bad habit of corrupting them without you realising, but they're not so terrible.
Opening a workbook that has hundreds of the things in it is horrible though. Unpicking how a number is calculated, when at every step you have to look up a name, then find out what that name refers to, can really ruin your day. Names are great while you're building a spreadsheet. Six months later, when you can't remember what you did, they're a proper pain in the neck.
Inconsistent logic
This is how big mistakes happen. Really big, expensive mistakes.
Sometimes you've got a big grid of numbers - 1000 or more rows of calculations and a few rows need to be "fixed". The tracking was out of line that week and needs to be adjusted downwards 10%, or certain rows don't have VAT added, while others do.
You could manually edit those numbers that need changing, or alter the formula in those cells, to add on VAT.
Now what you've got is a big column of numbers that look like they're all calculated the same way. Except that starting from row 800, the formula changes.
You will forget that you did this. It is inevitable.
At some point, somebody - probably you - will want to add some more rows to the data and when you do, you'll copy the formula downwards, assuming that everything below it is the same. At this point your carefully edited "VAT" rows will disappear, your final answer will change and you'll have no idea why, or what happened, or how to get back to where you were.
You're screwed. And you deserve to be.
Macros for everything
Excel Macros are tremendously useful. They're the the tool that brought IT capabilities to massed ranks of analysts and even if it's getting on a bit, I still think Visual Basic for Applications (VBA) is brilliant.
But. And it's a big But. Most people who get good with VBA go through a few stages. First, you can't make it do very much. Then you get better and you can build macros to do almost anything, so that's what you start to do.
Stage three is where you realise that Excel actually had functions and shortcuts all along, to achieve the same as many of your macros, only faster and better. Don't let yourself get stuck on stage 2! You'll waste tons of time programming and the workbooks you build will only ever function properly on your own PC, where your macro library lives.
Whatever the problem, Excel is the solution
Excel's great, everybody's got a copy and it's so flexible, you can do almost anything with it. But that doesn't mean you should...
Excel isn't a word processor, an illustration package, a dashboard designer, a database, a calendar and it also isn't many other things, even though you can usually make some passable looking output with it.
When Excel starts to get frustrating, there's probably a better piece of kit out there for the job and that better piece of kit is very often free. Stop abusing Excel and go and look for it!
Do you do any of these? For the love of God, stop it. Just stop it, right now.
Typing numbers straight into a spreadsheet, with no hint of where they came from
Number 1 deadly sin. Anyone doing this deserves to lose a finger. Maybe their left hand.
Wonderful things happen in Excel when you type "=". You can add stuff together! You can multiply! The next person who comes along after you, can understand what you did! It's marvellous and you should definitely try it.
An ex-colleague used to use Excel like a piece of graph paper and work out all his sums separately with a calculator, then type the answers onto an Excel worksheet. This is second only to using Tippex on your computer monitor. If you type numbers straight into cells, instead of leaving a trail by working them out with a formula, you're just as bad.
Hiding cells
To be fair, this is sort of Microsoft's fault. The hide cells functions shouldn't exist, or if they must exist, it should be incredibly in-your-face obvious that something has been hidden.
Barclays offered to buy 179 contracts that they didn't actually want, from the bankrupt Lehman Brothers, due to hidden rows. You have been warned.
If you absolutely have to hide things, use Group. It's not so well known, but it's much more obvious what you've done.
Shrinking column widths, until the column disappears
A favourite of people who don't know how to hide cells. This is so monumentally stupid, you shouldn't be allowed to use Excel ever again.
Colouring in cells to represent data
Want to piss an analyst off? Do this. You've got a big list of something - maybe a list of customers - and you want to highlight some of them as being your best customers, so what do you do?
You could type "best customer", or even better "TRUE" in a new column next to the customer names. That would be good, because then you can filter them, or use that column in formulas, or pivot tables.
Or if you're evil, you could colour all the best customers in yellow, so that anybody who wants to work with only the best customers, has to do it by hand.
Guess which one most marketing people pick?
Using Excel's default charts
Grey and two shades of purple either screams "newbie", or "incapable". Which one would you prefer?
Using Excel's 'exotic' charts
Step away from the 3D pie charts. Here's why.
Using loads of named ranges
In moderation, named ranges are mostly ok. Excel has a bad habit of corrupting them without you realising, but they're not so terrible.
Opening a workbook that has hundreds of the things in it is horrible though. Unpicking how a number is calculated, when at every step you have to look up a name, then find out what that name refers to, can really ruin your day. Names are great while you're building a spreadsheet. Six months later, when you can't remember what you did, they're a proper pain in the neck.
Inconsistent logic
This is how big mistakes happen. Really big, expensive mistakes.
Sometimes you've got a big grid of numbers - 1000 or more rows of calculations and a few rows need to be "fixed". The tracking was out of line that week and needs to be adjusted downwards 10%, or certain rows don't have VAT added, while others do.
You could manually edit those numbers that need changing, or alter the formula in those cells, to add on VAT.
Now what you've got is a big column of numbers that look like they're all calculated the same way. Except that starting from row 800, the formula changes.
You will forget that you did this. It is inevitable.
At some point, somebody - probably you - will want to add some more rows to the data and when you do, you'll copy the formula downwards, assuming that everything below it is the same. At this point your carefully edited "VAT" rows will disappear, your final answer will change and you'll have no idea why, or what happened, or how to get back to where you were.
You're screwed. And you deserve to be.
Macros for everything
Excel Macros are tremendously useful. They're the the tool that brought IT capabilities to massed ranks of analysts and even if it's getting on a bit, I still think Visual Basic for Applications (VBA) is brilliant.
But. And it's a big But. Most people who get good with VBA go through a few stages. First, you can't make it do very much. Then you get better and you can build macros to do almost anything, so that's what you start to do.
Stage three is where you realise that Excel actually had functions and shortcuts all along, to achieve the same as many of your macros, only faster and better. Don't let yourself get stuck on stage 2! You'll waste tons of time programming and the workbooks you build will only ever function properly on your own PC, where your macro library lives.
Whatever the problem, Excel is the solution
Excel's great, everybody's got a copy and it's so flexible, you can do almost anything with it. But that doesn't mean you should...
Excel isn't a word processor, an illustration package, a dashboard designer, a database, a calendar and it also isn't many other things, even though you can usually make some passable looking output with it.
When Excel starts to get frustrating, there's probably a better piece of kit out there for the job and that better piece of kit is very often free. Stop abusing Excel and go and look for it!
1 comment:
ahahah the ex-colleague of yours must be the owner of initials A.E. - can see his name written all over #1.
Am thinking to forward this on to our grads.
ta,
LB
Post a Comment