Here’s a little secret: No one is impressed by that giant Frankenformula you wrote in Excel. Just like with writing anything, the less verbose you can write your formulas, the better.
✂ A few ways to cut your formulas down to something more efficient and readable (resources in comments):
🏳 Use white space:
OK, this doesn’t actually condense your formula, but it makes it far easier to read. Use Alt + Enter in the formula bar to place a carriage return in the formula. This is great for breaking nested formulas into smaller pieces.
🔂 Do not repeat yourself (DRY)
This is a classic piece of programming advice encouraging developers to avoid repetition. For example, if you’ve already spelled out a given cell range in your formula, why write it again? Fortunately, the new LET() function allows you to do just this, reusing bits of your formula and following DRY.
🆕 Ditch nested IFS()
These statements are notorious for producing verbose, complex formulas. Say goodbye to them with the new IFS() function which will allow you to check for multiple conditions in one formula. (White space is still a good idea here.) I’m sure there’s a use case where nested IFS() are needed still, and let us know in comments, but for most cases IFS() is a great solution.
💁♂️ Don’t fear the helper column
It’s perfectly acceptable to break apart your formula into smaller chunks. This may actually feel more verbose and complex to you, but often to your collaborators it’s just what’s needed to make sense of the model.
What have you done to make leaner Excel formulas? Let us know in the comments.
Leave a Reply