Apply Conditional Formatting in Column Chart in Excel, highlight the max value




I am creating a column chart and it is dynamic based on selection of year, month, region and products. I want to highlight the maximum and minimum bar of column chart with green and red fill color respectively but it also be dynamic as per the selection, similar as conditional formatting. How do I conditionally format the data series so that the max and min value changes colour?

Thanks, Mukesh



There is no direct feature available in excel to apply this functionality in column chart but it can be done by creating extra columns (one for maximum value and another one for minimum value) and playing with chart bar options. Let’s look at these steps by step:-

Step 1: Create two additional columns for Minimum and Maximum value of the given series

Step 2: Insert column Chart based on all three series against given categories:

Step 3: Select any bar then press Ctrl + 1 and make “Series Overlap” to 100%.

Step 4: Change the color of Maximum bar to green and minimum bar to red by using option Fill color

Here you have the final solution. Hope this helps!