Excel: Percentage Values Not Accurately Represented in Chart
ID: Q87964
|
The information in this article applies to:
-
Microsoft Excel for Windows, versions 4.0, 4.0a, 5.0
-
Microsoft Excel for the Macintosh, version 4.0
SUMMARY
Microsoft Excel creates charts based on the numerical value (for
example .08), not the displayed value (for example, 80%), of the data
being plotted. Percentages plotted against nonpercentage values may
result in a poor visual representation of your data (because of the
large difference between the values).
To improve the visual representation of your data, do either of the
following:
- Create a chart that uses a different scale for your percentage
values.
-or-
- Use a multiplier to scale your percentage values so they are more
in-line with your other data.
MORE INFORMATIONCreating a Chart with Two Axis Scales in Microsoft Excel 5.0
In Microsoft Excel 5.0, see the topic in Help for using Multiple Chart
Types And A Secondary Axis. To find this Help topic, choose the Search
button in Help and type "chart types" (without the quotation marks), and
choose the Show Topics button. From the list of topics, select the Multiple
Chart Types And A Secondary Axis topic and choose Go To.
Creating a Chart with Two Axis Scales in Microsoft Excel 4.0
In Microsoft Excel 4.0, create an overlay or combination chart. Before you
create an overlay chart, you need to determine the order of your series
and, if necessary, change the order so that your percentage values are the
highest numbered series in the plot order.
To determine the order of your chart series, follow these steps:
- Bring your chart up in the active chart window.
- From the Chart menu, choose Edit Series.
- In the Edit Series dialog box, the Series appear in the order that
Microsoft Excel uses to create your chart (the Plot Order). If the
series that contains your percentages is the last one in the list, you
don't need to change the order and can now create the overlay chart.
Otherwise, you first need to change the Plot order.
NOTE: "New Series" in the Series list is used for adding a new series
to your chart (you can ignore this option for this procedure).
To change the plot order of your series, follow these steps:
- Determine the highest plot number by selecting the last series in
the list and noting the number displayed in the Plot Order box.
- Select the series that contains your percentages.
- In the Plot Order box, change the number to the highest plot order
number determined from step 1.
- Choose the Define button. Microsoft Excel reorders the list. The series
containing your percentage values should now be the last one in the
list.
To create the overlay chart, follow these steps:
- From the Chart menu, choose Add Overlay.
- From the Chart menu, choose Axes and select the Overlay Value (Y)
Axis check box.
- Choose the OK button.
Using a Multiplier to Scale Your Percentage Data in Microsoft Excel 5.0
Scale your percentage data by using a named formula to scale values,
and then substitute the named formula for the series reference.
To define the named formula, follow these steps:
- Open the worksheet that contains your chart values, and choose Name from
the Insert menu, and then choose Define.
- In the Name box, type the name you want to use for your scale
formula, for example, ScaledPerCnts.
- In the Refers To box, enter the following formula
<multiplier>*<series reference>
For this variable Enter the following value
----------------------------------------------------------------------
<multiplier> The value to multiply your data by. This should be
equal to the largest value in your data rounded to
the nearest power of 10. For example, if your
largest number is 957, use 1000 as a multiplier.
<Series Reference> The cell reference of the column containing your
percentage values. Use a fixed reference, that is,
include dollar signs in front of the row and column
values (for example, $A$1:$A$10).
For example, if your percentage values are in the cell range A1:A10 and
your data values range from 0 to 900, type the following and choose OK:
1000*$A$1:$A$10
To Substitute the variable in the series formula, follow these steps:
- Bring your chart up in the active chart window.
- From the Chart menu, choose Edit Series.
- In the Series list, select the series that contains your
percentages.
- Substitute your named formula for the cell reference in the Y
Values box. For example, change
Y Values: =Sheet1!$A$1:$A$10
to the following:
Y Values: =Sheet1!<Your formula name>.
- Choose the OK button.
Using a Multiplier to Scale Your Percentage Data in Microsoft Excel 4.0
Scale your percentage data by using a named formula to scale values,
and then substitute the named formula for the series reference.
To define the named formula, follow these steps:
- Open the worksheet that contains your chart values, and choose Define
Name from the Formula menu.
- In the Name box, type the name you want to use for your scale
formula, for example, ScaledPerCnts.
- In the Refers To box, enter the following formula
<multiplier>*<series reference>
For this variable Enter the following value
-----------------------------------------------------------------------
<multiplier> The value to multiply your data by. This should be
equal to the largest value in your data rounded to
the nearest power of 10. For example, if your
largest number is 957, use 1000 as a multiplier.
<Series Reference> The cell reference of the column containing your
percentage values. Use a fixed reference, that is,
include dollar signs in front of the row and column
values (for example, $A$1:$A$10.).
For example, if your percentage values are in the cell range A1:A10
and your data values range from 0 to 900, type the following and choose
OK:
1000*$A$1:$A$10
To Substitute the variable in the series formula, follow these steps:
- Bring your chart up in the active chart window.
- From the Chart menu, choose Edit Series.
- In the Series list, select the series that contains your
percentages.
- Substitute your named formula for the cell reference in the Y
Values box. For example, change
Y Values: =Sheet1!$A$1:$A$10
to the following
Y Values: =Sheet1!<Your formula name>.
- Choose the OK button.
REFERENCES
"User's Guide 1," version 4.0, pages 394-431
Keywords :
Version : WINDOWS:4.0,4.0a,5.0; MACINTOSH:4.0
Platform : MACINTOSH WINDOWS
Issue type : kbinfo
|