How to make excel formulae more readable?

Question

I'm looking to make a bunch of my complicated formulas more readable. For example:

I know about Alt+Enter to add newlines within the formula itself. This makes the formula a little bit more manageable, but it's still not as readable as I'd like it. What else can I do to make big formulas like this one more readable?

Answer 1

I'm going to answer this myself as Excel 2013 doesn't have any truly good ways of doing what I want with formulae.

In my research I have found three ways of making formulae more readable. The first is something I mentioned in my question: Alt + Enter. This will insert a newline into the formula so that you can break up the wall of text. As an example you can change this:

into this:

The second method is to add a sort of comment to your formula. Personally I don't like this solution as it doesn't make formulae more readable to me, but others may like it. The method involves using the function N(). This function will return zero if given a string. For example:

Will give a result of 42 as N() returns zero.

The third method (shoutout to user shawnt00 for suggesting this in the comments) is to use named ranges. This can really help with readability as the user can convert something like A8:C14 to whatever name the user desires. To define a named range go to the Formulas table and click on "Define Name". Give the named range a name (preferably something that makes sense and not just "foo") and select the range of cells. Once done you can change something like this:

into a formula that is much more easily read:

The last method is to create extra columns and hide them (Thank you to user Quintin Balsdon for suggesting this). If, for example, you need the same value multiple times in one formula you can put the formula used to get that value into a separate cell that you hide and reference that cell. Take this formula for example:

On its own this formula isn't too bad, but if you take a close look at the formula in my question you'd see I do a very similar thing. Simply taking the index/match out and putting that into another cell and changing the above formula to

will make a complicated formula much smaller and much easier to quickly check what the value of J1 represents.