Highlight Max And Min Values On A Column Chart - KING OF EXCEL

KING OF EXCEL

KINGEXCEL.INFO ( KING OF EXCEL ) Welcome KINGEXCEL.INFO - Nothing Is Unable ... About Excel Tricks, Learning VBA Programming, Dedicated Software, Accounting, Living Skills ...

Saturday, April 25, 2020

Highlight Max And Min Values On A Column Chart

Highlight Max And Min Values On A Column Chart

When using column charts to compare values, you may want to highlight the maximum and minimum values on the chart. By highlighting these columns it removes any confusion when trying to view the top and bottom values.
Highlighted max and min values

Finding the Max and Min Values

To show the max and min values on a column chart, we will first need to identify the max and min values of our range. These values will then be used as a second data series when we create the column chart.
We will use the following formula to check the values in our range, and return the value if it is the maximum or minimum. Otherwise the NA() function is used to return the #N/A error. We want this because the chart will not plot these error values.
=IF(OR(B4=MAX($B$4:$B$15),B3=MIN($B$4:$B$15)),B4,NA())
The range of cells we will use to create the column chart will look like below.
Data to use in column chart
The formula uses the IF and OR functions. The OR function enables us to test if the value is either the maximum or minimum figure. The IF function then takes the required action, which is to either display the value or return #N/A.

Creating the Column Chart with Highlighted Max and Min Values

Select the range of cells to chart. In this example, that is A3:C14. Then click Insert > Column Chart and select the 2D Clustered Column (This is the first chart in the sub-type list).
Insert a 2D Column Chart
The chart will appear like in the image below. The two data series are shown as separate columns.
Column chart with two data series
Click on one of the columns in the chart. Click the Format tab on the Ribbon and the Format Selection button. Enter 100% in the Series Overlap field.
Overlap the data series
The two data series are now overlapped giving the appearance of one data series with the max and min values highlighted.
Showing the max and min values on a chart
You can apply further formatting to adapt the chart to your own needs.

Watch the Video




#evba #etipfree #eama #kingexcel
📤How to Download ebooks: https://www.evba.info/2020/02/instructions-for-downloading-documents.html?m=1

No comments:

Post a Comment