Dynamic Excel Graphs

In this article, let us see how to create a dynamic clustered column graph where the maximum bar value is of different color with respect to others and it changes automatically once you change the data.

Let's take an easy example to understand this better.

Suppose we want to create a column graph for the sales per month. And also to highlight the maximum value with a different color. Just like this one:

(where X- axis shows months and Y-axis show fictitious sales figure)

Now there can be a manual way to select that maximum bar in the chart and select different color. But let's see how to make it dynamic because frankly speaking, it's a boring job to do it manually :p

Now, in order to that, let's first work on the data range. Using IF & Maximum function, let's first find the maximum value in column C.

Use this function '=IF(B2=MAX($B$2:$B$7),B2,"")'.

Here, it means that if a cell in column B has maximum value, then display that value in the adjacent cell in column C or return blank.

Drag this formula for all other cells in column C.

Once, we drag the formula in all other cells in column C, we get the maximum value in cell C4.

Now, let us insert the clustered column graph. Notice that, I am inserting the graph using the Column A and B only. The reason you will find it shortly.

Now, right click on the chart, go to select data. Click on Add, in Series Name- select cell C1, in Series Values - select cells C2 to C7. Click OK.

Now, you can see two column graphs, on the same chart.

Select Maximum series on the chart- right click and select Format Data Series.

Move 'Series Overlap' pointer to 100% and there you go!

Try changing the value in the cells, it will always show the maximum values with a different color and that too dynamically :)

Thanks! Happy Learning!

P.S. Like, comment and Share, if you find this article useful.