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.