Creating a Bar Chart for Temperatures

Ronald has a worksheet that shows low and high temperatures, by date, in 3 columns. He would like to chart the temperatures in a horizontal bar chart, where the bar starts at the low and extends to the high for each date. For instance, the range for temperature on one day has a low of -2 (negative 2) degrees and a high of positive 5 degrees. Therefore, the horizontal bar would start at -2 and extend to +5. For a temperature range where the low was 9 and the high was 22, the horizontal bar for that date would start at +9 and extend to +22. Ronald cannot get this type of bar chart created for some reason.

As with many things in Excel, the approach you take will depend on the data you are working with. For instance, let's say you have the temperatures (minimum and maximum) for each day in January 2023 for Knoxville, Tennessee. The lowest temperature for any date in that month is 23 and the highest is 75. (All temperatures in this tip are on the Fahrenheit scale.) This data is easy to chart, but it does require the use of a helper column.

First, about the helper column: What Ronald wants displayed is a bar that shows the temperature span between the low and high temperatures for a day. This is easily determined by subtracting the low from the high. So, this is the purpose of the helper column—to note the difference between low and high. Since you want to use this helper column in your chart, let's assume that your data starts with the date in column A, the low temperature in column B, and the high temperature in column C. Add a couple of blank columns between B and C, so that the high temperature is now in column E. In column C create your "difference" helper column. (See Figure 1.)

Figure 1. Getting your data ready to chart.

Now, select one of the cells in columns A, B, or C. Then, follow these steps:

1. Press F11. Excel creates a new chart sheet for the data. In addition, the Chart Design tab of the ribbon is automatically selected.
2. Click the Change Chart Type tool. Excel displays the Change Chart Type dialog box.
3. At the left side of the dialog box choose Bar.
4. In the types of bar charts (shown along the top of the right side of the dialog box) choose Stacked Bar. This is the second type of bar chart.
5. Click the OK button. Excel closes the Change Chart Type dialog box and reformats your data as a stacked bar chart. (See Figure 2.)



Figure 2. Your data in a stacked bar chart.

At this point, the blue bars are the minimum temperatures and the orange is the difference between the low and high. Since the data is stacked, this means that the orange starts at the low temperature for the day and ends at the high temperature for the day. This is the temperature span that Ronald needs. However, it would be beneficial to hide the blue data series (the one that represents the low temperature).

To do this, right-click on one of the blue bars and choose Format Data Series. Excel displays the Format Data Series task pane at the right of the screen. Using the controls in the task pane, set the bar fill to "No Fill" and the border to "No Line." This makes the blue data series invisible and you end up with the type of chart you wanted. (See Figure 3.)

Figure 3. Showing just the temperature ranges for each day.

At this point you can do any other customizations to your data that you desire, such as changing the title and legend.

I started this tip by noting that how you approached your charting depends on your data. In the approach already discussed, the temperatures were all above 0; there were no negative temperatures. This approach won't work if there are negative temperatures because the calculated temperature differential (in the helper column) will not, when plotted in a stacked bar chart, begin at the low temperature (which may be negative) and end at the high temperature (which may also be negative).

Because of this, you'll need four helper columns to calculate the data that will finally be charted. Assuming your data is in columns A (date), B (low), and C (high), you can place these helper columns in D:G. These should have the headings NegBlank, Negative, PosBlank, and Positive. In row 2 you can place the following formulas:

D2: =IF(AND(B2<0,C2<0),C2,0)
E2: =IF(AND(B2<0,C2<0),B2-C2,IF(C2<0,0,IF(B2<0,B2,0)))
F2: =IF(AND(B2>0,C2>0),B2,0)
G2: =IF(AND(B2>0,C2>0),C2-B2,IF(C2>0,C2,0))

Copy these down as many rows as necessary and your data is now ready for charting. (See Figure 4.)

Figure 4. Your data with four helper columns.

To create the chart, follow these steps:

1. Select one of the cells in your data and press F11. Excel creates a new chart sheet for the data. In addition, the Chart Design tab of the ribbon is automatically selected.
2. Click the Change Chart Type tool. Excel displays the Change Chart Type dialog box.
3. At the left side of the dialog box choose Bar.
4. In the types of bar charts (shown along the top of the right side of the dialog box) choose Stacked Bar. This is the second type of bar chart.
5. Click the OK button. Excel closes the Change Chart Type dialog box and reformats your data as a stacked bar chart. Note that this chart represents all seven columns from your data. (See Figure 5.)



Figure 5. Your data in a stacked bar chart.

1. With the Chart Design tab of the ribbon still selected, click the Select Data tool. Excel displays the Select Data Source dialog box. (See Figure 6.)



Figure 6. The Select Data Source dialog box.

2. In the Legend Entries column, clear the check boxes next to the "Low" and "High" series. This will remove the low and high temperature columns so they are not included in the chart.
3. Click on OK. Excel modifies and redisplays the chart so it includes only the desired columns from the source data.
4. Right-click one of the data series (it doesn't matter which one) and choose Format Data Series. Excel displays the Format Data Series task pane at the right of the screen.
5. Just under the Format Data Series title in the task pane you'll see a drop-down list named Series Options. Click this and choose the Series "NegBlank" option. This ensures you'll be working with this specific data series.
6. Click the Fill & Line icon (it looks like a paint bucket).
7. Expand the Fill option and click the No Fill radio button.
8. Expand the Border option and click the No Line radio button.
9. Click again on the Series Options drop-down list, this time choosing the Series "PosBlank" option.
10. Again click No Fill (under the Fill options) and No Line (under the Border options).
11. Click again on the Series Options drop-down list, this time choosing the Series "Negative" option.
12. Under the Fill options, use the Color tool to choose the color you want used for your bar.
13. Click again on the Series Options drop-down list, this time choosing the Series "Positive" option.
14. Under the Fill options, use the Color tool to choose the same color you selected in step 17.
15. Double-click the dates in the chart. Excel changes the Format Data Series task pane to the Format Axis task pane.
16. Click the Axis Options icon.
17. Expand the Labels options.
18. Using the Label Position drop-down list, choose the Low option. Excel immediately moves the dates axis leftward so it is to the left of the chart.
19. Close the Format Axis task pane. (See Figure 7.)



Figure 7. Your chart, mostly finished.

Your chart is basically done at this point, but you can make other formatting adjustments, as desired. (By the way, the data charted here is from actual temperatures for January 2023 for Gillette, Wyoming. As you can tell, we have rather wide swings in temperatures in our state.)

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9763) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.