Peltier Technical Services, Inc.
- Custom Excel Solutions
Jon's Excel and Charting Pages
- Jon's Charts
- Tutorials
- Excel
- Site Index and Search
- RSS
|
Peltier Technical Services, Inc. Jon's Excel and Charting Pages |
|
Excel Column Combination Chart for Monthly Temperatures.
Excel Dashboards by ExcelUser.com - Instructional eBook and Ready-to-Use Workbooks.
I was perusing the Sunday paper recently, and came across a chart which summarized the past month's temperatures. It's an informative chart, which compares each day's minimum and maximum temperature with the normal range and the historical minimum and maximum temperatures for each date. I've seen similar charts many times, including this same design in the local paper, but this time the chart called out to me, and I had to build it myself. This is the story of my version of the chart.
The chart looks pretty complicated, but it can be broken down into smaller components:
The DataThe important daily temperature data is shown below. I wish I could tell you where to download it from, but I don't know; instead I manually digitized the chart in the newspaper.
In order to generate floating and stacked columns, we need to make adjustments to the data. The new data for the Record High/Low and Normal Range series must include a hidden series on which the others float (using the Record Low value), a series containing the difference between Normal Low and Record Low, a series containing the Normal Range (the difference between Normal High and Normal Low), and a series containing the difference between Record High and Normal High. The new data for the Daily Range needs a hidden series (the Daily Low) and a series showing the Daily Range (the difference between Daily High and Daily Low). The circles around the month's High and Low require two more series that only have plottable data for the actual high and low data points. The transformed data is shown here:
The formulas required to construct this range are summarized below. The formulas in H5:N5 are filled down to H34:N34.
The formulas in M4 and N4 concatenate the label and value; these will become the series names, and the series will be labeled using the Series Name option. The formulas in columns M and N below these labels assure that only the maximum and minimum will appear in the chart; NA() produces an ugly #N/A error in the sheet, but a Line or XY chart ignores this when plotting points. Finally, we need special data for the series which will be used for the labels along the right edge of the chart. The range is shown below, along with the formulas which define these values.
The +0.5 in P5:P7 moves the XY points from on top of the bars for the last of the month to the right edge of the plot area. I didn't add this amount to P12, so the "Daily Range" label would be slightly left of the others. These cells display identical dates, but if a date-time format were used, P5:P7 would display 30-Apr-06 12:00 PM while P12 would display 30-Apr-06 12:00 AM. The lookup formulas in column Q are more intricate than necessary (as are those in column P), but they allow rapid scaling up if the data were converted to a dynamic range. They determine the latest value of each of the relevant series, for proper vertical alignment of the labels. Making the ChartThe first step is to make a stacked column chart from the historical data. Select A4:A34 (Date), then hold Ctrl while selecting H4:K34 (Bottom, Below, Normal, and Above). Start the chart wizard, and create a Stacked Column chart. (Don't make a Stacked 100% Column chart.)
The chart has my usual plot area formatting: black border, white fill, and light gray gridlines. Make the Bottom series invisible: double click on the series, and on the Patterns tab of the Format Series dialog, choose None for Area and for Border. Double click on the Below series and choose light gray for Area and None for Border; on the Options tab, reduce the Gap Width to 50 to widen the columns. Format the Above series the same, by selecting it and pressing the F4 function key (the shortcut for Repeat Last Action). Format the Normal series with a medium gray for Area and None for Border. This is starting to look like a chart.
Other adjustments at this point included deleting the legend, removing the axis tick marks, and formatting the X axis major spacing to 7 days. Now the Daily High and Low data is added to the chart. Select F4:F34 (Daily Low), hold Ctrl while selecting L4:L34 (Range), and copy (Ctrl+C or Copy from the Edit menu). Select the chart, and choose Paste Special from the Edit menu. Choose New Series, Values in Columns, and Series Names in First Row.
Move these two added series to the secondary axis. Double click one of the series, and on the Axis tab, select Secondary. Select the other series, and press the F4 function key (the shortcut for Repeat Last Action).
The two series converted from stacked to clustered when moved to the secondary axis. Select one of the series, and using Chart Type on the Chart menu, change its chart type to Stacked Column.
Now format these series as a floating column. Make the Daily Low series invisible: double click on the series, and on the Patterns tab of the Format Series dialog, choose None for Area and for Border. Double click on the Range series and choose an appropriate color (I used orange) for Area and None for Border; on the Options tab, increase the Gap Width to 250 to shrink the columns.
You don't need the Y axis on both sides of the chart, especially if they have different default scales. Remove the secondary Y axis: select Chart Options from the Chart menu, click on the Axes tab, and uncheck the Secondary Y Axis box. Now all series use the primary axis for their scaling, but they retain their separation between primary and secondary axis groups. Add the series which will indicate the month's minimum and maximum temperatures. Copy M4:N34 (Monthly Low and Monthly High), select the chart, and choose Paste Special from the Edit menu. Choose New Series, Values in Columns, and Series Names in First Row.
These series were added as more stacked columns. Select one of these series, and using Chart Type on the Chart menu, change its chart type to Line. Select the other series and press the F4 function key. Double click one series; on the Patterns tab of the Format Series dialog, select None for Line and a large (size 7) circle with black (for example) as its Foreground and No Color for its Background; on the Data Labels tab, check the Series Name option. Select the other line series and press the F4 function key. Double click on the Monthly High data label, and on the Alignment tab, choose Above for Label Position. Double click on the Monthly Low data label, and on the Alignment tab, choose Below for Label Position.
Add the series that will locate the descriptive labels. Copy P4:Q7 (Record, highlighted in blue), select the chart, and choose Paste Special from the Edit menu. Choose New Series, Values in Columns, Series Names in First Row, and Categories (X Labels) in First Column. Copy P11:Q12 (Daily Range, highlighted in green), select the chart, and choose Paste Special from the Edit menu. Choose New Series, Values in Columns, Series Names in First Row, and Categories (X Labels) in First Column. Since the last series was converted to a line series, these series are added as line series; Record is shown in blue and Daily Range in green.
To align these series along the right edge of the chart, they need to be converted to XY series. Select one of these series, and using Chart Type on the Chart menu, change its chart type to XY (Scatter). Select the other series and press the F4 function key.
Add labels to the series, then make the points invisible. Select the Daily Range series, and on the Data Labels tab, select the Series Name option; on the Patterns tab, select None for Line and Markers. For the Record series, there are three choices:
I've used a gray font color so the labels are more clearly associated with the historical series in the chart.
A Line Chart AlternativeWhile building the stacked column chart above, I realized that the same functional parts of the chart could be built using Line Chart features, such as High-Low Lines and Up-Down Bars. An OHLC Stock Chart for example is simply a line chart with four series: Open, High, Low, and Close. Given a set of Line series in a chart, Excel allows you to specify Hi-Low Lines, which connect the highest and lowest values at a given category value with a line; and Up-Down Bars, which connect the first and last values (by series order) at a given category value with a bar, which is colored differently for increasing and decreasing values. The advantage to the Line Chart/Stock Chart approach is that you don't need to construct chart data ranges with columns of formulas. The disadvantage is that the formatting options are less flexible. This line chart plots the Normal Low, Record Low, Record High, and Normal High series, in this order (a different column order than our original data at the start of this article). The Up-Down bars cannot be made any wider than this, whereas column width can be adjusted through a series' Gap Width, as demonstrated in the Column Chart version. The Hi-Low Lines are made thicker by applying the widest available line thickness. All series are formatted with no markers and no lines.
The Daily High and Low have been added to this chart, both series moved to the secondary axis, and High-Low Lines applied, using the medium line weight.
The custom markers for the monthly high and low values, and the labels for the right edge of the chart, were applied in the same manner to the line chart as to the column chart.
Column Combination Chart for Monthly Temperatures is an alternative version of this column chart that uses more formulas and more data series to display positive and negative temperature data.
Area Combination Chart for Monthly Temperatures is an alternative version of this chart that uses areas rather than columns to display the temperature data (this chart works with positive and negative data). | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Peltier Technical Services, Inc. Jon's Excel and Charting Pages Peltier Technical Services, Inc., Copyright © 2008. All rights reserved. |
Waterfall Chart
Box and Whisker
