• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Avoid Writing Complex Excel Formulas

You are here: Home / Excel Formulas / Avoid Writing Complex Excel Formulas
complex excel formulas
March 21, 2016 by Mynda Treacy

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:

power query unpivot youtube video

More Power Query

Click here for more examples of Power Query in action.

complex excel formulas

More Excel Formulas Posts

excel formula by example

Excel Formula by Example

Excel can now write a formula by example. Simply give it an example or two of the result and Excel will write the formula.
ai-aided excel formula editor

AI Aided Excel Formula Editor

Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.


Category: Excel Formulas
Previous Post:Simple Code to get Started With VBAGet Started With VBA – 3 Easy Examples
Next Post:Excel Compare Two ListsCompare Lists

Reader Interactions

Comments

  1. Pmail Smail

    November 10, 2016 at 12:31 pm

    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.

    Reply
    • Mynda Treacy

      November 11, 2016 at 8:31 am

      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.

      Reply
  2. Jeff Weir

    April 2, 2016 at 11:47 am

    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.

    Reply
    • Mynda Treacy

      April 3, 2016 at 2:55 pm

      Aw, thanks Jeff. That is the ultimate compliment 🙂

      I was wondering how the book was going. I imagine it’s a mammoth task.

      Mynda

      Reply
  3. MF

    March 31, 2016 at 5:19 pm

    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

    Reply
    • Mynda Treacy

      April 1, 2016 at 12:20 am

      Fixing formulas like this is like eating an elephant….one mouthful (formula) at a time and one day we might finally get there 🙂

      Reply
  4. pmsocho

    March 29, 2016 at 11:49 pm

    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!

    Reply
    • Mynda Treacy

      March 30, 2016 at 12:03 am

      Sure is, Pmsocho. Glad you’re in complete agreement 🙂

      Reply
  5. MF

    March 29, 2016 at 1:42 pm

    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.

    Reply
    • Mynda Treacy

      March 29, 2016 at 1:58 pm

      Cheers, MF. Glad you’re of like mind 🙂

      Reply
  6. Peter

    March 25, 2016 at 8:40 pm

    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.

    Reply
    • Mynda Treacy

      March 26, 2016 at 9:14 am

      Thanks, Peter. Glad I could give you a different way to approach your Excel solutions.

      MyndA

      Reply
  7. Pablo

    March 25, 2016 at 3:08 am

    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

    Reply
    • Mynda Treacy

      March 25, 2016 at 1:49 pm

      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

      Reply
  8. Paul

    March 24, 2016 at 12:50 am

    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

    Reply
    • Mynda Treacy

      March 24, 2016 at 6:24 am

      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

      Reply
  9. jim

    March 23, 2016 at 10:38 pm

    what does CTRL+ALT+ENTER do?

    Reply
    • Catalin Bombea

      March 23, 2016 at 11:42 pm

      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

      Reply
    • Mynda Treacy

      March 24, 2016 at 6:00 am

      Oops! I’ve changed ALT to SHIFT. Thanks for spotting my typo, Jim.

      Mynda

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

launch excel macros course excel vba course

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.