Today we are going to discuss about highlighting the max and min values in column chart.
This process will include some data preparation techniques and chart customization.
Before diving into the topic, let me show you the glimpse of our requirement.
Left side picture shows, month wise product sales data.
At right side, data preparation table in the range I3:J11, and then the chart based on the same range. In the chart, you can see that, column of max value highlighted in green color and column of min value highlighted in red color. This is fully dynamic, if we change the drop down in J3 cell and select Mar month, chart will reflect the march month values and the max and min values will change based on that.
Let me start this from scratch,
Copy the data from B3:B11 and paste in I3:I11, drop down provided in J3 cell to select the months from Jan to Apr. Below formula used in J4 cell to extract the sales from the source data, based on the selection in drop down.
=INDEX($C$4:$F$11,ROWS($J$4:J4),MATCH(J$3,$C$3:$F$3,0))
Select anywhere inside the range I3:J11 and press Alt + F1, to insert the default chart which is column chart.
We are going to show only one month and so we can remove legend.
You can delete grid lines by clicking on that and press delete. Click any one of the column and press Ctrl + 1 to open the chart format pane. In series options, change the gap width into 100%.
You can change the color of the column based on your preference.
Use below formula in J1 cell to get the dynamic chart title,
=J3&" Month Sales"
Now, select title in the chart, and in the formula bar press = and then refer the J1 cell.
You can change the font color of J1 cell into white, if you want to hide.
Click on the + icon in the top right corner of the chart and select data labels option.
Now, we are going to prepare the data for Max and Min value, for that in K4 cell use below formula,
=IF(J4=MAX($J$4:$J$11),J4,NA()) and the drag all the way down till K11
In L4 cell use below formula,
=IF(J4=MIN($J$4:$J$11),J4,NA()) and then drag all the way down till L11.
Charts in excel ignores the error values and so this will not get plotted. If we use the data from K3:K11, then only 99 will be plotted in chart and all other values are omitted. Copy the data from K3:L11, then select the chart and paste. Select any of the columns in chart and press Ctrl + 1, change the series overlap option to 100% and press enter.
Now, select the chart, in the format chart area pane, click on the drop down on chart options as shown below.
click on the series "Max" and change the fill color into green and click on the series "Min" and change the fill color into red. Select vertical value axis and press delete. Select the range K3:L11 and change the font color to white.
Now we highlighted the max and min values in column chart, same can be done in line charts, that we will see in further post.
Comments