How to add conditional formatting to an Excel chart

Data visualization is one of the most effective ways to make data clear, impactful, and actionable—”it’s science,” as the kids like to say. And one of the most powerful tools in this process is color.

In this post, we’ll dive into how to apply conditional formatting to elements of an Excel chart. Our example compares the land areas of New York City’s boroughs. By setting a threshold, we’ll highlight boroughs above a certain size with a distinct color, making those stand out. You can follow along with the exercise file provided below.

To get started, add a column to the data source table. You’ll notice that the bars in the chart automatically start to narrow—Excel anticipates that you’ll want to include this new variable and adjusts the chart to make room. At the moment, everything is set to zero, so you won’t see any new bars just yet.

Next, let’s populate the threshold column with a conditional formula: is the land area greater than the threshold? You’ll notice I used a named range in cell B1 to make the formula more readable and consistent with the structured table references.

=IF([@[Land Area (sq mi)]] > threshold, [@[Land Area (sq mi)]], "")

Our next step is to somehow “merge” the conditional formatted bar with the original bar and we can do that of sorts by making these two bars entirely overlap each other. To do that, right-click on any of the bars in the bar chart and seelct “Format Data Series” at the end of the right-click menu.

A Format Data Series menu will pop up to the right of your worksheet. Head down toward the bottom and “Series Overlap,” I will set that to 100% so that the conditional column is completely overlapping the original. This way all boroughs over the threshold are now highlighted.

Try setting the threshold to a new number, and you’ll see the colors automatically adjust to reflect the change.

In this post, you’ve learned how to apply conditional formatting to an Excel chart, specifically a bar chart. You can use this technique in other chart types too, such as scatterplots or line charts, to highlight outliers or key data points.

What questions do you have about conditional formatting in Excel charts or using color and other attributes to enhance your charts? Let me know in the comments.