Excel over-engineering

Some might say that Excel functions are a little bit over-engineered. Like the car manufacturer that brings out a new model each year with a slightly better engine and up-rated suspension, Microsoft (bless it) has been bringing out new versions for years. The result might be a fantastically high performing machine but not one that necessarily makes it easier to get from A to B. In Excel’s case the result is an awesome product but one that can be a little difficult to navigate. A typical challenge in Excel might have four potential solutions and that makes part of the fun just working out which solution to use.

Today’s problem: picking out the right piece of data from a data set

Today we’re going to imagine something that seems like it should be a very simple Excel challenge: picking a piece of data out of a list.  We’re going to give you four solutions, tell you our favourite but, ultimately, leave it to you to make the decision about which you like.  Here are some potential solutions:

·         Sorting the data manually

·         Pivot tables

·         The Sumifs function

·         The Sumproduct function.

For most delegates on our training courses there’d be something a bit new within the list above!

Today’s challenge: picking a piece of data out of a list

Imagine a set of data.  We’ve got a first column for employee name.  We’ve got a second column for expense category (e.g. “travel”).  We’ve got a third column for amount.  It’s a long list but imagine we want to work out what one of our employees (e.g. “John”) spent on travel with multiple trips away – we want to know his total spend on travel.

What if we didn’t have all the power of Excel?

Without knowing what Excel can do for us, what we might think of doing is sorting the data (Data > Sort in Excel).  First by employee, then by category.  That would group the data for us and make it easier to see how much John spent on travel – but Excel can do better than that.

Pivot tables

Some delegates on a financial modelling course know about pivot tables and, if they’re not familiar with them, we’re happy to help them get started.  In this case, you’d need to select the data and go Insert > PivotTable in Excel.

Pivot tables are great for quickly sorting and presenting data.  They will update as your data changes, but only if you right mouse click and click “Refresh”.

For the investigation into John’s travel spend, a pivot table would represent a bit of overkill.  There are some standard Excel functions that will successfully pick out the correct data from the list.

Sumif, Sumifs and Sumproduct

Some of our course delegates know about Sumif and Sumproduct.  Sumif kind of does what it says on the tin – it adds data if it meets a certain condition.  We have multiple conditions here though – that’s why we need Sumifs.  You want to use Sumifs for multiple conditions.

Some of our course delegates know about Sumproduct – it multiplies one list of data by another and sums the result.  Fewer of our delegates know that you can build conditionality into Sumproduct.  For our investigation, we could use that to tell us quickly how much John spent on travel.

Is there a best solution here?  Who’s for pivot tables?

It’s fun trying to navigate Excel, including any over-engineering!  Some people love pivot tables – the way they will sort all your data very quickly and present it nicely.  Others though realise that some of Excel’s standard functions are perfectly competent and capable of picking out the one piece of data you are interested in and plonking it into the cell you want.

Sumifs vs. conditional sumproduct

It’s hard to differentiate between these two formulas though.  Sumifs is designed exactly to do this job – so there’s a vote for that.  But then, if you can understand how the conditional sumproduct works, and pause for a moment to consider how simple that formula ends up being, then you’d probably be forced to vote for that.

Here’s to engineering

Shall we just pause for a moment to celebrate the power of engineering and the way it brings us all the powerful products we love to use?  If that results in a little bit too much engineering sometimes – so what? It’s the small price we all end up paying for fantastic solutions!