Conditional Formatting and Creating a Zero/One Toggle Switch in Excel

Models and other Excel spreadsheets often need to employ a toggle. A toggle is a mechanism used to turn certain assumptions or parameters on and off. An example might be a debt refinancing – the model might need to look at the cash flow profile is the debt is refinanced or not. A very common way to approach this problem is to create a toggle Yes/No cell in the assumptions section of the model, and use an Excel favorite in the actual calculations – the IF statement. So the line in the model that calculates interest expense on the new debt would have the following logic:

= IF(ToggleCell=”Yes”,Average(BegginingBalance,EndingBalance)*Rate,0)

There is really nothing wrong with the above formula. But when the math becomes more complex, using IF statements can result in really long formulas. Also, if the toggle affects a multitude of cells, it can be tedious to keep creating IF statements everywhere.

An alternative to the above approach is to use a 0/1 toggle. Here, the toggle cell has either the value 1 (meaning “yes” or run the scenario) or 0 (meaning “no”). The benefit of doing it this way is that you can simply put a multiplier at the end of each relevant formula to serve as the On/Off conditionality. In other words, using the example above, the formula would become:

= Average(BegginingBalance,EndingBalance)*Rate*ToggleCell

So if the toggle cell is 1, the calculation yields a value. But if the toggle cell is 0, the result is zero as well (i.e. the scenario is off). Much cleaner.

A useful wrinkle on this idea is to use a Custom Number format on the 0/1 toggle cell. People would likely prefer to show the words “Yes” or “No” in the toggle cell to make the spreadsheet more readable, but still have Excel recognize a 1 or 0 value so that the multiplier technique can be used. On the toggle cell, create a custom number format that looks like this in the Type section:

[=1]”Yes”;[=0]”No”;”Invalid”

Excel will interpret this is as IF, THEN, ELSE logic for the value in that cell. So if the cell contains the value 1, Excel will allow the value to be used in cell references, but it will display the text “Yes” (or whatever you type in the quotes). If the user puts in something other than 1 or 0, they will see the word “Invalid”.

Here’s how it looks on the screen: