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

Archive for the ‘Excel Formatting’ Category

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.

Reveal Hidden Column A or Row 1

Hiding rows and columns can be very helpful in keeping worksheet users focused on important details or to keep them from straying into areas that you don’t want them to see. However, if you happen to hide either row 1 or column A, unhiding them can be a bit tricky.

‘Hidden’ is a formatting property of Excel rows and columns. As with all formatting you have to select the row(s) or column(s) that you want to hide or unhide and then apply the format.

If you have hidden column D, for example, you can select columns C through E, either by dragging on the column header or by clicking the column C header and shift clicking the column E header. Column D will be included in the selection so when you unhide, column D will be unhidden.

However, how do you select a column that is hidden when you can only see columns to its right? Well, you could select all columns by clicking the Select All button (that’s the little square at the top left corner, just above the first visible row and to the left of the first visible row. Then, when you unhide you will unhide all hidden columns (or rows depending on which you choose to unhide).

That’s all well and good if you don’t mind having to re-hide some rows or columns that were revealed in the process but you can select a column simply by selecting a cell within that column.

Wait, you say, if I can’t see the column, how do I select a cell in that column? That’s where the name box or the GoTo (<ctrl>G or <F5>) command can help you out. The video shows how to use the name box to select a cell in column A, then unhide the column using formatting and avoid the unwanted side-effect of unhiding other hidden columns. The video shows the technique in Excel 2007 but you can use the Format/Columns (or Rows) menu in earlier Excel versions to go to (select) any location on the worksheet.

There is yet another way to reveal a hidden left column or top row but it requires good mousing skills. Keep in mind that hiding a column or row simply sets its width (or height in the case of rows) to zero. If you hover your mouse over the left boundary of the leftmost visible column  in the column header, you can use the re-sizing handle to increase the width of the first hidden column to the left. For rows, use the top boundary of the topmost visible row.