There comes a time in many Excel users’ careers where we start to write incredibly complex Excel formulas to summarise or extract data from poorly structured workbooks. I’m not talking about relatively simple nested functions like INDEX and MATCH, I’m talking about monsters like this:
And as we press CTRL+SHIFT+ENTER on massive array formulas like the one above we realise we’ve reached the echelon of Excel Guru.
But have we? For a while we think so. And we should be proud, after all formulas like that are not for the feint hearted. It shows we’ve achieved the level of black belt formula writing that most will never attain.
However, in our effort to handle more and more complexity through formulas we’ve failed to see that the problem was never in our lack of ability to write these formulas in the first place. It was that we allowed ourselves and others to structure workbooks in a way that meant we had no other choice.
For the true Excel Guru knows that if the data were in a Tabular format we’d be able to use the amazing tools Excel has available. Tools that make mincemeat of complex formulas like the one above. For example:
- Aggregation functions like:
- DSUM/DAVERAGE/DCOUNT etc.
- Lookup functions like:
You see the Excel Guru knows that poorly formatted workbooks that require complex formulas to aggregate, analyse and extract data are error prone, often slow to respond and typically suffer from MB bloat.
So if formulas like the one above scare the bejesus out of you then fear not. All you need to learn is the correct way to structure your data and then you can use the built in tools the way they were intended.
Say No to Complex Excel Formulas
Here at MOTH we often get asked for help with writing complex formulas. I used to take on the challenge of writing monster formulas to solve these problems and accommodate poorly structured files. But nowadays I prefer to encourage a better data structure and easy solutions, because when things are easy you can do them yourselves and they’re easily handed on to the next person who inherits your workbook.
Now you might be thinking:
“where’s the job security in that?”
And you’re right. If you make things easy for everyone to use then you’re more easily replaced. But the flip side is if you make things easy to use and help others look like Excel Rock Stars then everyone is going to think YOU ARE THE EXCEL GURU and you will be indispensable 😉
Now not all complex formulas are the result of poor workbook layout. Some array formulas are the best or only solution, but when there is a way to achieve the end result more easily by reformatting your data, then I encourage that every time.
Common Challenges Solved without Complex Excel Formulas
|Excel Challenge||Complex Solution||Simple Solution|
|Extracting a subset of data from one sheet to another||Nested function array formula||PivotTables to Extract data|
|Summarizing data spread over multiple sheets (3D Formulas)||3D SUMIF/S array formula||Tabular data and SUMIFS/AVERAGEIFS/COUNTIFS etc. or even PivotTables.|
|Extracting a unique list of values||Array formula||PivotTables automatically create a unique list of items in row labels. Or you can use Advanced Filter.|
|Multiple sheet VLOOKUP formulas||3D VLOOKUP array formula||Tabular data and a regular VLOOKUP or PivotTables|
So, next time you’re faced with a complicated formula you should ask yourself if changing the layout of the workbook could be the solution instead of writing a mind bending formula that is likely to end up broken the minute a less experienced Excel user tries to edit it.
Note: this may also apply if you’re reaching for ATL+F11 to open the VBA editor every time you come across something you can’t solve with the built in Excel functions and tools.
Fixing Workbook Layouts
No doubt you’ve seen a few poorly laid out workbooks in your time. You may even have some of your own. I hope this post motivates you to learn the correct way to structure your data and fix the layout of those workbooks.
If the idea of converting them to a Tabular format is daunting then check out Power Query which makes the task easy. In this video at the 3m 32s mark I show you how you can quickly unpivot data into the ideal tabular layout:
More Power Query
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.