Excel AppNote: Tips for Manipulating Data in a Chart (XE0183)

 Last reviewed: February 2, 1998
Article ID: Q107484
The information in this article applies to:
  • Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a
  • Microsoft Excel for the Macintosh, versions 3.0, 4.0

The Application Note "Tips for Manipulating the Data in a Chart" (XE0183) provides an overview of how values are plotted in a chart and discusses how to add information to and delete information from a chart, how to make a chart independent from a worksheet, and how to add data point labels to a chart.

You can obtain this Application Note from the following sources:

  • Microsoft Download Service (MSDL)
  • The Internet (Microsoft anonymous ftp server)
  • Microsoft Product Support Services

For complete information, see the "To Obtain This Application Note" section at the end of this article.

THE TEXT OF XE0183

   ======================================================================
     Microsoft(R) Product Support Services Application Note (Text File)
              XE0183: TIPS FOR MANIPULATING THE DATA IN A CHART
   ======================================================================
                                                     Revision Date: 11/93
                                                         No Disk Included
   
   The following information applies to Microsoft Excel versions 3.0,
   4.0, and 4.0a for Windows(TM), and Microsoft Excel versions 3.0 and
   4.0 for the Macintosh.
   
   -----------------------------------------------------------------------
   | INFORMATION PROVIDED IN THIS DOCUMENT AND ANY SOFTWARE THAT MAY     |
   | ACCOMPANY THIS DOCUMENT (collectively referred to as an Application |
   | Note) IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER      |
   | EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED      |
   | WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR       |
   | PURPOSE. The user assumes the entire risk as to the accuracy and    |
   | the use of this Application Note. This Application Note may be      |
   | copied and distributed subject to the following conditions:  1) All |
   | text must be copied without modification and all pages must be      |
   | included;  2) If software is included, all files on the disk(s)     |
   | must be copied without modification (the MS-DOS(R)  utility         |
   | diskcopy is appropriate for this purpose);  3) All components of    |
   | this Application Note must be distributed together;  and  4) This   |
   | Application Note may not be distributed for profit.                 |
   |                                                                     |
   | Copyright (C) 1989-1993 Microsoft Corporation. All Rights Reserved  |
   | Microsoft and MS-DOS are registered trademarks and Windows is a     |
   | trademark of Microsoft Corporation.                                 |
   |---------------------------------------------------------------------|


                               OVERVIEW
                               ========
This Application Note provides and overview of how values are plotted in a chart and discusses how to add information to and delete information from a chart, how to make a chart independent from a worksheet, and how to add data point labels to a chart. 

            HOW MICROSOFT EXCEL PLOTS YOUR DATA IN A CHART
            ==============================================
When you select data in a worksheet to be plotted in a chart, Microsoft Excel plots that data based on the shape of your worksheet selection and the type of chart you select. The data in charts is represented in data series and categories. For all 2-D charts except pie charts, the y-axis is the value axis against which each of your data series is plotted. In xy (scatter) charts, both the x-axis and the y-axis are value axes. In 3-D charts, the z-axis is the value axis. Each point in a data series is grouped in a category that is represented on the x-axis.

In Microsoft Excel, your data is plotted so that your chart has more categories than series. If the selection has more rows than columns, as in the following example, 

      |  A   |   B   |   C
   --------------------------
   1  |         1991    1992
   2  | Jan        1       2
   3  | Feb        2       3
   4  | Mar        1       2
   5  | Apr        2       3
each column is plotted as a separate data series:

[Chart Deleted]

If the selection has more columns than rows, as in the following example, 

     |  A   |   B  |   C  |   D  |   E
   --------------------------------------
   1 |         1991   1992   1993   1994
   2 | Jan        1      2      6     10
   3 | Feb        2      3      7     11
each row is plotted as a separate data series:

[Chart Deleted]

For more information about how chart data is plotted in Microsoft Excel, see the appropriate manual and page numbers listed below: 

   Version of Microsoft Excel            Manual title      Page numbers
   --------------------------------------------------------------------
   
   4.0 for the Macintosh or for Windows  "User's Guide 1"  423-425
   3.0 for Windows                       "User's Guide"    400-404
   3.0 for the Macintosh                 "User's Guide"    400-404
For more information about the parts of a chart, see the appropriate manual and page number listed below: 

   Version of Microsoft Excel             Manual title     Page numbers
   --------------------------------------------------------------------
   
   4.0 for the Macintosh or for Windows   "User's Guide 1" 401
   3.0 for Windows                        "User's Guide"   395
   3.0 for the Macintosh                  "User's Guide"   395
For information about specific chart types, see the appropriate manual and page number listed below: 

   Version of Microsoft Excel             Manual title      Page numbers
   ---------------------------------------------------------------------
   
   4.0 for the Macintosh or for Windows   "User's Guide 1"  406-418
   3.0 for Windows                        "User's Guide"    408-410,
                                                            414-421
   3.0 for the Macintosh                  "User's Guide"    408-410,
                                                            414-421


         CHANGING THE WAY MICROSOFT EXCEL INTERPRETS YOUR DATA
         =====================================================
In Microsoft Excel, the shape of your data selection always determines how the data appears in your chart: Microsoft Excel assumes that your selection consists of more categories than series. However, if your data does have more series than categories, you can change the way Microsoft Excel plots your data. The best way to do this varies depending on your version of Microsoft Excel.

Changing Category and Series Orientation in

Microsoft Excel Version 4.0

If you are using Microsoft Excel version 4.0, the easiest way to change the plot order is to use the ChartWizard tool:

  1. Select your chart and choose the ChartWizard tool.

  2. When you reach step 2 of 2, select either columns or rows. On the left, a sample of what your chart will look like is displayed. If you want the axes reversed, select the alternate option under Data Series In. For example, if Microsoft Excel has defaulted to rows, select columns. Verify that your chart is displayed correctly, and then choose OK.

For more information on controlling how Microsoft Excel plots your chart data series, see pages 423-427 in "User's Guide 1."

Changing Category and Series Orientation in

Microsoft Excel Versions 3.0 and 4.0

Another way to change the way Microsoft Excel plots your data is to re- create the chart and specify whether you want your data series values to come from row or column data. You can specify this information in the Paste Special dialog box in the Chart window.

For more information about specifying rows or columns as the chart data series, see the appropriate manual and page number listed below: 

   Version of Microsoft Excel             Manual title       Page numbers
   ----------------------------------------------------------------------
   
   4.0 for the Macintosh or for Windows   "User's Guide 1"   424
   3.0 for Windows                        "User's Guide"     406
   3.0 for the Macintosh                  "User's Guide"     406

CONTROLLING HOW ZERO VALUES AND

BLANK CELLS ARE PLOTTED IN A LINE CHART

In a Microsoft Excel line chart, zero values are plotted, while blank cells cause a break in the line. You can use the #N/A error value in place of a zero or blank cell if you don't want to plot a data point but you do want a continuous line.

How Zero Values Are Plotted

In Microsoft Excel, zero values are plotted as points on a chart. For example, if you were to chart the following values in a line chart 

      |  A  |  B  |  C  |  D  |  E
   ----------------------------------
   1  |  1     2     3     4     5
   2  |  1     2     0     3     4
your chart would resemble the following:

[Chart Deleted]

How Missing Values Are Plotted

Microsoft Excel will not plot a point on a chart if the reference for that point is blank. If the range of values your series is based on contains values in cells prior to and following a blank cell, the line will be drawn to the last point before the point that has no value and will resume at the first point after it that does have a value. The result will be a broken line. If you want your chart to skip a point and break the line, clear the corresponding cell. For example, the information from column C2 has been cleared from the table below 

      |  A  |  B  |  C  |  D  |  E
   ----------------------------------
   1  |  1     2     3     4     5
   2  |  1     2           3     4
to create a line chart that resembles the following:

[Chart Deleted] 

  NOTE: For this procedure to work correctly, you must clear the
  entire contents of the cell. Any data, such as a space character in
  the cell, will result in a point being plotted in the chart. If it
  is a text character, the point will be plotted as a zero value.

Using # N/A to Create a Continuous Line

To create a line that continues through a point that has no information, type the value #N/A in the blank cells in your table. For example, the following data 

      |  A  |  B  |  C   |   D  |  E
   ------------------------------------
   1  |  1     2     3       4     5
   2  |  1     2    #N/A     3     4
will be plotted in a straight line between the point before and the point after but will not include a data point for the missing value; it will resemble the following example:

[Chart Deleted] 

      ADDING INFORMATION TO AND DELETING INFORMATION FROM A CHART
      ===========================================================
The data in your chart is stored in formulas that use the SERIES() function. You do not need to edit the SERIES() function directly. You can easily add or delete an entire series from a chart or add or delete data points from one or more existing series using either of the following methods. The best method to use depends on your version of Microsoft Excel.

WITH THE CHARTWIZARD TOOL (MICROSOFT EXCEL VERSION 4.0 ONLY)

If you are using Microsoft Excel version 4.0, the easiest way to add or delete chart data is to use the ChartWizard:

To add information

  1. If your chart is a chart object, select the chart by clicking it once. If your chart is in a separate chart window, activate it.

  2. Click the ChartWizard button on the Chart toolbar.

  3. In the ChartWizard Step 1 Of 2 dialog box, change the range to include the data you added. If you add data to the existing series, enlarge the range to include the new row. If you add a new series, enlarge the range to include the new column. 

         NOTE: When your new data is entered in cells that are not adjacent
     to the range of cells your chart is linked to, separate the data
     ranges with a comma when you update the chart. For example, if your
     chart data is located in cells $A$1:$C$4, and you want to add a new
     series to your chart and you've entered the data in cells E1:E4, in
     the Range box in the ChartWizard dialog box, change your reference
     to $A$1:$C$4,$E$1:$E$4.

To delete information

To use the ChartWizard to delete information from your chart, follow the steps above for adding data. In step 3, change the ranges so that they no longer include the cells that contain the information that you want to delete. 

   Tip: You can use defined names in place of actual cell ranges. This
   option enables you to update the chart by redefining the named range
   to include the new data. For more information about using defined
   names with charts, see the "Using Defined Names to Make Editing
   Charts Easier" section of this Application Note.
For more information on adding information to and deleting information from charts with the ChartWizard, see page 438 in "User's Guide 1," version 4.0.

WITH THE EDIT SERIES COMMAND

You can also add or delete information with the Edit Series command, which is located on the Chart menu. 

   NOTE: The Chart menu is displayed when a chart window is active. If
   you are working with a chart that is positioned as a graphic object
   on a worksheet, you will need to double-click the chart to activate
   the chart window.

To add a new series

  1. Select the chart window of the chart you want to edit, or if a chart is a graphic object on a worksheet, double-click it to activate the chart window.

  2. From the Chart menu, choose Edit Series.

  3. Under Series, select New Series from the top of the Series list.

  4. In the Y Values box, enter the range of the new series in the form 

          =<sheetname>!<range>

    where <sheetname> is the sheet that contains your chart data, and <range> is the range of values you want to plot in the new series.

  5. To accept the changes and close the Edit Series dialog box, choose OK. If you want to add another series, choose Define. 

         NOTE: You can edit the series only when the supporting worksheet is
     open.

To add data points

You can also use the Edit Series command to add new data points to existing data series. For example, if your category labels (x values) are in cells A2:A10 and the associated y values are in cells B2:B10, you would do the following to add a new data point to your chart:

  1. In cell A11, type the category label you want for the new x value, and in cell B11, type the value you want for the new y value.

  2. To display the new data in the chart, do the following to update the x values and y values for each data series in the chart: 

        a. From the Chart menu, choose Edit Series.


        b. In the X Values box, change the designated range from
       <FILENAME>!$A$2:$A$10 to <FILENAME>!$A$2:$A$11.


        c. In the Y Values box, change the designated range from
       <FILENAME>!$B$2:$B$10 to <FILENAME>!$B$2:$B$11.


        d. To update the chart, choose Define or OK.


          NOTE: If the whole range is not displayed in the X Values or the
      Y Values box, position your insertion point in the box and drag to
      the right. You should then be able to edit the ranges.

For more information about adding new series or data points with the Edit Series command, see the appropriate manual and page numbers listed below: 

   Version of Microsoft Excel             Manual title      Page numbers
   ---------------------------------------------------------------------
   
   4.0 for the Macintosh or for Windows   "User's Guide 1"  441-443
   3.0 for Windows                        "User's Guide"    442-443
   3.0 for the Macintosh                  "User's Guide"    442-443

ADDING A SECOND Y-AXIS

In Microsoft Excel, you can use an overlay chart to plot different types of data on the same chart. An overlay chart is a second chart plotted on top of the main chart in the same chart window. The overlay can be a different chart type or have a different scale or both. For example, if you have one data series that consists of numbers of units sold and a second data series that consists of revenues, the values representing units sold and those representing revenues are different types of information.

When you plot these two types of data on the same chart, if you use only one y-axis, the data markers may be skewed if the revenues are in a significantly higher range of values than the units sold. Instead, to meaningfully represent the different types of information, add an overlay chart with a second y-axis.

To add an overlay chart, do one of the following:

  • If you've just created a new chart and haven't yet customized it, use the Combination command on the Gallery menu. Select one of the combined chart options and choose OK. 

        -or-
  • If you've already customized the chart and want to preserve the formatting, use the Add Overlay command on the Chart menu.

Using either method, Microsoft Excel automatically divides your chart data series between the main chart and the overlay chart. If you have multiple data series and you want to plot specific data series in the overlay chart that were omitted when you added the overlay, use the Overlay command on the Format menu. Under Series Distribution, select the First Overlay In Series option and specify the plot number of the first chart data series you want plotted in the overlay chart. All data series with a greater plot number will also be plotted in the overlay chart. (To determine the plot number for each data series, choose Edit Series from the Chart menu and, with a specific series selected from the Series list, look at the value in the Plot Order box.) 

   NOTE: If you are working with a chart object on your worksheet and
   you want to add or edit an overlay chart, you must open the chart
   in its own window by double-clicking it.
For more information about adding and deleting overlay charts, see the appropriate manual and page numbers listed below: 

   Version of Microsoft Excel             Manual title      Page numbers
   ---------------------------------------------------------------------
   
   4.0 for the Macintosh or for Windows   "User's Guide 1"  429-431
   3.0 for Windows                        "User's Guide"    422-425
   3.0 for the Macintosh                  "User's Guide"    422-426

To display two y-axes in a 2-D chart

To display two y-axes in a 2-D chart, you need to use an overlay on the chart. When you create an overlay, you must use at least one series for the overlay. In order to create two identical y-axes (without displaying the values in the overlay series), the chart must include an empty series that can be used as the overlay series. To create a 2-D chart with two identical y-axes:

  1. Create your chart using any of the conventional methods.

  2. From the Chart menu, choose Edit Series.

  3. Create an empty series by placing a reference to an empty cell in the X Labels and Y Values boxes. Choose OK to define the new series.

  4. From the Chart menu, choose Add Overlay.

  5. From the Format menu, choose Overlay and make sure that the first overlay series is the last series available (the empty one).

  6. From the Chart menu, choose Axes and select the Value (Y) Axis option in the Overlay group.

  7. Choose the new axis to select it and, choose Scale from the Format menu.

  8. Change the scale of the new axis to match the primary y-axis.

  9. If desired, change the color of each series back to its original color.

USING DEFINED NAMES TO MAKE EDITING CHARTS EASIER

When you edit your chart, you can use defined names in place of actual cell ranges. This technique enables you to update the chart by redefining the named range to include the new data.

To name the ranges that contain your data series

If in the Edit Series dialog box your X Values are in the range $A$2:$A$10 and your Y Values are in the range $B$2:$B$10, you can define these ranges on your worksheet as <xvals> and <yvals>, 

respectively.   Once you've defined your ranges as xvals and yvals,
you can use these names in place of the actual cell ranges in the Edit Series dialog box. That is, instead of entering $A$2:$A$10 in the Y Values box, you can type "yvals" (without the quotation marks), and instead of entering $B$2:$B$10, you can type "xvals" (without the quotation marks). In addition, with the x values and y values represented with defined names, you can add or delete data points by redefining the appropriate defined names in your worksheet (in this case, xvals and yvals) rather than having to activate the chart and use the Edit Series command each time you want new information to appear in your chart.

To define the range that contains your chart data

You can also use defined names along with the OFFSET() function to create formulas so that they are changed automatically when you update a range that encompasses the entire range from which the chart is drawn. For example, if you define the range $A$1:$B$10 as <ChartRange>, you could define xvals with the following formula 

   =OFFSET(ChartRange,1,0,rows (ChartRange)-1,1)
and the name yvals with the formula: 

   =OFFSET(ChartRange,1,1,rows (ChartRange)-1,1)
If you want to add the new data to your chart, you only need to redefine the defined name ChartRange to include the cells that contain your new data. For example, if you want to add the information in cells $A$11:$B$11 to your chart, redefine ChartRange from $A$1:$B$10 to $A$1:$B$11.

When you redefine ChartRange, the defined names xvals and yvals are updated immediately, and the changes are automatically reflected on your chart because the defined names are linked to the chart. This method is especially useful when you are creating charts that have many data series.

For more information on naming a cell or range of cells on a worksheet, see the appropriate manual and page numbers listed below: 

   Version of Microsoft Excel             Manual title      Page numbers
   ---------------------------------------------------------------------
   
   4.0 for the Macintosh or for Windows   "User's Guide 1"  263-268
   3.0 for Windows                        "User's Guide"    224-227
   3.0 for the Macintosh                  "User's Guide"    216-219
For more information on using the OFFSET() function, see the appropriate manual and page numbers listed below: 

   Version of Microsoft Excel             Manual title      Page numbers
   ---------------------------------------------------------------------
   
   4.0 for the Macintosh or for Windows   "Function Reference"   299-300
   3.0 for Windows                        "User's Guide"         163-164
   3.0 for the Macintosh                  "User's Guide"         163-164


          BREAKING THE LINKS BETWEEN A CHART AND A WORKSHEET
          ==================================================
At times you may want to maintain a chart without it being linked to a supporting worksheet. You can do this by selecting each series in the chart and calculating the series formula so that its references are converted to the underlying values. This procedure allows you to open a chart by itself without receiving the message ''Update references to unopened documents?'' This procedure will also allow you to alter the information on your worksheet without changing previously created charts.

To evaluate and convert the formula for each series:

  1. Select a series marker.

  2. Select the entire contents of the formula bar (it will contain a SERIES() formula).

  3. To force the references to be evaluated to their underlying values, press CTRL+= (if you are using Microsoft Excel for Windows) or COMMAND+= (if you are using Microsoft Excel for the Macintosh).

  4. Press ENTER.

  5. Repeat steps 1-4 for each series you want to convert. 

        NOTE: You can also evaluate a portion of the series formula by
    selecting the reference you want and pressing CTRL+= (Windows),
    or COMMAND+= (Macintosh).


                           ADDING DATA POINT LABELS
                       ========================

TO ADD DATA POINT LABELS USING THE GALLERY AND FORMAT MENUS

If you want to have a line or an xy (scatter) chart with all the data points labeled:

  1. From the Gallery menu, choose Column and select the column chart with value labels (chart 7).

  2. From the Format menu, choose Main Chart. In the Format Chart dialog box, in the Main Chart Type box, change the chart type to Line or Scatter.

TO ADD DATA POINT LABELS WITH A MACRO

Note that the following procedure will not work on 3-D surface charts- -attaching text to series and data points is not an option for these chart types. In addition, this macro doesn't work with pie charts (2-D or 3-D). However, you can create a pie chart with labels by specifying a pie chart with labels in the gallery. The following sample macro will add data point labels to your chart.

  1. In a new macro sheet, type the following macro: 

         Attach_Text
     =ECHO(FALSE)
     =ERROR(FALSE)
     s=1
     attach_to=4+AND(GET.DOCUMENT(9)>6,GET.DOCUMENT(9)<>11)
     =WHILE(NOT(ISERROR(SELECT("S"&s))))
     p=1
     s=s+1
     =WHILE(NOT(ISERROR(SELECT("S"&s-1&"P"&p))))
     =ATTACH.TEXT(attach_to,s-1,p)
     p=p+1
     =NEXT()
     =NEXT()
     =RETURN()

  2. Select the cell containing the macro name Attach_Text, and choose Define Name from the Formula menu. Under Macro, select Command, and choose OK.

  3. Switch to the chart window that contains the chart to which you want to attach data point labels. From the Macro menu, choose Run. Select the Attach_Text macro, and choose OK.

The macro automatically attaches data point labels to each data series in your chart.

Additional query words: noupd appnote
Keywords : kbappnote
Version : WINDOWS:3.0,4.0,4.0a; MACINTOSH:3.0,4.0
Platform : MACINTOSH WINDOWS
Issue type : kbhowto

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.