Most, if not all, pieces of analyses in Excel employ the IF function numerous times. In fact, it is one of those functions which analysts and Excel experts play with a lot. And, because of that, nested IF functions result much more often than one would imagine.
Well, that doesn’t sound like a problem in itself, or does it? One can see the wrath brought about by nested IF functions only when one has to debug them or fix any errors in them. And this is especially so when you are not the one who created them. But you do not have to fear them anymore. Excel MVP, Jordan Goldmeier, has an interesting tip for us to make nested IF functions readable.
So, let’s check out the tip.
1 – ALT + ENTER
While writing in a single cell, you might want to start from a new line. Would pressing ENTER work? No! If you press ENTER, Excel will throw you to the next cell in the row.
So, what’s the solution to this problem? It’s actually very simple. Press ALT + ENTER instead. Most of us would already know about this simple shortcut.
2 – Another Use
The good news is that the ALT + ENTER technique works with formulas as well, as long as they take multiple inputs. To see this, write a function like you normally would in a cell and press ENTER. Now edit it by pressing ALT + ENTER after placing your cursor to the immediate right of a “comma”. And press ENTER now. You would see that the formula would still be working as it normally does. An illustration is in the image on the right.
3 – The IF Function
Separating out the inputs of an IF function using ALT + ENTER can increase its readability quite dramatically. We can do this by putting “value_if_true” on a separate line and “value_if_false” in another line. Of course, adding spaces before them (which will act like indentation) is a good idea, especially if you have nested IF functions. The image below shows an example of this:
What’s next?
Start using this structure to make your spreadsheets more readable.
Also, share this tip with your colleagues. It will make it easier for you to understand their work!
- SSSVEDA DAY 7 – Every Team Needs Someone Who Understands Data - February 18, 2018
- SSSVEDA DAY 5 – When Data Analysis is Wrong - October 31, 2017
- SSSVEDA DAY 4 – Sharing the Excel Knowledge - July 18, 2017
This site is worth a look for “beautifying” complex formulas: http://excelformulabeautifier.com