Excel Best Practices To Avoid A Black Box Model

I build a models/automated spreadsheets for my team as one of my tasks. I'm pretty good with Excel and can make my spreadsheets highly dynamic. This is necessary at times for 1) repeatable/foolproof processes and 2) property managers (lol).

This means that I often find myself using multiple nested functions like sort>filter>unique or iferror>if>sumproduct>sumif>indirect. The formulas never go beyond one or two lines in the formula bar. But they're still so robust (convoluted) that I can't imagine myself, let alone someone else, coming back and decoding it just to make one adjustment to the formula.

What are your best practices for creating easily digestible formulas that still perform really dynamic functions? Any resources or general tips?

Comments ( 13 )

5d
Capital360 , what's your opinion? Comment below:

Nested IFs are just the worst but sometimes the easiest route to a goal in excel. There are generally always a way around them, it just depends on how you are breaking down the info prior to going into a formula and knowledge of other functions. But at the end of the day it doesn't matter if it's ugly, if it works, it works.

Here is a detailed thread on peoples most used formulas on the job

https://www.wallstreetoasis.com/forum/real-estate/excel-formulas-you-use-constantly-on-the-job#comment-1749004

  • Associate 1 in RE - Comm
5d

I know better by now than to use nested IF's. My issue was moreso with nesting different functions because there are several "steps"/actions I'm trying to complete in one fell swoop.

And the issue isn't getting something to work now. I can get my formulas to work. The problem I guess I'm having is what coders typically call "documentation". How do I know what the hell any of this formula I wrote 2 years (or even months) ago is doing, without spending hours parsing through it and potentially creating major bugs?

Now that I think about it, anyone know of an Excel plugin that lets you break out your formulas into different lines and lets you add comments to them (like Power BI and any relatively robust coding programs have)?

Most Helpful
4d
pudding , what's your opinion? Comment below:

Instead of making long formulas, use "helper rows." This will show each step in the process and logic you are using as opposed to putting it in one formula. The person who made the models at my firm (who has since left) used 2/3/4/5 lines in the excel top bar to write his formulas and our acquisitions analyst has been untangling the web and making a new template model to use over the last twelve months. While the old models work really well, having to sort the logic is a pain in the a*s and just takes longer to make changes. The helper rows means someone can go one row at a time and quickly determine what it is that you did in each row, to figure out what the final row (summation/subtraction/product/division of what's above, is actually doing).

You state above that you want an excel formula or program to break rows out etc or tell you what you did.
You can always leave written comments to tell you what you did. However, if you think you will have trouble and you wrote it - unfortunately that probably means someone else will have more trouble. While I'm sure it works and it is probably a good model - you should probably take the time to re do it in the helper row fashion I list above. That way you, or the person who takes over after you, can actually understand what you did. Good logic and code in excel usually, though not always, also means it is easy for anyone to pick up and quickly determine what you did.

  • Associate 1 in RE - Comm
4d

Very helpful. I think helper cells is the move. Thank you!

Learn More

300+ video lessons across 6 modeling courses taught by elite practitioners at the top investment banks and private equity funds -- Excel Modeling -- Financial Statement Modeling -- M&A Modeling -- LBO Modeling -- DCF and Valuation Modeling -- ALL INCLUDED + 2 Huge Bonuses.

Learn more
3d
Angus Macgyver , what's your opinion? Comment below:

I tell junior analysts this all the time. Getting all fancy with complex formulas is cool and all, but it is a real pain in the ass for me when auditing your model. Plus makes it way more likely you will mess something up with a bracket in the wrong place or something.

You can use as many rows and columns as you want. Stop putting 10 formulas in a single cell that is pulling data from 5 different tabs and 2 external workbooks.

4d
rf949 , what's your opinion? Comment below:

If you have long formulas that are hard for you to review and audit -- then you're doing it wrong.

If you find your formulas are too long - break them up into steps.  If a formula is doing 3 things - break it up into 3 formulas in 3 different columns or rows.

TLDR: modeling with baby steps is always best, and easiest to audit and follow what's happening.  Also use good descriptions for what is happening in each column / row.  If you can't pick up the model you're working on 6 months from now and quickly trace back how it works -- then you aren't taking small enough baby steps.

  • 1
4d
ASEANalyst , what's your opinion? Comment below:

Depending what the model is for, i usually always go the route of having 1 sheet for every flags/triggers of fixed/time based assumptions.

Some people prefer to put their flags on their calculation sheet but it makes the calculation sheet overly crowded with different outcome of each rows which makes it inaccessible for newer analyst/3rd parties to read.

Use only 1 string of IF(AND(OR if possible, never use IF within an IF since once the model is broken, fixing them would be a nightmare.

My advice is similar to other posts in this thread...

Always follow baby steps logic on each of your models. Makes the model easier to understand, audit, and develop. Having 1 line of super complicated formula for the sake of clean looking model is one of the worst thing you can do. Especially if the model gets thrown around. People get intimidated immediately and coworkers will hate you for it.

4d
Abel Tiffauges , what's your opinion? Comment below:

In cells and columns,

Paste Special's mystic force,

Data alchemy.

14h
HealthcareRE , what's your opinion? Comment below:

Just want to echo the other poster's comment about helper rows/columns - this makes things so much easier to audit.

Also, please please think three times before using named ranges. I know they can be convenient but there's nothing worse than stepping into a workbook full of 500 convoluted named ranges if you're just trying to understand what's going on.

13h
128 , what's your opinion? Comment below:

Maxime repellat dolore eos cum facere itaque voluptas. Est enim eveniet accusamus excepturi nihil quibusdam. Officiis non vel et perspiciatis.

Start Discussion

Career Advancement Opportunities

March 2023 Investment Banking

  • Lazard Freres ( + + ) 99.5%
  • Jefferies & Company ( ▽01 ) 99.1%
  • Financial Technology Partners ( = = ) 98.6%
  • Lincoln International ( ▽02 ) 98.2%
  • William Blair ( ▲10 ) 97.7%

Overall Employee Satisfaction

March 2023 Investment Banking

  • William Blair ( ▲04 ) 99.5%
  • Canaccord Genuity ( ▲18 ) 99.1%
  • Lincoln International ( ▲09 ) 98.6%
  • Stephens Inc ( ▲10 ) 98.1%
  • Jefferies & Company ( ▲05 ) 97.7%

Professional Growth Opportunities

March 2023 Investment Banking

  • Financial Technology Partners ( ▲11 ) 99.5%
  • Lazard Freres ( ▲14 ) 99.1%
  • Lincoln International ( = = ) 98.6%
  • Jefferies & Company ( ▽03 ) 98.1%
  • William Blair ( ▲01 ) 97.7%

Total Avg Compensation

March 2023 Investment Banking

  • Director/MD (6) $592
  • Vice President (26) $422
  • Associates (140) $260
  • 3rd+ Year Analyst (9) $194
  • 1st Year Analyst (260) $171
  • 2nd Year Analyst (84) $170
  • Intern/Summer Associate (43) $164
  • Intern/Summer Analyst (191) $92