Microsoft Office Tutorials and References

In Depth Information

**Emphasizing Top Values in Charts**

Figure 9-24:
Entering #,##0;; as the custom format for a data label renders all zeros in that data series hidden.

When you go back to your chart, you see that the rogue zeros are now hidden and your chart is ready

for colors, labels, and other formatting you want to apply.

You can apply the same technique to highlight the bottom five values in your data set. The only

difference is that instead of using the LARGE function, you use the SMALL function. Whereas the LARGE

function returns the largest
nth value from a range, the SMALL function returns the smallest
nth value.

Figure 9-25 illustrates the formulas you use to apply the same technique outlined here for the

bottom five values.

Figure 9-25:
Use the SMALL function to highlight the bottom values in a chart.

The formula for the first column (F4) checks to see if the value in cell C22 is greater than the number

returned by the SMALL formula (the fifth smallest value). If it is, the value in cell C22 is returned.

Otherwise, NA is used. The formula for the second column works in the same way, except the IF

statement is reversed: If the value in cell C22 is greater than the number returned by the SMALL formula,

then NA is used; otherwise, the value is returned.