Video Tutorial: Conditional Formatting

By Marc Casal on November 8 2010

Video Transcript

This video tutorial is on conditional formatting. Conditional formatting is an easy to apply Excel tool used to allow formatting onscreen to dynamically update subject to certain conditions.

Some of its main uses are:

• To create visual flags such as binary flags
• To create visual guides for inputs so that the user knows when to input certain data
• And to make unsightly data less prominent so that your spreadsheets look a bit cleaner

We’re now going to run through an example on how to create conditional formatting. You can see that conditional formatting can easily be applied to create visual flags. In this case, however, we are going to hide unsightly data.

All you have to do is select the relevant area which needs to be conditionally formatted and click Alt + O + D. In the menu which pops up, click ‘New Rule’. A number of options appear. In practice we only see two:

• Using a formula
• And, in this case, using cell contents

We’re going to tell Excel to change the formatting onscreen if the cell contains a zero.

Now, if you click ‘Format’ a number of options will appear. You can change the number format, you can easily add a border or you can change the cell fill. In this case we are merely going to change the colour of the text so that it stands out less.

Once that is done, you can press ok a few times to go back to your current worksheet. As you can see, less useful zero ratios in this spreadsheet are now coloured grey so that they stand out less.

Additional uses of conditional formatting are covered in our training course PFM(A). To learn more about this topic and to find other financial modelling tutorials, please visit our website www.navigatorPF.com and also www.fimodo.com.

Comments