It’s now over 10 years since Tip number #1. Over this period, and even before the start of the series (originally “Tip of the Week” and latterly “Tips and Tricks”), the ICAEW Excel Community has sought to help community members improve their use of Excel, making it more productive more reliable and capable of delivering reports with greater impact. To celebrate the 500th Tip, we have asked one of the community’s advisory group members to reveal the Excel tip that has played the most important part in their own journey to Excel mastery.

Ben Ducker – Targeted Tips

I’m a competitive target shooter, I’ve been British Champion, and have been lucky enough to have represented Great Britain at several World Championships. Shooting is a very technical sport: there are lots of things to consider when competing, like the setup of the firearm, the effect of ammunition choice, and environmental factors which can affect your shot.

Improving at this sport is a learning process, and requires experimentation to see what works, and what doesn’t. Critical to reaching a high level is the ability to learn and to improve over time – a process which can be accelerated with the right techniques. I realised that the techniques I used to improve my target shooting could also be applied in my work, both are technical skills which require an iterative learning approach, and I want to share with you how those techniques can also be applied to learning a technical skill like Excel.

The Big Idea

If I were to distil this article to a single point it would be this:

When you’re presented with a lesson, make sure you learn from it.

Not everything is going to work, often you’ll learn even more from things which don’t work (!) and both are absolutely valuable when learning a new skill or seeking improvement. It’s inevitable that you will be presented with lessons, and it’s crucial to make the most of them when they come along.

My technique with shooting was to keep taking notes – whenever I was at a competition and something went either well, or poorly, I would take a note. Things like remembering my point of aim for a particular type of target, or certain items of gear which would been handy to have at a match – I’ll make a list and make sure I can refer back to it.

Over time, and from a wealth of learning opportunities, these notes grew. Whenever I came across a valuable nugget, I would make a note of it in a folder in my phone. I organised my notes into sections so that I could quickly find the relevant points when I needed them. I segregated my list into areas like ‘before a match’, ‘to do upon arrival’, ‘remember when taking on long range targets’ etc. Then whenever I was due to attend a match, I would refer back to those notes so that all my accumulated knowledge and learning points were at my fingertips, and I could put them into practice in the competition.

It became a living document, and it was really effective in accelerating my progress as I made sure to refer to those learning points and update them as I encountered new lessons. I believe it is a huge waste of an opportunity to be presented with a lesson, and then not take the time to work out what was the ‘take away’ or then allowing it to fall out of my mind so that I didn’t end up making a change next time around. Keeping these notes was a highly effective tool to make sure I was maximizing my ability to accelerate my learning, and I want to share that with you. These things really are like gold – when you find a nugget, keep it safe!

Applying the Technique

So how does this relate to Excel? I use the same technique to build out a kind of ‘personal knowledge bank’. Each time I learn a new technique, be that a new feature, a new application of a function, a new piece of VBA code etc, I make a point to save that nugget somewhere I can access it. I’ve set up a ‘demos’ file, in which I have a number of sheets, with example formula, and example code. When adding to the workbook, I take time to distil the new concept into a simple example, to showcase just the key idea, and ‘tag’ it with a number of search terms which I might use to find it later on to make sure that it’s easy to locate when I need it.

Example of a Nugget

When documenting your new learning, try to break it down into its component parts, and reflect on each part separately. For example, suppose you have just discovered a neat way to handle potential errors in calculations that avoids the use of IFERROR. Instead of copying an entire tutorial workbook, build your own Excel example and then elaborate, in writing, on what you have done.
Say you previously used to suppress errors in a ratio (e.g. gross margin %) calculation by using IFERROR, e.g. =IFERROR(D3/D1,0). But you have now seen a new technique which allows you to check for zero in the denominator, rather than an error in the overall calculation, and this is better because by avoiding IFERROR, it doesn’t mask other possible causes of errors. The new formula would be =IF(D1=0,0,D3/D1). You could make yourself a sheet in your knowledge base called “Margin Calc” and include some tags on the page like “IFERROR, DIV/0!, RATIOS, MARGIN PCT, MARGIN %, MARGIN PERCENTAGE” so that you can easily find it. Then set up example workings:

Having created your own example, explain it to yourself thoroughly in words, within the Excel workbook. This “teach it back to yourself” approach helps consolidate your understanding and makes it simpler to reference later. Summarising in your own words, and in your own example working, means you have made sense of the concept in a way that is personal to you, and this is much more likely to stay with you.

The importance of context

I have been able to learn a lot about Excel through my own research and experimentation, but there are limits to self-learning. The challenge is not just finding a way to solve a problem but recognising that there are often many ways to solve problems in Excel, as my IF function example shows. Then the real trick is finding the best possible solution taking into account the need for flexibility, reliability and simplicity. This can be subjective, and indeed scenario-specific! The Excel Community resources help establish the context in which potential solutions can be compared and assessed, in particular the other articles and publications such as the 20 principles for good spreadsheet practice and the Spreadsheet Competency Framework, together with the Excel Community on-demand courses.

Invest in Yourself

Personal development requires you to go beyond the day to day demands of your job, it’s an investment into your personal skill set which will likely outlast your current position or grade. Spending a bit of time outside work hours to upskill is really important, as learning new tools and techniques will enable you to work more efficiently, allowing you to spend more time on ‘value add’ activities rather than ‘just coding’, or indeed on the trial-and-error aspects of problem solving.

It’s a bit of a cliché, but it’s still a truism: “you don’t know what you don’t know”. Sometimes investigating things you don’t currently know about may be uncomfortable (e.g. making the move to XLOOKUP from the comfort of INDEX/MATCH) but ultimately will pay dividends for you down the line. So take the time to have a bit of a play, make a blank workbook, look up an article on how LAMBDA’s work (for example), and then try to recreate it!

Conclusion

During my years of shooting, and in professional services, my ‘personal knowledge banks’ have grown. I still maintain them, and I still use those same techniques to keep improving, and to keep ‘getting better at Excel’.

Excel is far more than a collection of functions and menus. It is a dynamic tool that rewards a curious, proactive learning style. By experimenting freely, seeking help when stuck, and thoroughly recording what you learn, you lay the foundation for developing a true expertise. The aim is not to memorise every feature, but to know where to look. In a world where AI gives the air of near-infinite knowledge but by design will provide the ‘likely’ answer rather than the ‘best’ one, your personalised resource will set you apart as someone who can not only find what you need when you need it, but fully understand and apply it. Most importantly, it empowers you to keep growing in a purposeful way, unlocking the full potential of Excel one ‘nugget’ at a time.

The ICAEW has now published 500 Tips articles – and there’s still so much to cover, from the latest functions, to refreshing those little-known timesavers that are as relevant now as they were when we first covered them. Along with many more supporting blogs and on-demand webinars, there is a wealth of knowledge available to members of the ICAEW Excel Community. If you have some time to invest in your personal development, the tips index is a great place to start - so here’s to many more!