Emphasize Top Values in Charts for Your Excel Dashboards
Sometimes a chart is indeed the best way to display a set of data on your Excel dashboards and reports, but you still would like to call attention to the top values in that chart. In these cases, you can use a technique that actually highlights the top values in your charts.
That is to say, you can use Excel to figure out which values in your data series are in the top nth value and then apply special formatting to them. This figure illustrates an example in which the top five quarters are highlighted and given a label.
The secret to this technique is Excel’s obscure LARGE function. The LARGE function returns the nth largest number from a dataset. In other words, you tell it where to look and the number rank you want.
To find the largest number in the dataset, you enter the formula LARGE(Data_Range, 1). To find the fifth largest number in the dataset, use LARGE(Data_Range, 5). This figure illustrates how the LARGE function works.
The idea is fairly simple. In order to identify the top five values in a dataset, you first need to identify the fifth largest number (LARGE function to the rescue) and then test each value in the dataset to see if it’s bigger than the fifth largest number. Here’s what you do:
Build a chart feeder that consists of formulas that link back to your raw data. The feeder should have two columns: one to hold data that isn’t in the top five and one to hold data that is in the top five, as shown in this figure.
In the first row of the chart feeder, enter the formulas shown in the previous figure.
The formula for the first column (F4) checks to see if the value in cell C4 is less than the number returned by the LARGE formula (the fifth largest value). If it is, the value in cell C4 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 C4 is greater than or equal to the number returned by the formula, then the value is returned; otherwise, NA is used.
Copy the formulas down to fill the table.
Use the chart feeder table to plot the data into a stacked column chart.
You immediately see a chart that displays two data series: one for data points not in the top five, and one for data points in the top five, as shown in this figure.
Notice that the chart in this figure shows some rogue zeros. You can fix the chart so that the zeros don’t appear by performing the next few steps.
Right-click any of the data labels for the “not in top 5” series and choose Format Data Labels.
The Format Data Labels dialog box appears.
In this dialog box, expand the Numbers section and select Custom in the Category list.
Enter #,##0;; as the custom number format, as shown in this figure.
Click Add and then click Close.
When you go back to your chart, you see that the rogue zeroes 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 dataset. 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.
The following figure illustrates the formulas you use to apply the same technique outlined here for the bottom five values.
The formula for the first column (F22) 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.