10 Popular Excel Formulae: OVERRATED / UNDERRATED?

Are these 10 Popular Excel Formulae Under- or Overrated?

There’s so much noise out there in Excel land at the moment!  Excel updates from Microsoft, endless content on YouTube.  You could never learn it all; so, what Excel formulae do you actually need to know?

FREE: YOUR EXCEL CHEATSHEET DOWNLOAD

The truth is, an Excel beginner need only learn a few formulae to accomplish what they never thought possible in Excel.  So, let’s cut to the chase: which of these 10 popular Excel formulae should you actually learn?  And which are overrated?

FILES DOWNLOAD

CLICK HERE TO WATCH THE VIDEO

1) XLOOKUP – is the most talked about formula from the bundle of new formulae added to Excel a couple of years ago.  Though, as some claim, its capabilities are not ‘game-changing’, they are certainly impressive: the power of MATCH / INDEX (more on those two later) in one function.  But, how can you rely on it if an up-to-date version of Excel is required? I still don’t use it in my consulting work because of this and this is why, in my view, it’s overrated.

XLOOKUP FOR BEGINNERS

2) MATCH – the great facilitator.  The formula that makes the others look good.  Yes, MATCH quietly but powerfully gives us control of the one thing that really matters in Excel: position.  The concept is simple: it returns the ‘number of cells along’ a range that a target value appears.  So, it returns a single value.  Why not add this underrated Excel formula to your formula toolkit?

MATCH TUTORIAL

3) INDEX – so why do I think INDEX is overrated?  It’s only when you look at the other options that you see why.   You see, for me, OFFSET is a better alternative to INDEX.  Are you going to make the switch today?  Read on.  A little harsh perhaps, but, from this perspective, INDEX is overrated.
4) OFFSET – many are yet to appreciate the game-changing capabilities of this formula.  First, why is it better than INDEX?  For me, it’s simple: OFFSET requires only a single cell ‘anchor point’ for a dataset, whereas INDEX requires us to specify the whole data set.  So, OFFSET can easily handle changing datasets because it only needs ONE anchor cell. 
5) VLOOKUP – I’m not denying people the satisfaction of getting a new formula to work – we all love that.  It’s just that there are so many other formulae (MATCH? DSUM?) that get little recognition and could be just as momentous in your Excel learning.  Why can’t VLOOKUP share the limelight?  That’s why, for me, it’s just a touch overrated.
6) PIVOT TABLES – Trigger Warning!  I’ll cut to the chase: Pivot Tables are overrated.  Yes, I just went there.  There’s no doubting their supreme computational power: instant analysis, at least for the advanced user.  But, how many people can actually harness their power?  How many users can confidently set up a pivot table, or tweak one to get the results they need?  The average professional cannot.  Interested in other options?  Great, simply read on.

ALTERNATIVES TO PIVOT TABLES

7) SUBTOTAL – I was introduced to this formula through my work in the field of football trading which is a massive market here in the UK.  Something was very fashionable in this field: using SUBTOTAL and filtering to find the sum of filtered rows.  Yes, it works.  But, the clicking!  Setting up a filter, unless you’re good with keyboard shortcuts (most are not) requires a lot of clicking.  It feels very ‘manual’.  Yes, SUBTOTAL allows us to do other things too but, once you’ve seen the alternatives (below), I doubt you’ll ever return to SUBTOTAL to sum filtered rows.  It’s overrated.
8) DSUM – one of my Excel secret weapons.  Need the power of a pivot table in a formula?  Need a lean alternative to SUBTOTAL?  This formula can speak for itself – check out the examples that come with this article.  (Hugely) underrated.

DSUM TUTORIAL

9) INDIRECT – OK Chris, so if you’re not using pivot tables, or tables at all (I generally don’t), how are you getting dynamic function?  In other words, how do you work with datasets that change in terms of numbers of rows/columns?  You’re not using static references, are you?  No – I call on the power of INDIRECT.  Check out the example that comes with this article that includes a dynamic chart powered by INDIRECT. 
10) UNIQUE – another of the new formulae that’s often in use in the Tiger office.  This is because it is truly … unique.  It actually does something that couldn’t easily be done with formulae previously - listing unique values.  For this reason, it’s the most useful of the newer Excel formulae.  Would I use it in a client file?  Probably not, for reasons mentioned previously – but it’s already saved me a huge amount of time in personal work.

FREE: YOUR EXCEL CHEATSHEET DOWNLOAD