Excel Charts - Goal Chart Tutorial



 


You have no doubt seen Goal Charts promoting fund raisers or membership drives.  You probably have seen these for United Way, the YMCA, the local band for band uniforms, etc.  These Goal Charts show how close the organization is to reaching its ultimate goal.  You could place a Goal Chart on a web page so that everyone could view it or you could make it available to others by sharing it on your Onedrive.

Practice for Creating an Excel Goal Chart

In this exercise you will create a goal chart for a band whose goal is to raise $2000.  Each week the band enters the amount of money they have raised for that week.  The goal chart will show in green the total amount raised so far.  The amount still need to be raised will appear in red.

  1. Create a Worksheet with the following data.

    2. Data to enter for Goal Chart

  2. In cell B17 enter the Goal value which is 2000.
    In cell B19 enter the formula =SUM(B2:B15)
    In cell B21 enter the formula =B17-B19
  3. Select cells B17, B19 and B21.  Click on the HOME tab.  In the Number group click on the $ formatting button.  Remove the two decimal positions.
  4. Select cells A19:B19 and A21:B21.  Click on the INSERT tab.  Click on the dialog box launcher for the Charts group.
    Click on the the All Charts tab.
    In the left pane select Column.
    Click on Stacked Column in the upper half of the right pane.
    Two stacked column charts are displayed.  Select the one on the right.
    Click on the OK button.

    Stacked Column Chart

  5. Click on the Horizontal Axis.  It currently contains the value1.  Right-click on it then select Delete.
  6. Click on the Orange part of the chart(Series "Still Needed").  Right-click on it then select Format Data Series.
    Click on the Fill & Line paint bucket Fill & Line paint bucket .  Select Solid Fill.
    Click on the color button then select red from the Standard Colors.
    Click on the Blue part of the chart (Series “Raised”).
    Click on the color button then select green from the Standard Colors.
  7.  Hold down the Ctrl Key and click on the Red portion of the Chart.  Click on the Chart Elements button  Chart Elements Button.   Select Data Labels.  Click on the arrow to the right then select Center.
  8. Select the FORMAT tab.  In the Current Selection group click on the down arrow for the Chart Elements then select Vertical (Value) Axis.
     

    Vertical Value Axis

    Click on Format Selection.
    In the Axis Options set the Minimum bound to 0 and the Maximum bound to 2000.  Excel automatically adjusts the Major and Minor units.
    Change the Chart Title to Band Goal to Raise $2000.  Select the title then press Ctrl + B to bold the text.

  9. Adjust the height and width of the chart so that it is taller and narrower.

    Goal Chart for Band

    We will add an upward arrow to the right of the chart and fill it with green to indicate we want the direction we want the raised amount to go.
    Click on the INSERT tab.  In the Illustrations group click on the Shape button.  In the Block Arrows group select the Up Arrow.
  10. Drag out the arrow as shown in the figure below.

    Add Arrow to Goal Chart

  11. In the Shapes Styles group click on the green Shape Style to convert the arrow you just drew to green.
  12. The values on the chart need to stand out more:
         Click on the $1,356 label.  Click on the HOME tab.
         Change the Font Size to 12.
         Click on the B for Bold.
         Change the font color to White.
         Click on the $644 label.
         Change the Font Size to 12.
         Click on the B for Bold.
         Change the font color to White.
  13. Click on the Legend. Change the font size to 12.
    Click on the Vertical Axis.  Change the font size to 10.
  14. Type 130 in cell B8. Notice how the chart automatically adjusts.

    15. Goal Chart with Green Arrow