Use min-max charts to show the spread of data – Charting Best Practice

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