Excel Tip of the Week #440 - How to get better at Excel

Rather than being too maudlin, I wanted to leave off with some of my best tips for getting better at Excel – from the big, to the small. These are the habits and strategies that I have used over my own career to steadily improve and learn new things, to the point where I am now by all accounts a very experienced user. I think there’s something in here for everyone, whether you’re a novice or an expert, so hopefully you find it useful.

1 – Don’t accept manual work

I’ve often spoken in courses that I’ve run about a “fifteen-minute rule”: If you’re looking at a manual task that’s going to take fifteen minutes or more, whether it’s cleaning data or creating a summary or whatever else, try and spend the time learning a better, automatic way instead. That learning often takes longer than fifteen minutes, sometimes a lot longer, but it always pays dividends in the long run.

The key is to assume that a better solution will exist, and you just have to find out what it is. Excel is a well-weathered program with decades of history, so most problems have been tried in it by this stage and many of them have built-in solutions if you just know where to look for them.

2 – Try and stick to the keyboard when you can

There are plenty of keyboard shortcuts in Excel, and they are great for increasing efficiency. We’ve covered them plenty in the TOTW archive – see for example #328 on navigation shortcuts, or even our keyboard shortcuts advent calendar from a few years back in #266. Learning new ones takes practice, but if you take the time to get used to them you can minimise the time you spend hopping to the mouse and clicking through menus, which makes for everything being a bit quicker.

3 – Be curious about new formulas

There are almost a thousand formulas in Excel, but most of them are rarely if ever used. Most of the unused ones are obscure statistical measures or engineering applications, but there are plenty more that you can use for more everyday things. Like with tip 1, the key is to look for something when you run into a situation that requires it, and then looking at Excel’s Help files, the Microsoft website, and other online resources (perhaps even the TOTW archive!) for tips on how to use it.

4 – Understand why solutions work

If you end up using a template or a formula from Google to solve your problem, that’s 100% fine! But try and figure out how and why that formula works. Try working through the formula step by step with the Evaluate Formula tool on the Formulas tab, or breaking it into separate stages in different cells, to get a grip on why it works. This approach can really help you to pick something up from solutions, even if you aren’t able to make them yourself in every case.

5 – Don’t stick to one way of doing things

Having a broad base of knowledge can be really valuable when you’re looking to solve more problems. For example, if you are always thinking about complex single-cell formulas, you can miss a case where a simple helper column would work better. Or if you are a regular PivotTable user, you might overlook where a formula would be easier for the end user. I use both Power Query and Power Pivot occasionally, and VBA every so often; but having used each of these somewhat means I am more likely to remember what they offer when a case arises when they are useful. Broadening your horizons is definitely valuable.

6 – Help and teach others

The best way to really solidify your own learning is to use it to help bring others up to your level. Teach colleagues about the new pivot trick you just learned, or go online to a help forum (I’ve written before about the Excel subreddit) and see if you can lend someone a hand. Teaching requires you to ground your understanding in explainable terms, and fit it into someone else’s frame of reference so you can help them learn it. Helping others with their questions will expose you to new problems and help you to expand your own skillset by learning new things to help them.

7 – Stay in practice

Excel’s formula language – all of its many languages, really – work just like real-life second languages: learn them and practice them, and you will get better and more fluent, but let them lie and you will get rusty and forget things. Keep your hand in with some occasional Excel dabbling even if it’s not your main focus, and you’ll reap the benefits.

These lessons have helped me immensely over the years, and I hope they can do the same for you.

Happy formulating,

-David

• Tip of the Week Index