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:
=IF(ISERROR(INDEX(Sheet1!$A$1:$E$162, SMALL(IF(Sheet1!$A$1:$E$162="ABC", ROW(Sheet1!$A$1:$E$162)),ROW(1:1)),4)),"", INDEX(Sheet1!$A$1:$E$162, SMALL(IF(Sheet1!$A$1:$E$162="ABC", ROW(Sheet1!$A$1:$E$162)),ROW(1:1)),4))
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 faint 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:
- PivotTables
- Aggregation functions like:
- SUMIFS
- AVERAGEIFS
- COUNTIFS
- AGGREGATE
- SUBTOTAL
- MIN/MAX/SMALL/LARGE
- DSUM/DAVERAGE/DCOUNT etc.
- Lookup functions like:
- VLOOKUP/HLOOKUP
- INDEX
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:
Hi
I broadly agree with this post, but there are scenarios where I’m not sure it holds as well as others.
If the data source for these reports is well formatted, tabular data that is either accessed by ODBC or is pasted into a data worksheet and the output required is a highly formatted, fully automated (i.e. no intervention) report them I’m not sure some of the techniques mentioned here are ideal.
One approach I like to take to uber-complex formula is to seperate it out across cells and then hide those cells that are not require to be viewed. This also fit a programming paradigm where the output of cells become reusable throughout the report.
Anyway .. my 0.02.
That fits in with what I recommend too. Your data is already in the ideal format – tabular. I agree that breaking your mega formulas down into smaller chunks in helper columns is often more efficient than one huge formula.
Absolutely brilliant post, Mynda.If I hadn’t already invested days and days writing a similar introduction in my (still unfinished) book, I would hire a hitman so that I could steal this content for myself.
Aw, thanks Jeff. That is the ultimate compliment 🙂
I was wondering how the book was going. I imagine it’s a mammoth task.
Mynda
To echo more…
I have just seen a report from a colleague with monster formula… it’s SIX lines long with IF, SUMIFS, and ISERROR. This formula is copied across 10+ columns and all the way down to row 10000.
I could imagine how long it takes to recalculate and I can’t help myself to set the Calculation to Manual.
Finally, I saw the final note on the sheet: “It may run for more than 30 minutes when save”. Speechless… 🙁
With raw data set up in a tabular format, a Pivot Table that is lightening fast can replace all the monster formulas.
But that’s the real world, I guess. :p
Fixing formulas like this is like eating an elephant….one mouthful (formula) at a time and one day we might finally get there 🙂
Great post! Thanks!
Most users like “report layout” since it is easy to read for them (they think so).
SAP or similar systems are cool but make monkeys out of the users. They say: “One click and I have the report I want” and I say “That is true, but we’ll see for how long. In a year or two, you will need another report that your system is unable to provide and then you will not be willing to pay hundreds of thousands $ for modifying your system”
What users do when it comes to that? They try to build a report based on another or they take many reports to build the one they (or their boss) want. The ant work begins!
People stop doing that! Just ask IT to get the source data for that reports and you will be able to build your own reports in the way you want (or need).
THE RAW DATA IN TABULAR FORM IS THE WAY TO GO!
Sure is, Pmsocho. Glad you’re in complete agreement 🙂
Great post!
“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 ”
Can’t agree more! Actually, the skills to have the data set up in the right way sound easy, but not commonly available yet… So no worry on job security.
Cheers, MF. Glad you’re of like mind 🙂
Hi Mynda, once again an amazing post!
The title, and the sentence “…. 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 …” were real eye openers for me.
I realised I have a number of files where, if I put the raw data in tabular format, I can do away with most of the formulae (not really complex ones at that) & just use a pivot table to have a simpler & more professional looking report.
Many thanks for sharing.
Thanks, Peter. Glad I could give you a different way to approach your Excel solutions.
MyndA
Hi Mynda,
I am always looking forward to reading your newsletter. I rarely leave a comment, but trust me, I read, learn and apply most of what you have shared.
The content is top quality, easy to read and understand, it’s just great. I appreciate your dedication and effort.I know it requires a lot of time, creativity and work to keep it up. So thank you.
I decided to leave a comment today, because this newsletter goes beyond Excel, it’s pure wisdom that can be applied to many situations at work.
Once again great content and thank you.
Pablo
Hi Pablo,
Thanks for taking the time to leave a comment and your kind words.
It’s great to know this topic has been well received.
Mynda
I have always been comfortable working with data but I have recently started to analyse a data set for that has 700 fields of data and the extract that generated the data has done it “vertically” rather than “horizontally” – ie I have 700 rows and x columns.
what is the simplest way to transpose the data?
Is it feasible to work with / navigate through a table with 700 columns?
Many of the columns I do not need for most of the analysis work that I would be doing.- but want to keep format for any additional updates of data
There is a potential of a carrying out analysis on a larger data set of 400 items (not a lot in excel terms ) – but only if I can keep it manageable and not get lost in a sea of data with so many fields
Hi Paul,
It sounds like your data isn’t in the ideal tabular format.
You will definitely struggle with 700 columns, and if you can avoid bringing data into your file that you don’t need then that’s the absolute best option.
You can use this reverse PivotTable to unpivot your columns, or you can use Power Query to do so and then have it automatically update when new data is added to the file.
Please email me with a sample of your data if you have any questions.
Cheers,
Mynda
what does CTRL+ALT+ENTER do?
Hi Jim,
After you edit a formula, if you press the Ctrl+Shift+Enter, Excel will process that formula as an array formula, not as a regular formula. CTRL+ALT+ENTER does nothing, it’s a typo 🙂
Cheers,
Catalin
Oops! I’ve changed ALT to SHIFT. Thanks for spotting my typo, Jim.
Mynda