Conditional Formatting: A Useful Feature Of Excel

Thu 24th March 2011

Conditional formatting is not nearly so daunting as it sounds. It is an extremely useful tool that can make cells perform in many different ways. As the example in this article shows, conditional formatting can make a simple alert that is activated when the user's finances go into the red.

Outside of the office one of the most common uses that Excel is put to is in keeping track of the household budget. I have a workbook set up to track my finances for the whole of 2011 (where each monthly sheet is accessed by the use of nifty buttons) and I have applied a simple formula to do some of the calculating for me.

I have a cell in which I enter my total monthly earnings and next to this a list of my outgoings for that month. To the right of this is the formatted column in which I enter how much I have paid for each expense. I pay my bills in different ways and on different dates so it is important for me to keep this column updated. At the foot of this column is a formatted cell, which subtracts everything above it from the cell containing my earnings.

This simple set up allows me to keep track of how much I have left after paying my obligations for that month. If I was to have a month when my outgoings exceeded my income and my total slipped into the red, there would be nothing to draw my attention to this other than a miniscule minus sign. There is, however, a very easy way to create an alert for such a happening.

Let us take the case of a window cleaner, whose income fluctuates a lot because of setbacks such as bad weather or people not being home when he collects. If he does not have enough coming in to cover all of his outgoings then he needs to be made aware of this fact at the earliest opportunity. Excel can alert him by showing a coloured cell if the amount coming in is less than that going out, and the obvious choice of colour for this cell is red.

Our window cleaner has a column for his takings with total in the cell B50. His outgoings are logged in an adjoining column, with their total displayed in the cell D12. In cell D14 he inserts a simple formula to subtract his outgoings from his earnings (=D12-B50). This is the cell he wants to glow red if his outgoings exceed his income.

He selects the cell D14 and goes to Format/Conditional Formatting. In Condition 1 he chooses Cell value is and Less than, and he enters zero. Then he clicks on Format and selects the Patterns tab. He selects red from the choice of colours and clicks OK. Cell D14 is now formatted to turn red if the total within goes below zero.

To demonstrate Conditional Formatting in more detail, let us have our window cleaner format cell, D14, to show up black when his finances are in the black. To do this he would have to alter the colour of the text as well as the cell, as the black numbers would be invisible against a black background.

So with the cell D14 selected again, he goes to Format/Conditional Formatting and clicks on the Add button, which brings up Condition 2. This time he selects Cell value is greater than zero, as he wants a colour to show his positive status. He clicks on Format and then the Font tab to select a colour from the options available - in this case he chooses white. Then he clicks on the Patterns tab and he selects black just as he selected red earlier. He clicks OK and that all-important cell that contains his immediate financial status now shows white text on a black background when his affairs are in the black, but it changes to black text on red if his finances show a negative return. If is status is zero then the cell remains white.

Conditional Formatting is a useful tool that has many more applications than the single cell example I have given. It is very easy to get to grips with and a little experimentation will soon have you proficient in its use. As with many other features of Excel, Conditional Formatting is a lot less daunting than it sounds, and a few simple actions can create a function that serves a genuine purpose.

Author is a freelance copywriter. For more information on excel training london, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1563-conditional-formatting-useful-feature-excel.html