Thermometer Chart in Excel

Here is a cool technique for creating a thermometer style chart in Excel. This was sent in by Abhay Jain of New Delhi, India. When I first opened the workbook, I was completely fooled by what I saw. When you see how Abhay creates the chart, it is very clever.

The thermometer chart is a chart based on a single data point. In a lone cell, put a formula that points towards your percentage completion towards a goal. If the original number is a percentage, multiply by 100 to get an integer value between 0 and 100.

From the menu, select Insert - Chart. Choose a Column Chart and the first column sub-type - cluster column.

Proceed to step 3 of the Wizard. On the Titles tab, ensure there is no title. On the Axes tab, uncheck the Category option. On the Gridlines tab, uncheck Major Gridlines. On the Legend tab, uncheck Show Legend. Choose Finish. You will have a fairly boring chart with one bar like this one.

Right-click the blue bar and choose Format Data Series. On the Options tab, change the Gap Width to 0.

On the Patterns tab, choose the Fill Effects button.

On the Gradient tab of the Fill Effects dialog, choose Two Color and select a red and orange color.

Choose OK to close the Fill Effects dialog. Back on the Patterns tab, choose Orange for the line color. Choose OK to close Format Data Series dialog.

The next couple of steps are somewhat tricky. To Format the plot area, you will right-click just above the orange bar, but below the top of the chart. In my case, this is a fairly narrow strip of white - basically the four points from 56 through 60. If you happened to have a chart where the orange is to the top of the chart, it would be hard to isolate the plot area. In this case, jump ahead to reset the axis scale and then come back to this step. Right click and choose Format Plot Area.

Change the Border color to Orange on the Format Plot Area dialog. Choose OK.

Next, right-click in above the top of the chart, but still inside the outer border to select the Chart Area. Choose Format Chart Area.

Change the Border to None and select Round Corners. Choose OK to close the Format Chart area dialog.

With the chart area selected, grab the resize handle on the bottom right corner. Drag in and down to make the thermometer be narrow and long.

During the resize, the font along the y-axis scale has become tiny. Right-click any of the numbers along the left side of the chart and choose Format Axis.

On the Patterns tab, change the line color to Orange.

On the Scale tab, change the Maximum to 100.

On the Font tab, choose Arial, Orange, and type 6.5 as the font size.

Choose OK to close the Format Axis dialog. This last step ensured that the chart extends from 0 to 100. You now have something that looks like a thermometer.

If you wish, add a circle to the bottom. Display the drawing toolbar with View - Toolbars - Drawing. Select the Oval icon. While holding down the shift key, draw an oval. The shift key will constrain the oval to be a circle. Right-click the circle and choose Format AutoShape. Choose the appropriate color for the fill and the line color. Drag the circle so that it is roughly centered below the thermometer.

Excel in the K-12 Classroom

Most teachers have computers in their classroom, but don’t know how to utilize Excel. In this segment, we’ll take a look at 3 classroom uses for Excel: 1) Create customized math exercise sheets 2) Track reading progress 3) Track Attendance Customized Math Exercise Sheets
Say that your first graders need to work on adding digits 1 to 9 where the sum does not exceed 10.

• Tools - Add-Ins - Make sure Analysis Toolpack is turned on
• For the top addend, use =RandBetween(1,8)
• The second addend needs to be a number between 0 and (9-top number). Use a formula of =RandBetween(0,(9-B2))
  

• Apply formatting, copy the formulas to create multiple problems.
  

Every time that you type the F9 key, you will produce a new worksheet.

Track Attendance

• Enter the student names down column A, starting in row 2.
• Enter the date for the first day of school in C1.
• Move the cellpointer to C1. In the lower right corner, there is a square dot. This is the fill handle. Right-click the fill handle and drag to the right. Drag out for 180 columns. When you release the fill handle, choose Fill Weekdays.
• To use the chart, enter an "X" for any day the student is absent.
• The formula in B2 is =CountIF(C2:IV2,"X")

Track Reading Progress
This is a cool chart to share with parents during parent-teacher conferences. All of the reading scores are stored out of sight in rows 34 & below. In the top of the worksheet, you will select a single student from the dropdown in A4. The chart will update to show that student.

• Enter your test data starting in row 34
• In cell A4, use Data - Validation - List and specify a list range of A34:A64. This will add the in-cell dropdown.
• The formula in B4 is =VLOOKUP($A4,$A$34:$I$56,2,FALSE). This will return the test result from column B for this student.
  

• When you copy the formula in B4 to C4 & D4, change the 3rd parameter from 2 to 3 for column C and then to 4 for column D.
• Build a chart using A3:D4. Format the chart so it is the right size for viewing with the parents.

As each parent comes in, choose the student from the dropdown in A4. The chart will update.

Track Progress Towards a Goal

With the new year coming up, you might want to track progress towards some sort of a goal. This cool chart in Excel contains elements that indicate if you are on track to meet a goal. Say that you want to run 250 miles this quarter.

• The black line is the goal
• The blue line is the "on track" line - you have to run 2.7 miles per day to meet the goal
• The green line is the actual miles run
• The red line is the prediction of where you will finish the quarter, based on your progress so far. The red line is by far the coolest part of the chart.

Here is how to create the chart.

1. Enter these headings at the top of a worksheet. In cell A5, enter the first date for the chart.

2. Put the cellpointer in A5. You will notice that there is a square dot in the lower right corner of A5. This is the fill handle. With the mouse, grab the fill handle and drag downwards. Watch the tooltip until you see that you've dragged far enough to include the last date of the quarter.

3. The formula for B2 is =COUNT(A5:A200). This will count the number of days in your range.

4. The formula for B3 is =B1/B2. This calculates how far you have to run each day to meet the goal.

5. Use column B to enter how far that you ran each day.

6. Although column B contains your actual data, it is not suitable for creating the chart. You will actually build a completely new chart range in columns C:F that will contain the elements needed for the chart.

7. Column C will contain dates. If one axis of your chart range contains numbers or dates, you must leave the top left cell of the chart range blank. This is a strange bug in Excel - leaving that cell blank will allows Excel's Intellisense to recognize the chart range later. So, leave cell C5 blank, and enter headings for Distance, Track, and Goal in D5:F5.

8. The formula in C5 is =A5.

9. Select cell C5. From the menu, select Format Cells. On the Numeric tab, choose the Date category and then the 3/14 format.

10. Select cell C5. Drag the fill handle down to copy this formula. Drag down as far as you have data in column A.

11. For columns D & E, the formulas in row 5 will be different than those in rows 6 & below. The formulas in row 5 will provide a numeric start. Formulas in row 6 & below will increment those numeric values in row 5. The formula for D5 is =B5 - this will simply copy the first day's distance to the chart range.

12. In a later step, Excel will add a trendline based on the values in column D. If you merely copied D5 down, you would end up with zeroes for future dates. The zeroes will invalidate the trendline. The trick is that you need to fill future dates with #N/A error values. In English, then, the formula basically says, "If we ran today, then add today's value to yesterday's total. Otherwise, put an N/A in this cell. To achieve this result, enter =IF(NOT(ISBLANK(B6)),D5+B6,NA()) in cell D6.

13. Select cell D6. Double click the fill handle to copy this formula down. The double-click trick works because the column to the left of this cell contains values that will guide Excel how far down the formula needs copied.

13. The formula in E5 needs to copy the per day from B3. Enter =B3 in E5.

14. The formula for the rest of E needs to add B3 to the previous day. The formula for E6 could be =E5+B3 but this formula would not work when you copy the cell down to the other rows. You always want the formula to add B3 to the cell above. Thus, use =E5+$B$3 in E6. The dollar signs make sure that when you copy the formula, it will always point to B3.

15. Move the cellpointer back to E6 and double-click the fill handle to copy the formula down.

16. The formula for all of column F is =$B$1. This range will be used to draw in the black goal line on the chart. Here is a tip for entering this formula. Start in F5. Type the equals sign. Using the mouse, touch B1. Then, type the F4 key to put the dollar signs in. Hit Ctrl+Enter to accept the formula and stay in the current cell. You can now double-click the fill handle to copy the formula down.

17. Finally, you are ready to create the chart. Select the range of C4:F94. Either use Insert - Chart from the menu, or click the Chart Wizard icon in the standard toolbar.

18. In step 1, choose a Line chart.

19. The chart will require a lot of customization, but none of it happens in the wizard, so just click Finish to create this horrible looking chart.

20. Right-click on the grey background and choose Format Plot Area.

21. Choose Fill Effects.

22. Set up a 2-color gradient as shown here.

23. Right-click the yellow Goal series line. Choose Format Data Series.

24. Choose a thick blue line for the goal series.

25. Repeat steps 23-24 to change the track line to a thin black line.

26. It is difficult to format the Distance series, as there are only 2 points and it is nearly impossible to see. If it is difficult to right-click an element of the chart, you can use the dropdown in the Charting toolbar.

27. Once the distance series is selected, use the Properties icon to open the Format dialog for the selected series.

28. Add a Marker to this dialog. Change the color to dark blue and the line thickness to medium.

29. To add the trendline, you will have to be able to right-click on the distance series. This is nearly impossible to do at this point in time. So - I suggest that you "cheat". Enter a fake data point of 20 in cell B7. This will allow you to right-click on the distance series. Choose Add Trendline.

30. On the Trendline dialog, choose the Linear Trendline.

31. Select cell B7 and type the Delete key to erase the fake value there. You will now have the trendline drawn in as a thick black line.

32. Right-click the trend line and choose Format Trendline.

33. Change the Trendline to a broken red line with a thin weight.

34. On the Options tab of the Format Trendline, change the series name to Prediction

You now have a completed chart. If you miss a day of running, enter a zero in column B. If you miss a couple of days, the red prediction line will show that you are not on track to make the goal.