Excel AppNote: Tips for Manipulating Data in a Chart (XE0183)ID: Q107484
|
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 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 Last Reviewed: April 8, 1999