|
This tutorial shows you how to highlight the min and max values on an XY chart using Excel's Index & Match functions. The download file Chart_min_max.xls includes the example.
1. Introduction
In our example, we have time series data, with date in column A and flow data in Column B. We have an XY trend chart that shows the trend, we want to highlight the min and max values. How do we do this?
| Date |
Flow |
| 1/1/2006 |
6.0 |
| 1/2/2006 |
11.0 |
| 1/3/2006 |
8.8 |
| 1/4/2006 |
8.9 |
| 1/5/2006 |
3.1 |
| 1/6/2006 |
4.2 |
| 1/7/2006 |
3.3 |
| 1/8/2006 |
3.9 |
| 1/9/2006 |
6.5 |
| 1/10/2006 |
6.0 |
2. Setting Up New Min & Max Data Series - To chart them, we need to calculate the min and max values of our data and the corresponding dates. One way to do this is to add two additional data series to our data set that include min and max data. The new min and max series cells include formulas:
(Min series) =IF($B2=Min($B$2:$B$22),$B2,NA()) (Max series) =IF($B2=MAX($B$2:$B$22),$B2,NA())
We can then chart these series. Since Excel does not plot #N/A values, only the actual min and max values will be charted. We can then format the min and max series with color, shape, size to highlight them.
This method is perfectly valid, however, we have added two series to our data set, mixing our "hard" data with chart format data. If we were to do this to a number of variables, our data set begins to grow in number of variables and could become unwieldy.
Is there a simpler and cleaner way to add min and max values to our chart?
3. Setting Up Min and Max Data Points - Why not add single point chart series to our charts to represent min and max values instead of the multiple data point series? We can create a small table of the min and max values like the one below. We can locate this table anyplace in our workbook.
The min and max formulas are straight forward. The Index and Match formulas may not be as familiar to some Users. I have found index and match to be very powerful tools. If you are not familiar with using this combination of Excel function, here are two links that will give you all the information you need to start using this powerful data query capability.
http://www.contextures.com/xlFunctions03.html
http://www.mrexcel.com/tip021.shtml
4. Adding Min and Max Series to Our Chart - Now that we have calculated our min/max values, we can add them to our chart.
To add the min data point series to our chart:
1. Select cells: B4:C5
2. Place cursor on outside border of the data selection, a small cross hair type cursor will appear.
3. Press left mouse key, drag cursor to the chart; cursor will change to a +
4. Release left mouse key. Paste Special Menu will automatically appear (See picture copy below)
5. Press OK; the min data series will be added. You can now go to the Source Data menu for the chart and name the min data series.
6. To format the min data point, we can select it from the chart menu and make it any color, shape and size that we want.
7. Finished Chart
Jon Peltier has a good tip on how to use an arrow to highlight the min/max points.
http://peltiertech.com/Excel/Charts/PointAtPoints.html
|