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 INFORMATION

Creating 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

Last Reviewed: March 26, 1999