|
|
Excel Dashboards
Books at Amazon.com
|
Excel Column Combination Chart for Monthly Temperatures.
In Column Combination Chart for Monthly Temperatures, I showed how to create a stacked column chart which summarized the past month's temperatures. It's an informative chart, and compares each day's minimum and maximum temperature with the normal range and the historical minimum and maximum temperatures for each date. Jack Jordan sent along a data set that didn't work using the protocol in that web page. Jack's temperatures were lower and stated in Celcius, so the data ranges that feed the chart had to be changed following the procedures in Stacked Column Charts that Cross the X Axis.
The chart looks pretty complicated, but it can be broken down into smaller components:
The DataThe important daily temperature data supplied by Jack is shown below.
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:R5 are filled down to H35:R35.
The formulas in Q4 and R4 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 Q and R 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 lookup formulas in column U are more intricate than necessary (as are those in column T), 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. The uncorrected label positions in U8 and U14 in the original version of this page may cause the "Normal Range" and "Daily Range" labels to overlap. The complicated formulas in U7 and U13 and the factor in U20 allow for moving of these labels if they would overlap on the chart. Making the ChartThe first step is to make a stacked column chart from the historical data. Select A4:A35 (Date), then hold Ctrl while selecting H4:O35 (Top-, Above-, Normal-, Below-, 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. Other adjustments at this point included deleting the legend, moving the axis tick labels to the Low position, and formatting the X axis major spacing to 7 days.
Note: for my light gray color, I used a custom color in the Excel color palette. I went to the Tools menu > Options > Color tab, clicked on the "dark yellow" square (the greenish-brown square, third from the left in the second row), clicked on Modify, and selected the third lightest gray on the custom color tab. I used the lightest gray on Excel's palette (just above the white square) as my medium gray color. Now the Daily High and Low data is added to the chart. Select F4:G35 (Daily Low and High), 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. These two series are added as two more stacked column series on top of the nicely formatted gray series showing the historical norms and extremes.
Change the two new series to Line types. Select one of these new series, and on the Chart menu, choose Chart Type, and select Line Chart in the left list, and any of the subtypes on the right. Select the other new series, and repeat (use the F4 function key as a shortcut for Repeat Last Action).
Add the up-down bars. Double click one of these new line series, and on the Options tab, check the Up-Down Bars box. Decrease the number in the Gap Width box if you want thicker up-down bars (I kept this at 150).
Format the line series and the up-down bars. Double click one series, and on the Patterns tab, choose None for Line and None for Markers. Select the other line series, and repeat (use the F4 function key as a shortcut for Repeat Last Action). Double click the up-down bars and on the Patterns tab choose appropriate settings for Area color (Orange) and Border (None).
Add the series which will indicate the month's minimum and maximum temperatures. Copy Q4:R35 (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 become two more line series, and (temporarily) mess up the nice up-down bars.
Change the two new series to XY types. Select one of these new series, and on the Chart menu, choose Chart Type, and select XY (Scatter) Chart in the left list, and the Scatter subtype (markers without lines) on the right. Select the other new series, and repeat (use the F4 function key as a shortcut for Repeat Last Action). Notice that the up-down bars have returned, and Excel has added secondary X and Y axes to the chart.
Move these new series to the primary axis. Double click one of the new series, and on the Axis tab, select the Primary option. Select the other new series, and repeat (use the F4 function key as a shortcut for Repeat Last Action). The new markers now line up with the highest and lowest ends of the up-down bars.
Format the Monthly High and Low series. Double click on one of the 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 T4:U7 (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 T12:U13 (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 an XY series, these series are added as XY series; Record is shown in blue and Daily Range in green.
Add labels to the new XY series. 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:
Hide the series and format the labels. To hide the series, double click on each and on the Patterns tab choose None for Line and None for Markers. I've used orange and gray font colors so the labels are more clearly associated with the historical series in the chart.
Area Combination Chart for Monthly Temperatures is an alternative version of this chart that uses areas rather than columns to display the temperature data. |
Peltier Technical Services, Inc.Excel Chart Add-Ins | Training | Charts and Tutorials | PTS BlogPeltier Technical Services, Inc., Copyright © 2011. All rights reserved. |
