Arguably the most useful part of the Microsoft Office suite, Microsoft Excel can be extremely powerful.
But with that power comes responsibility. It is easy to let one little error really ruin your day, week, business…
With that in mind, here are our top ten Excel tips and tricks, focusing on some of the most common traps businesses using Excel fall into – and how to avoid them!
#1 Are you absolutely sure that is right…or “How I started spending $s to save £££s”
Whenever you copy down formulae in an Excel sheet, the formula adjusts for the row or column that you move the formula into.
This is great when you are copying a formula down against columns of data – you certainly don’t want to have to type them over again – but what if you want to reference the same cell over again?
Copying down a formula will start referencing a different part of the spreadsheet and the results will be gibberish. This is where absolutes ($ signs to you and I) are worth their weight in £££s!
#2 Pivot Tables – so powerful, but so deadly
Pivot tables are great. They can handle lots and lots of data and can give you really quick answers.
But they must be treated with care. Are you sure that all of your data is being picked up?
Are all your numbers really numbers, and if they aren’t, are they counting towards your totals? Read our upcoming guide to error-free pivot tables to use them with full confidence.
#3 Error trapping – Excel’s little insurance policies
Sure, you never make mistakes, but what if someone else does?
Ss can look like 5s, and Excel sure doesn’t like multiplying things by letters.
This is where error trapping comes in – wrap your formula in an error trapper like ISERROR() and you can tell Excel what to do if it finds a mistake.
#4 Friends don’t let friends use merged cells
We all want our Excel reports to look pretty, and “Merge and Centring” titles allow this, but using merged cells anywhere else is just asking for trouble.
Cell references start to fall over, formulae become unreliable and in the end you wonder “was this pretty spreadsheet worth all of this pain”.
There is a time and a place for merged cells, and that is “practically never”.
#5 External links
No spreadsheet is an island and your spreadsheets will often link into those of others. Woo, collaboration!
But, what if your data pal changes his spreadsheet, moves a few columns around. Or what if it is so big that it slows down everything it touches?
Using an ‘airlock’ – a data set copied from an external spreadsheet into your spreadsheet – is a great way to control how and when data makes its way into your workflow.
#6 Magic Numbers are toxic or “Why 0x5F3759DF is great, but that’ll do”
In Excel slang – how cool am I? – a Magic Number is a number hardcoded into a cell.
Every once in a while a Magic Number is really really useful – one for the nerds: 0x5F3759DF a.k.a. the fast inverse square root – but most of the time they are little ticking timebombs.
For example, =A2*0.2 will tell you what the VAT amount is on an invoice. But what happens if the VAT rate changes, or someone looks at that formula in the future and can’t understand where that number came from?
Instead, referring to another cell (using absolutes of course) or even a named range makes it crystal clear what that number represents, and how you can change it.
#7 You’ve heard of Big Data, how about Big Formulae?!
Well, aren’t you clever? Nesting a dozen IF statements together to form the King of IF statements.
Well, good luck reading that tomorrow and knowing what it means. Sure, Excel does have ‘Evaluate formula’ in its toolbox, but once your formula gets too complex it starts pulling its hair out too.
Instead, why not make your life easier with a few helper columns, maybe even a cheeky little XLOOKUP into a static data table? Head this way for more Zen formulae.
#8 Cell formatting dangers, or “That’s not a date, this is a date!”
Next on our list of Excel tips and tricks, one of the most common pitfalls is inconsistent formatting. For instance, if you apply different date formats to similar data, Excel might struggle to recognize the values as dates, even though they appear to be formatted correctly.
This can cause sorting issues, lead to incorrect date calculations, or even break your formulas. Similarly, number formatting can create major headaches:
- For example, if one cell is formatted as a general number and another as currency, Excel may not treat them the same way, even though they contain similar data.
- This could cause errors in your simple or complex calculations or, worse, lead to misinterpretation of financial or sensitive data.
Another formatting issue to watch out for is hidden values:
- When working with large data sets, you might format multiple cells to hide certain information or apply colors to rows to help them stand out.
- But if done incorrectly, these hidden elements could be mistaken as empty cells or misinterpreted by others when reviewing your spreadsheets.
It’s about ensuring your data is handled consistently and accurately in the desired format. Take a little extra time to standardise formatting across your workbook and ensure that every cell is properly formatted for the type of data it contains.
#9 The danger of unprotected sheets
Now that Sharepoint and Teams rule the roost, collaboration is key. But, can all of your partners be trusted?
Sure, version control means you can always roll back problems (if you catch them in time) but it is probably better for all concerned that those errors don’t happen in the first place.
So, if you are collaborating with someone, perhaps only give them access to the sheets that they need to see? The easiest way may be to hide some sheets, but, take it from my experience, Murphy’s Law is strong in Excel, and errors will find a way.
Protecting cells, sheets or even the whole Excel workbook (‘read only’ to the rescue!) may save you so much hassle down the line.
#10 Don’t drink and Excel, or, to paraphrase Hemingway “Excel drunk, edit sober”
I’m sure you aren’t in the habit of a 9am G&T to get you through the admin, but sometimes all of those spreadsheets are the last thing you want to look at after a busy workday (and maybe a bit of client entertaining?) so this isn’t a technical tip but more of a life one.
If you are burning the midnight oil getting a presentation (full of charts) or proposal (so many variables!) out the door, remember 5pm Friday is 9am Monday.
Take a beat and review your spreadsheet with clear eyes – or another pair of eyes if they are available to you. Rubber Ducking with a colleague might prove invaluable.
Final thoughts: Excel tips and tricks
Hope you enjoyed this guide and will find these Excel tips and tricks useful – if so, why not take a look through some of our other blogs? Everything you wanted to know about digital marketing but were afraid to ask is on our website, from crimes against web design to SEO for estate agents.
If you have made it this far you are probably either thinking “These are great, thanks Dan, I’ll add them to my Excel Toolbox”…
…Or you are thinking “Oh, F@$&, who knows what is going wrong in my spreadsheets, I just want to crawl under a rock”.
If you are thinking the latter, don’t worry, help is at hand – find out all about our workflow automation service don’t hesitate to contact us. And if you are thinking the former, well, don’t mention it – keep coming back here for more.