Next time you want to make a chart to tell how your sales (defects, customer calls, page views, number of clicks, walk-ins etc.) are doing over a period of time, consider showing them in a min-max chart.
For eg. a min-max sales chart for the last 12 months tells average, minimum and maximum sales per each month. See below for an example:
Min Max Excel Chart - An Example of Monthly Sales Data
These are really easy to create and can tell more than simple sales are up story. The best part is you can make the min-max charts with ease.
1. Have your data ready
The first step of course is to have the data ready. It is not always you have the minimum, maximum sales details for a give month, so you may want to summarize the data before moving to the next step. For our example, let us revisit ACME products (trivia acme link for curious mice out there). The data looks like this:
2. Create an Area Chart
As you might have already guessed, these min-max charts are nothing but area charts in disguise.
So, select the tabular data and click on “insert > chart” and select area chart (just the simple area chart, not the stacked area chart)
3. Format the Chart to Get the Min-Max Effect
This is the last step. First you may want to adjust the data series order of the area chart to ensure that the areas are overlapped properly. See below:
To adjust the order, right click on any of the areas and select “format data series” option, then go to “series order” tab.
The only formatting necessary is filling the bottom most area with white color (the minimum part). But you can also remove the plot area background – the gray color and adjust the fonts. Also, you can adjust the colors of other 2 areas (average and maximum) and adjust the border line width of average to make it standout.
That is all, there are no further steps, so go ahead, create your own min-max chart and let the conversation begin.
Like this? Also try: Thermometer charts in Excel, Micro bar charts, Gantt charts with excel bar graphs
19 Responses to “Use min-max charts to show the spread of data – Charting Best Practice”
Here's another approach. Insert another row to calculate the range, that is (MAX-MIN). Plot MIN and (MAX-MIN) as stacked area series, and AVE as a line. Format MIN with no border and no fill to hide it. I also remove the border from the otherwise visible (MAX-MIN), but that's a preference.
[...] on charting: Hack together a thermometer chart, tell a story with min-max charts, 73 awesome chart templates, download and wow Tags: bubble charts, charting, clock, Excel [...]
I like the banded approach, but would like the ability to "cut-off" in mid-graph (like, I want to show data for Jan-Dec, but we're in July, so months Aug - Dec are blank). Is this possible? I tried using the NA() trick like on column charts, but no luck.
@AlexJ:
In Excel 2007 empty values or NA() do exactly what you want to do, so I suppose you are working with Excel 2003 or earlier.
For Excel 2003 you have to define names, assign OFFSET-formulas to them and use the names as the chart data source.
Jon Peltier provides an excellent description for this technique (original by Debra Dalgleish):
http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html
The example is on a column chart but you can use that for an area chart too.
@Robert
Yes, its Excel 2003.
But I think you mis-read my question. I'm familiar with, and use, the dynamic data ranges for charts regularly.
(I'd love to post a picture of what I'm looking for, but don't know how. Chandoo - can you help?)
I need a chart that has an X axis for all the months from Jan to Dec, but to show data for only, say, March to October. This works (using NA) for the linerepresenting the average value, but the area graph used for banding insists on going to zero, instead of interrupting.
@AlexJ
You can post an image by uploading it anywhere (flickr or photobucket) and post the link here in comments.
Let me understand your question: you want to show only partial data in the chart but the axis to continue? like this: http://chandoo.org/wp/wp-content/uploads/2008/08/min-max-charts-altered.png
if so, you can do that by adjusting "min" data for the months after october to some high value like 30 (not too high, a value slightly more than what have seen for all max values in the series), this will ensure that rest of the chart is in white color, see the image. You can do so for months from Jan to Mar as well.
But I am not sure I understood your question correctly. Post back with an image if you can...
Thanks, Chandoo. You got the basic idea.
This is what I'm looking for.
(I'll try your tip.)
Sorry: its
http://my.inbox.com/photos/?apj.jpcs
@AlexJ:
You are right: I did not understand that you want the x-axis to be shown until December, although there are only values until July.
The technique Jon Peltier described will lead to an x-axis that ends at the month your data is in (July). So - as far as I got your intention right - this does not help you.
But you have 2 options to manage that:
1. Change the the maximum of the x-axis from automatically to the fixed value December.
2. Add a Dummy series that is filled with 0 from January to December. It will not show in the area chart (since the values are 0) and it will force the x-axis to expand until december.
Does that help?
@AlexJ:
Sorry I didn't read the last 3 posts before posting my answer. My options will not help for the empty data at the beginning of the time line.
After looking at the image, I think I understand your problem now.
But Chandoo's idea seems to work, though you have kind of a triangle at the borders (when the white min-area walks down to the real values)...
Not sure I can get Chandoo's approach to work the way I want.
Maybe this is a job for PELTIERman!
Hi AlexJ:
you can do that by using formulas to adjust the min values for the months you dont want a value like 30. It works both ways. see this image: http://i287.photobucket.com/albums/ll133/pointy-haired-dilbert/min-max-alterantive1.png
@AlexJ: of course the triangle shape in the end / begin is annoying, but this is technically not a area chart, so it does not really mean something...
@AlexJ:
Maybe this could be a solution:
Let us assume – as shown in your picture – you have data from March to September.
1. Create 2 dummy series. One dummy series has the total maximum (seems to be 35 in your example) from January to March and from October to December. All other values are 0.The other dummy series has the total maximum 35 from January to February and from September to December. All other values are 0 again.
2. Add the 2 dummy series to your chart.
3. Change the chart type of the dummy series to a clustered column chart.
4. Format the two columns with white fill color, no border and set the gap width of the columns to 0.
The dummy columns with the white fill color will now hide the area chart coming up from and going down to the horizontal axis.
I tried that on my other computer with the Excel 2003 installation, so it should work for you as well.
Tkx Robert. Found a very similar approach myself and sent is on to Chandoo.
There are drawbacks - since these columns obscure parts of the plot area (as per design) using the chart grid is not possible. Luckily I wasn't planning on using the grid!
@ AlexJ
Thanks for your response.
I haven’t thought of that (because I am using gridlines very rarely). But if you would need them, there is another tweak to get the gridlines as well:
1. Make a copy your chart
2. Insert the gridlines on the second chart and format them the way you want to.
3. Format all data series of the second chart (the areas, the line and the dummy columns) with no fill color and no border.
4. Format the plot area and the chart area of the second chart with no fill color.
5. Position the chart exactly on top of your first chart.
Shortfalls:
1. The gridlines (second chart) are in front of the data series (first chart). This is unfamiliar because usually the areas, lines and columns are on top of the gridlines, but you might get around that by carefully choosing the colors (e.g. same color for ares and gridlines).
2. You need a second chart and it has to be positioned exactly on top of the first. To be honest, I am not a fan of this kind of technique, but in this case I don't know a better solution. And I have to admit: I used exactly this 2-charts-technique for the chromachron-fun-clock:
http://chandoo.org/wp/2008/08/19/show-time-excel-fun/
[...] Impressive Excel Charts you should try - Thermometer Charts, Min-Max Charts, Incell Tag [...]
[...] try: Min-max charts to tell the story better, Incell graphs with colors, hot looking thermometer [...]
how did you format the legend?