Home Archives for Format Excel Chart Values
November 12, 2010 1 Comment

How to Create a Thermometer Chart in Excel

When you want to create a chart that tracks your progress towards reaching a goal – e.g. Monthly Sales Goal – use a Thermometer Chart in Excel. This is a fairly easy chart to create. However, there are a few “got’cha” steps along the way to avoid. Here are the key points to consider when creating a thermometer chart in Excel:

  • Base the chart on one cell. This cell contains the formula =”month to date number” / goal – formatted as a percentage.
  • Be sure to clearly separate this one cell from the other cells – e.g. insert a blank row about the formula cell.
  • Begin with a Column Chart Type.
  • Format your Vertical Axis on the chart – the percentage values. Set the Minimum Value to be “fixed” at 0.0 and the Maximum Value to be “fixed” at 1.0
  • Add a Data Label to your chart – there are several options for placing this on the chart.
  • Continue to format your chart as desired.

Follow this link if you want to watch this video in High Definition, Full Screen Output.

You can learn more about creating and formatting charts on my DVD, “The 50 Best Tips for Excel 2007.” Click here to add this to your secure shopping cart.

How to Create a Thermometer Chart in Excel [ 7:12 ] Play Now | Play in Popup | Download (874)
Link to this post!
Filed Under: Excel Charts, Excel Training, Podcast Tips & Timesavers Tagged: , , , ,
October 24, 2008 1 Comment

Click & Drag a Data Series to Goal Seek in Excel

Did you know that you can click on a Data Series in an Excel Chart and “drag it to reach a new value?” You can!

Changing the data value in your chart brings up the Goal Seek Dialog Box. Now, it is a simple matter to confirm your new value and tell Excel which cell reference to change in your formula. Instantly, your chart redraws and your data and formula are updated with the substitute value – and your NEW GOAL!

Here are the steps to follow in this Excel Video Lesson:

  1. I use the =FV() Function to show the future value of my investment for each year that we invest. The =FV() Function requires a “fixed” interest rate and a constant investment value contributed each period.
  2. I inserted a “Line” type chart onto my worksheet – i.e. I “embedded” the chart on the sheet.
  3. I used a “Custom Format” on the Value Axis for our numbers (#,##0,K) to show the numbers as thousands ($80K)
  4. For this chart, I also changed the “Maximum Value” of the Value Series by typing in a new (higher) number.
  5. To reach a “new goal” on our chart, first click to select the data series. Then click again and drag the data line to a new point on the chart.
  6. This brings up the “Goal Seek” Dialog Box. Edit your new goal if necessary. Then click on the cell that you want to change in order reach your goal. That cell must be referenced in your formula. The chart redraws and your formula results are updated.

Find the Excel Video Lesson that you want – Index of all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.

Link to this post!
Filed Under: Excel Charts, Excel Functions, Excel Training, What-if Analysis Tagged: , , , , ,