Excel – Pick the right tool for the job

Who does the caveman go to when he has a tooth ache? The dentist of course! Only back then, as you can imagine, the dentists’ tools were quite different to those used today. It has taken millennia to refine the tools we use in everyday life.

Part of the challenge of using Excel properly is understanding the vast collection of tools and functions available. The reality is, even with knowing as little as 10% of Excels capabilities you’re likely to be able to conjure up amazing models.

The main aim should always be to maximise efficiency through effective use of the tools – to provide the most transparent and coherent outputs. To achieve this we must consider the tools available, when best to employ them but also when best to avoid them!

So what are these must-know tools, I hear you ask?

Tables:

As analysts we tend to dive straight into the aggregation and analysis. Often we don’t give much thought to how our information is stored and ordered. For me my first effort is to collate the data I need and house it in tables. These banded rows of data have transformed the way I work. Tables contain data in a single entity. They force me down a modular-design route and tend to support formula and/or pivot table aggregation with much greater ease.

One feature of tables that I especially like is calculated columns. These formulas automatically scale when data is added or removed to/from the table. Better visual filtering is supported using slicers. The formula syntax is descript, and elements are referred to by their labels rather than their position on the grid. They look better and work better – so if you haven’t yet explored these then I highly recommend you explore these sooner rather than later.

Formulas:

Without doubt the most popular approach to problem solving. But also possibly the most complex subject to master in Excel’s arsenal of features. The caveman in the cartoon might have used the entire fish to attempt removing an obstruction from the patients’ mouth (well rather that than the club, eh?). An advancement may have been to use a little fish bone as a tooth pick. I might then liken this to the typical over-use of the VLOOKUP exact-match method, when the approximate-match method could be configured to yield the same result, with far greater effectiveness and efficiency.

As a trainer I note a rather significant dependency on Volatile functions such as INDIRECT and OFFSET, often a major culprit behind those grinding slow aggregation models. Volatile functions have their uses, but a good understanding of how formula’s actually work will likely have you reconsider how you organise your information and you are likely to find the necessity of volatile functions far and few between.

And as for the typical use of SUMIF(S) / COUNTIF(S), have you considered…

Pivot Tables:

Pivot Tables have been around for quite some time now, yet the volume of analysts I encounter who either don’t use them, or won’t, flabbergasts me. Pivot tables are feature-rich, incredibly quick to build and a lot less likely to contain errors, unlike a formula approach. If you don’t favour pivot tables it’s likely that you just haven’t learned their full capabilities. But here’s the cruncher – even if you don’t like them now, you need to learn to love them. Pivot Tables are here to stay and what with the introduction of Power BI (most notably PowerPivot), they’re likely to become a much more prevalent and relied on feature.

Conclusion:

So there you have it, my shortlist of Excel essentials. Tables, VLOOKUP (specifically the approximate-match method) and Pivot Tables. Ask me again in 6 months-time and I’m likely to add Power Query to the mix, but I just don’t think we’re quite there yet.

Remember, it’s not just about what tools you have, it’s about how you use them. Your dentist won’t extract your tooth using a club (we hope). Don’t beat and butcher your models with complex, convoluted methods. Learn just a handful of features and functions, learn when and how to employ them, and once you’ve mastered them, learn something new. Evolution, not revolution!

Kindest regards,

Jacob Blackmore ACCA Linked In

Owner and Finance Director

Excel Evolution Ltd