How to Highlight Top 3 and Bottom 3 Bars in an Excel Chart

Bar charts are a standard choice for comparing datasets, but you can make your visualizations more impactful by automatically highlighting the top three and bottom three results.

Here is how to set it up.

Begin by entering your data into Excel—for example, a list of student names and their corresponding grades.

To make the chart dynamic, you need to create two helper columns. Label the first one "Top 3." In cell C2, enter the following formula:

=IF(B2>LARGE($B$2:$B$14,4),B2,NA())

This formula works by using LARGE($B$2:$B$14,4) to identify the fourth-highest score in the range. The dollar signs are used to lock the cell range. The IF statement then checks if the score in B2 is greater than that fourth-highest value. If it is, the score is displayed; otherwise, the formula returns #N/A. It is important to use the NA() function rather than an empty string ("") or a zero, as those values can cause formatting issues within the chart. Once the formula is entered, drag the fill handle at the bottom-right of cell C2 down to the end of your list.

Next, create a "Bottom 3" helper column. In cell D2, enter:

=IF(B2<SMALL($B$2:$B$14,4),B2,NA())

This follows the same logic: if the score is lower than the fourth-smallest value, it appears in the column. If not, the cell displays #N/A. Fill this formula down the rest of the column.

With your helper columns ready, you can now insert a clustered bar chart. Select all your data (columns A through D), go to the Insert tab, click the bar chart icon, and select Clustered Bar.

By default, Excel will generate a chart with three separate series.

To make the colors appear on the same bars, you must overlap them. Right-click any of the orange "Top 3" bars and select Format Data Series. In the settings pane, set the Series Overlap to 100%.

To change the color for the top performers, go to the Fill & Line tab and select green.

Repeat this process for the "Bottom 3" bars. Right-click the gray bars, navigate to Format Data Series > Fill & Line, and choose red.

To finish the chart, change the original blue bars to a neutral light gray. You can then add data labels, refine the chart title, and move the legend to a better position. Your chart is now set up so that anyone viewing it can instantly identify the highest and lowest performers.