Some of the really cool charts that we can build in Excel involve the trick of combining multiple chart types together to make them happen. In this article, we’ll build one of those; a temperature chart that not only shows the forecasted high and low temperatures, but also the season highs and lows. The beauty of this chart is that it provides a lot of information, some of which essentially fades into the background until you really need it.

The steps below are for completing this chart in Excel 2007 and 2010, and will end up with a completed chart looking like this:

The data

We have forecasted high and low, seasonal minimum and maximum data all contained in cells A1:F6 as shown below:

Creating The Base Chart

To start building the chart:

• Highlight the entire data table
• Go to the Insert tab --> Area Chart and select the Stacked Area Chart (2^nd one in the top row)

You’ll be greeted by a very ugly chart on your worksheet that looks something like this:

Make sure your chart is selected and then:

• Go to the Chart Tools tab --> Design --> Select Data

This opens a window that allows us to change the defaults data series a bit. The data series are listed from top to bottom in the window, which is the order they are plotted on the chart. (So the first series is plotted at the bottom, then the next is added on top of it.) Let’s change the order of the series to make sure the Seasonal Minimum is plotted first, and the Seasonal Range is plotted second:

• First find the Seasonal Minimum in the list, select it and click the up arrow above it twice

With Seasonal Minimum now first in the list, it will be the first series plotted on the chart, and will therefore be closest to zero on the axis. Next we need to make sure the Seasonal Range is plotted second:

• Select the Seasonal Range and move it up the same way so it is under the Seasonal Minimum in the list

If you can see the chart behind the Data Source window, you’ll see that your chart now looks like this:

There’s a couple of important things to notice here. The first is that the Seasonal Minimum and Seasonal Range are correctly plotted. The second is that they stacked, with the Seasonal Range being added to the Seasonal Minimum. The effect of this is that the top of the Seasonal Range line is the Seasonal Maximum… so we don’t actually need that series on the chart.

• Select Seasonal Maximum in the Date Source Window
• Click “Remove” (beside the arrows)
• Click OK

And the chart now looks as follows:

Combining Chart Types

Now we get to the part that makes this chart shine. We’re going to change the top two series so that they are line charts plotted on top of the area charts. And here’s how:

• Click on the Forecasted High section of the area chart to select it
• Right click and choose Change Series Chart Type
• Choose Line Chart (the first one in the top left)
• Click OK

Cool, it’s converted to a line! Now, do the exact same thing for the Forecasted Low series. Once done, your chart should look as follows:

Getting better… At least the series are starting to look a bit more like we want. Now we need to format it.

• Right click on the Seasonal Minimum grey band at the bottom of the chart
• Change the fill colour to white

Excellent, the series seems to disappear. Now, let’s make its legend entry disappear too.

• Click on the legend once to select it
• Click on Seasonal Minimum to select that entry only
• Press delete

Notice that the white series stays, but the legend entry is gone. Now, let’s soften the Seasonal Range background colour, and choose some better colours for the forecasted temperature series as well.

• Select the dark blue Seasonal Range area portion of the chart and right click on it
• Change the fill colour to a light green
• Select the orange Forecasted Low line and right click it
• Right Click on the line, click the dropdown arrow beside Shape Outline and choose a dark blue color (as shown below):

• Do the same with the Forecasted High line changing it to red

It’s starting to actually look similar to the picture that we were shooting for!

Cleaning Up Chart Presentation

Despite the progress we’ve made, this could still be much better. Let’s go the rest of the way, starting with getting rid of the unnecessary gridlines. To do that:

• Click on the chart once. (This selects the entire chart)
• Then click on one of the grid lines

You should see the selection circles move to the ends of the gridlines only:

Press Delete

• Next we’ll deal with the axis:

Select the dates in the horizontal axis, right click them and choose Format Axis

• On the Axis Options tab, change Position Axis to “On Tick Marks” Instead of Between Tick Marks
• Click the Number Tab on the left
• Un-check the Linked to Source checkbox
• In the Format Code box type in m/dd
• Click add
• Click close

The chart is start to get close to finished here… this is looking pretty good!

There’s still some things we can do to make this better though… we can move the legend to the bottom, and add some context:

• Select the chart
• Go to Chart Tools --> Layout tab --> Legend --> Show Legend at Bottom
• Go to Chart Tools --> Axis titles --> Primary Vertical Axis Title --> Rotated Title
• Click in the formula bar and type “Temperature (Celsius)”, then press Enter
• Go to Chart Tools --> Chart Title --> Above Chart
• Click in the formula bar and type “Temperature Forecast”

The last thing you need to do to make the finished chart look like that at the beginning of the article is:

• Right click the Forecasted High --> Format Data Series --> Line Style --> Smoothed Line
• Do the same for the Forecasted Low series

And there you have it, the completed chart!

You can download both the starting and completed versions of the file from the link below: