Dynamic Excel Graphs

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:

No alt text provided for this image


No alt text provided for this image

(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.

No alt text provided for this image

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.

No alt text provided for this image

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.

No alt text provided for this image

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 :)

No alt text provided for this image

Thanks! Happy Learning!

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

要查看或添加评论,请登录

Aakash Chawla的更多文章

  • MS Excel- Dynamic Drop-down list

    MS Excel- Dynamic Drop-down list

    Have you ever wondered that while filling a registration form when we select State, in the next field all the cities or…

    3 条评论
  • Solver Analysis in Excel

    Solver Analysis in Excel

    In this article, we will solve the typical supply chain problem with the help of excel. Consider yourself in place of a…

    1 条评论
  • Confusing excel formulae DGET or Vlookup? Which one to use and when?

    Confusing excel formulae DGET or Vlookup? Which one to use and when?

    DGET formula helps to extract the record based on the conditions specified. Now, it works similar to VLOOKUP formula…

    3 条评论
  • MS Excel Cell Modes

    MS Excel Cell Modes

    We all have used excel at some point in our life. Whether it is to perform a simple calculation or to do complex tasks.

    2 条评论

社区洞察