98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsPrevious article Next article Excel articles
Conditional Formatting: A Useful Feature Of Excel
Thu 24th March 2011
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
London's widest choice in
dates, venues, and prices
Public Schedule:
On-site / Closed company:
TestimonialsUnited International Pictures
Marketing Manager (Sony) Juan Pujol Introduction to Management (1 day) Thank you. Very interesting Oxford Immunotec
Customer Experience Supervisor Jess Walker Personal Leadership Tony is great at what he does. I always enjoy our training together Arrows Group
Payroll Adminstrator Maryam Chogri Excel Intermediate I feel as though today training has enabled me to broaden my knowledge using Excel. I might not be using some of the stuff that have been taught today however will be benefical for me in the long term. The trainer was effective in order that all topics were covered. Communication was excellent I was always able to understand all aspects of the training. Help was always offered and what had appealed to me the most was i was able to ask numerous questions throughout the training. I would like to thank Cindy for her time she has made a good difference. |
PUBLICATION GUIDELINES