Conditional formatting in Excel can be used to present data in a way that improves the understanding of the meaning of the numbers. In the examples below, we’ll turn good numbers blue and bad numbers red, and talk about the impact of that presentation.
The worksheet is named “MLB – Whos Hot Whos Not.xls”
·
Cell and Range References (Relative and Absolute)
·
Named Ranges
·
Copying Formulas
·
Copying Formats
Conditional Formatting - Based on a constant, Based on a relative cell reference, and Based on an absolute cell reference.
According to the Excel97 Help,
A reference identifies a cell or a range of cells on a worksheet and tells Microsoft Excel where to look for the values or data you want to use in a formula. With references, you can use data contained in different parts of a worksheet in one formula or use the value from one cell in several formulas. You can also refer to cells on other sheets in the same workbook, to other workbooks, and to data in other programs. References to cells in other workbooks are called external references. References to data in other programs are called remote references.
By default, Microsoft Excel uses the A1 reference style, which labels columns with letters (A through IV, for a total of 256 columns) and labels rows with numbers (1 through 65536). To refer to a cell, enter the column letter followed by the row number. For example, D50 refers to the cell at the intersection of column D and row 50. To refer to a range of cells, enter the reference for the cell in the upper-left corner of the range, a colon (:), and then the reference to the cell in the lower-right corner of the range. The following are examples of references.
To refer to Use
The cell in column A and row 10 A10
The range of cells in column A and rows 10 through 20 A10:A20
The range of cells in row 15 and columns B through E B15:E15
All cells in row 5 5:5
All cells in rows 5 through 10 5:10
All cells in column H H:H
All cells in columns H through J H:J
From the Excel97 Help:
When you create a formula, references to cells or ranges are usually based upon their position relative to the cell that contains the formula. For example, if cell B6 contains the formula =A5; Microsoft Excel finds the value one cell above and one cell to the left of B6. This is known as a relative referencing.
When you copy a formula that uses relative references, the references in the pasted formula update and refer to different cells relative to the position of the formula. For example, if the formula in cell B6 is copied to cell B7, then the formula in cell B7 will change to =A6, which refers to the cell that is one cell above and to the left of cell B7.
If you don't want references to change when you copy a formula to a different cell, use an absolute reference. For example, if your formula multiples cell A5 with cell C1 (=A5*C1) and you copy the formula to another cell, both references will change. You can create an absolute reference to cell C1 by placing a dollar sign ($) before the parts of the reference that do not change. To create an absolute reference to cell C1, for example, add dollar signs to the formula as follows:
=A5*$C$1
From Excel97 Help: A descriptive name in a formula can make it easier to understand the purpose of the formula. For example, the formula =SUM(FirstQuarterSales) might be easier to identify than =SUM(Sales!C20:C30). In this example, the name FirstQuarterSales represents the range C20:C30 on the worksheet named Sales.
When you copy a formula, absolute cell references do not change; relative cell references will change.
The easiest way to copy cell formats is to use the Format Painter. You can use this two ways.
1. Select the cell or range containing the format you wish to copy. Click on the format painter icon. Then select the target area. The copying will take place immediately.
2. If you wish to repeat the process, double-click the format painter icon, and then click it again a single time when you’re done.
3. You can also use Edit|Paste Special|Formats if you like to be old fashioned.
Select the cells you want to format.
On the Format menu, click Conditional Formatting.
To use values in the selected cells as the formatting criteria, click Cell Value Is, select the comparison phrase and then type a value in the appropriate box. You can enter a constant value or a formula; you must include an equal sign (=) before the formula.
Click Format.
Select the font style, font color, underlining, borders, shading, or patterns you want to apply.
Microsoft Excel applies the selected formats only if the cell value meets the condition or if the formula returns a value of TRUE.
You can specify up to three conditions.
If none of the specified conditions are true, the cells keep their existing formats. You can use the existing formats to identify a fourth condition.
You can compare the cell value to a constant. In the example worksheet, cell B13 will be blue if greater than or equal to 7, and red if less than or equal to three. If both conditions fail, the cell will be black.
You can compare the value of the cell to the value in another cell. In the example, cell D13 will be black, blue, or red based on its relative size compared to cell E13.
If you change the “cell value is” to “formula is” you can use any formula that you like. In the example, cells H13 and I13 are colored blue or red based on the comparison of their ratio to that of a specific cell, $J$2. Notes: You can use a Named Range in these formulas, and it might make them easier to read. Also, you can use absolute and relative cell addresses, or Named Ranges whether you compare to the cell value or use “formula is.”
The process of using conditional formatting in Excel requires prior knowledge of cell reference and format copying. The following is an example where we focus on the message, not the medium.
We will use the American League baseball results for May, 2000 to exemplify some of these methods.
First, look down to rows 2 thru 4 where the performance of the divisions is summarized. For the East, Central, and West Divisions, you can see the won-lost results for the last 10 games, for the season, and for the season broken down by home and road. The road won-lost percentage is calculated after that.
By arbitrary definition, winning 7 or more of ten games is hot, winning 3 or less is not. Normally, the worst teams win 2 games out of 5, and the best teams win 3 games out of 5. The long baseball season is normally a six month campaign among evenly matched teams, where on any given day the worst team can beat the best team 13-1. Success is defined in small increments. In baseball, the gap between best and worst is summed in your performance every fifth day, and the gap between the average teams and the best is summed in your performance every tenth day. This is a sport with a great premium on consistency. If we graphed that performance, there would not be a great difference in the average weekly performance of the teams. In our example, we make the teams that win 7 or more games blue, and the teams that win 3 or less red, and the teams that are performing within average ranges are black.
Using this yardstick, you can see at a glance who’s hot and who’s not. The Blue Jays and White Sox are hot, and the Royals, Tigers, and Rangers are not.
The formulas for the formats for losses are just reversed.
For the season, formatting is based on the same basic measure – teams winning more than 60% of the time are blue, teams losing more than 60% of the time are in red. This calculation isn’t as simple – instead of comparing the cell value to a constant, we compare it to the value of another cell AND a constant. In this case, wins are blue if they are greater than losses*1.5, and red if they are less than losses/1.5. The 1.5 in the formula could by typed in an assumptions cell and referred to by absolute reference, and this would be a good thing to do if you wanted to move the numbers around and instantly see the difference.
While home records are usually better than road records, the same formula has been used to calculate home performance as for the season.
Road wins are harder to come by. Normally, only the best teams win on the road. As a result, the formulas for road performance are different from the formulas for the season. If you win more than you lose on the road, you’re in the blue numbers. If you win less than one out of three road games, you’ll be in the red.
In the example, I’ve used a different approach for the AL East, using blue for above average win-loss percentage on the road within the division, and red for below. There would only be black numbers if a team exactly hit the average. Here’s an example of use of a Named Range in a formula.
Using this method, you can see a few things clearly. First, using shading instead of using font colors alone really brings out differences. Second, you can see a mixture of good and bad performance in some cases. Identifying the areas where performance improvement is needed is a key portion of any Management By Exception approach. So the White Sox are hot and the Tigers are not. The twins are average in every way, and the Royals are winning at home and getting killed on the road.
Another point worth mentioning is that one month is not long enough to draw projections for a season in baseball. It’s a good way to measure monthly performance, but it’s not wise to extrapolate these figures over a season without more information. As a rule, the first 50 games tell you what kind of team you have, and then you address your weaknesses with the next 50. The last 50 are the sprint to the wire. Baseball history is full of stories of teams that performed at unexceptional levels until August, and then buried the division. The old Royals teams were a classic example of that.
You might be able to think of ways that using 4 colors will help you communicate information hidden in your data. Conditional formatting is just one of those ways – and anyone who has excel can use it.
Web page notes. Make a link to download the worksheet, and pu the preliminary text and the final text in separate pages. Finally, put a represenation of the worksheet in HTML format up there, too.