Awards
Join OfficeTipsAndMethods

Have some suggestions to share with the world?

Join OfficeTipsAndMethods and share your insight in a Comment. You must be a member to comment and all comments are moderated before being published. Membership is free and we would never share your personal information with anyone.

Become an OTM Member

Log In

YouTube Video Channel
OfficeFix
Article Categories

Posts Tagged ‘highlight cells’

Conditional Formatting–Everything You Wanted to Know but Were Afraid to Ask

At one time one of my job responsibilities included budget management and financial reporting for the charity I worked for. Among other things, I had to identify, analyze, and report on variances on each account that was $1,000 or more over or under budget. At the time, my employer operated offices in four locations, with three or four departments in each location. So I had a fair number of accounts (as I recall about 600) to review every month just to identify those that needed further attention.

Keep in mind that personal computers spreadsheet software were, if not in their infancy, still in their very early youth. These were the days of DOS and non-graphic spreadsheets. Colour monitors, were as yet virtually unheard of.

So, at the time, the best solution I could come up with was a conditional formula in cells adjacent to the variance column that displayed an asterisk when the actual value was $1000 or more over or under budget.

image

image

Formulas

The formulas in column G use the ABS() function to calculate the variance. Printed reports had to show this value for all accounts. The formula first calculates the difference between the actual and budget for the account. ABS calculates the absolute value of the result. So whether the variance is positive or negative, it will be displayed as a positive number.

Column H will display asterisks only if the variance is $1,000 or greater. The formulas use the IF() function to test the variance for being equal to or greater than $1,000. If the test is true, the cell displays an asterisk “*”. The third argument “” cause the cell to display a blank. “” is known as the empty string. You can use it anywhere that you need to have text with no actual characters.

Welcome to the recent past

By today’s standards my solution was rather primitive. With the advent of Graphical interfaces in general and Excel for Windows in particular, conditional formatting became available. With conditional formatting, you can control the format of a cell depending on a rule you create. The format of the affected cells changes automatically depending on whether the rule is true or false whenever the worksheet is recalculated.

So now, something like this became available:

image

You could set up to three rules so you could highlight cells differently according to the rules you set. So you could visually rank cells according to their values.

image

Here I have three rules. If the cell value is less than $500.00 the fill colour should be green. If the cell value is $500.00 – $999, the fill colour is yellow. For variances of $1,000 and more, the fill colour is red.

It is also possible to control the formatting of a cell depending on the value of a different cell:

image

Conditional formatting not only makes it easy to flag serious error conditions (reportable variances in this case) but also enables more pro-active flagging. So, in this example a red cell indicates that variance reporting is required; yellow indicates an account that should be reviewed and monitored; green indicates that no action is needed for the line item at this time. By yellow flagging accounts that may be on their way to a serious variance you can be pro-active and start looking for ways and means to prevent the item from sliding out of control.

Welcome to the 21st Century

Excel 2007 introduced some major enhancements to Conditional Formatting. Now we don’t just have a choice of graphics to convey information about our data even more effectively.

image

Here the conditional formatting in column G indicates the relative value of each variance by the size of the bar. In column H the traffic light icons indicate whether a report is required for the variance, whether the account should be watched more closely, or whether current results for the account are within satisfactory limits.

The possibilities of Conditional Formatting, while not endless, are extensive. Whether your worksheet is strictly a working document designed to help you manage you job responsibilities more effectively, or whether your worksheet is part of a presentation to your boss or others, Conditional Formatting is well worth exploring.