Checks and Balances – Excel 2007 Conditional Formatting

What is conditional formatting?

Conditional formatting in Excel ensures cells are formatted based on certain criteria. For example if we have a cell is equal to “No” and we want to colour all cells equal to “No” red, then we can do this using conditional formatting.

Why use it in financial modelling?

There are a number of reasons you would use conditional formatting in in a financial model. One of the major reasons is to ensure model integrity through checks and balances. Examples of this include:

• ensuring that your balance sheet balances; and
• there are no negative cash balances etc.

Conditional formatting gives the financial modeller a visual representation as to whether these checks are met and the financial model integrity is upheld.

Using conditional formatting for model checks and balances?

Ok, the best way to learn conditional formatting is probably to look at a couple of examples. If you haven’t already done so open up the spreadsheet and theyoutube video at the top of this blog tutorial.

Ok first how do we find the conditional formatting button? Go to the:

• Home menu; the
• go to Conditional Formatting; and then
• we are going to use the New Rule and the Manage Rules.

See below for a diagram showing the conditional formatting button.

Now if we had a cell with Check and Ok and we wanted to change the cells:

• so that when it equalled Check it would come up with a red background and white font; and
• when it equalled Ok it would come up with a green background and black font.

So, let’s select the Ok and Check by holding down the shift key and using your arrow keys (or your mouse) and holding down the left click.

Once selected let’s go to Home, click the Conditional Formatting button and select the New Rule button. You should come up with the following screen. Enter in Format only cells that contain. Then select equal to and Check.

Now select format and choose fill, select red and then go to font and select white under colour. Push Ok and then Ok again. The Check should now have a red background with white font as shown below.

Whilst the Ok and Check cells are still selected go back to the conditional formatting button and select New Rule. Repeat the same procedure as above using equal to and Ok then select a green background with black font. You should come up with something like the below figure.

Now have a play around by changing the Check to Ok and vice versa. You’ll notice that the cells change formatting based on the value.

If you like this article, check out our training course Excel Shortcuts which has plenty more tips and tricks.