For a video demonstration of this tip, please visit
When using think-cell data links in Office 2007, the charts do not update when I change the cell background color or other cell formats in Excel.
Notice, however, that the zero values are effectively hidden from the chart because they are, in fact, zero values; thus, the chart gives the impression of applying conditional formatting to maximum and minimum values in column B.
Conditional formatting is also a tremendous mechanism for doing the same.
Combining these two tools by tricking Excel into doing something it technically cannot do, allows you to create reports that clearly and effectively communicate your intended message.
Finally, in cell E3 (and the remainder of the cells extending through cell E15), the formula below was used to enter all values other than minimum and maximum values.
=(B3-C3-D3) Once you have arranged the data, as shown in Figure 2, a simple stacked cylinder chart, similar to the one shown in Figure 3 can easily be created.
In a pivot table with a simple layout, you can select a group of cells, and apply a conditional formatting rule.
In this example, the Date field is in the Rows area, Territory is in the Columns area and Sales Amount is in the Values area.
If you change the pivot table layout, or add new records to the source data, the rule may be applied to the wrong cells, or might not include all the new data.
Follow these steps to add new data, and see what happens to the formatted cells in the pivot table.
Conditional formatting is a great tool for automatically formatting data in Excel, based on the conditions present in a cell(s).
For example, we can use conditional formatting to highlight, automatically, all unfavorable budget variances in red and all favorable budget variances in blue.
Consider the data presented in Figure 1; sales data is presented for each of thirteen salespersons.