Running Microsoft® Excel 2000

Author Mark Dodge and Craig Stinson Pages 1024 Disk N/A Level Beg/Int Published 05/07/1999 ISBN 9781572319356 Price $39.99 To see this book's discounted price, select a reseller below.	More Information About the Book Table of Contents Sample Chapter Related Books About the Author Support: Book & CD Rate this book

• Adding Data
• Removing Data
• Replacing Data
• Changing the Plot Order
  • Using Multilevel Categories
• Adding Trendlines
• Adding Error Bars
• Dragging Chart Markers to Change Dat

Charts have a way of changing over time. New data arrives, old data becomes irrelevant, and new visual comparisons become meaningful. In this chapter you'll look at Microsoft Excel 2000's procedures for working with the data that drives your charts. You'll see how to add new points and series to a chart, how to change the order in which series are plotted, and how to plot multilevel categories of information. You'll also look at Excel's features for adding trendlines and error bars.

Adding Data

Excel provides several ways to add new data to a chart. We'll look at the simplest method first.

Figure 23-1 plots data through May 2. The figure shows a week's worth of data that hasn't yet been added to the chart. Notice that, because Chart Area is selected, Excel draws two rectangles around the chart's worksheet data. The first rectangle, in column A, outlines the worksheet range that the chart is using for its category-axis labels. The second rectangle, in columns B through E, outlines the four data series.

To extend the chart so it includes the new data in rows 750 through 755, drag the fill handle in the lower right corner of either rectangle.

Alternatively, you can use drag-and-drop copying procedures:

1. Select the new data, including the category-axis labels (the dates in column A of Figure 23-1).

2. Position your mouse on the border of the selection, so that the mouse pointer changes to an arrow.

3. Drag the selection and drop it anywhere on the chart.

   
   FIGURE 23-1.
   When you select a chart's chart area, Excel outlines the data area on your worksheet.

Note that while the chart area is selected Excel draws lines on the worksheet to show the cell range that's currently plotted. You can use these lines as a confirmation that your new data points have been added.

The methods just described work fine if your chart and its data are close together on the same worksheet. If they're not, you can use standard copy and paste procedures. Select the new data, choose Copy from the Edit menu, select the chart, and then use Paste from the Edit menu. Alternatively, you can use the Add Data command from the Chart menu. (The Add Data command does not normally appear on shortened menus. Click the double arrow at the bottom of the Chart menu to display the Add Data command.) The Add Data dialog box appears, as shown in Figure 23-2.

FIGURE 23-2.
Type the data range here or point to it on your worksheet.

Enter a reference of the range you want to add in the Range edit box by typing cell coordinates, typing a range name, or selecting the range with the mouse. If you use the mouse, you might first want to click the Collapse Dialog button at the right side of the edit box, but this step isn't required.

Adding a new data series is just like adding new points to existing series: simply select the data series you want to add, copy it, and then paste it onto the chart (or drag the selection to the chart). Assuming the new data series is adjacent to the existing data, Excel has no difficulty in determining that you want to add a whole new series, as opposed to a set of new points. If the new data is not adjacent to the old, Excel may still be able to add the data correctly. For example, in Figure 23-3 on the next page, Excel can correctly paste the selected data as a new series, because the selection is a column and has the same row positions as the existing three series. If the selection were in H3:H7 instead of H2:H6, however, as shown in Figure 23-4 on the next page, Excel would no longer know what to do. Pasting that data block creates the meaningless chart shown in Figure 23-4.

FIGURE 23-3.
If you paste this block onto the chart, Excel knows you want to add a series.

FIGURE 23-4.
When Paste doesn't give you what you want, use Undo, and then try again with Paste Special.

To help Excel out when it can't discern exactly how you want to paste new data, use Paste Special instead of Paste. Select the data you want to add, select the chart, and then choose Paste Special from the Edit menu. (Select the chart before you choose Paste Special or you'll see an entirely different set of options.) With a chart selected, the Paste Special dialog box appears, as shown in Figure 23-5.

FIGURE 23-5.
Use the Paste Special dialog box to tell Excel exactly how to display the new data.

Removing Data

Removing a data series from a chart is simple, provided you remem-ber to delete from the chart, not from the underlying worksheet. For example, suppose you want to remove Product C from the chart shown in Figure 23-3. If you simply clear the data from cells D2:D6 (by selecting that range and pressing the Delete key), Excel adjusts the chart, as shown in Figure 23-6.

FIGURE 23-6.
Clearing the worksheet data generates a blank series. Instead, select the series on the chart and then press Delete.

If you delete the range D2:D6 instead of clearing it (that is, if you actually remove the cells from the worksheet by selecting them and choosing the Delete command from the Edit menu), the chart's third series generates a #REF! error, and Excel displays an alert message. To avoid these errors, activate the chart, select the series you want to delete, and then press Delete. To remove particular data points rather than entire series, select your chart. Then drag the blue rectangle at the lower right corner of the bounding box that appears around your data. Drag it upward to remove the unwanted data.

Alternatively, choose Source Data from the Chart menu and click the Data Range tab. The dialog box shown in Figure 23-7 appears. Select the range you want to change and, as usual, you can modify the specification by typing the range or selecting it in the worksheet.

FIGURE 23-7.
To remove data points, change the range in the Data Range box.

Replacing Data

To make your chart plot a different set of data, select the chart area and drag the rectangle on your worksheet, just as you would if you were simply adding new data. In this case, however, don't drag the fill handle. Instead, drag the upper or lower boundary of the rectangle.

Alternatively, you can use the Series tab of the Source Data dialog box, shown in Figure 23-8, to change the data the chart illustrates. Select the chart, choose Source Data from the Chart menu, click the Series tab, and then change the contents of edit boxes on the Series tab of the Source Data dialog box.

Note that you can also use this dialog box to add and remove entire series. In most cases, however, you'll find it easier to use the cut-and-paste method for adding and the Delete key for deleting.

FIGURE 23-8.
The Series tab of the Source Data dialog box specifies the data series that make up a chart.

Changing the Plot Order

To change the order in which series are plotted, select any series, choose Selected Data Series from the Format menu, and then click the Series Order tab. The dialog box shown in Figure 23-9 appears.

Note that scrambling the series order may be an effective way to make all series visible in a three-dimensional chart type.

FIGURE 23-9.
Use the Series Order tab of the Format Data Series dialog box to change the plot order.

Using Multilevel Categories

Excel lets you categorize your categories. This sounds redundant, but a quick example will illustrate the technique. Suppose you want to plot the data shown in Figure 23-10 (the result is shown in Figure 23-11). Here, the series are months and the categories are the sales offices located in different cities. The categories-the city sales offices-are further classified by state, however.

FIGURE 23-10.
This worksheet uses multilevel categories: city sales offices are grouped by the states in which the cities are located.

FIGURE 23-11.
A multilevel category chart uses two or more sets of category names to label the category axis.

To plot the data in Figure 23-10 in a chart with multilevel categories, select the range A3:E10 and use the Chart Wizard or the Chart Command on the Insert menu to plot the data in a simple column chart. Excel displays both the city names and the state names below the category axis, as shown in Figure 23-11.

Adding Trendlines

A trendline is either a regression line that best fits the plotted data of a series or a line that plots a moving average of the values in a series. To add a trendline to a series in an area, bar, column, line, or xy chart, select the series and then choose Add Trendline from the Chart menu. (The Add Trendline command does not normally appear on shortened menus. Click the double arrow at the bottom of the Chart menu to display the Add Trendline command.) Excel displays the Add Trendline dialog box shown in Figure 23-12.

FIGURE 23-12.
A trendline illustrates the trends your data series predicts.

To specify how Excel should draw the trendline, select one of the Trend/Regression Type options. If you select Polynomial, indicate the highest power (from 2 through 6) for the independent variable in the adjacent Order box. If you select Moving Average, indicate how many periods Excel should use in its calculations in the adjacent Period box.

After you've indicated the type of trend/regression line Excel should draw, select the Options tab if you want to name this trendline specification. As long as you're not working with a moving average trendline, you can also use the Forward and Backward spinners to extrapolate the trendline. For linear, polynomial, and exponential trendlines, you can set the y-intercept in the Set Intercept edit box. And, if you want, you can display the regression equation and the R-squared value alongside the trendline plot.

Adding Error Bars

When you are charting statistical or experimental data, it is often helpful to show the confidence level of your data. Excel's error-bar feature makes this easy. To add error bars to a data series in an area, bar, column, line, or xy chart, select the data series, choose Selected Data Series from the Format menu, and then click the Y Error Bars tab. Excel then displays the Error Bars dialog box shown in Figure 23-13.

FIGURE 23-13.
Use error bars to illustrate the potential error for each data point in a series.

Error bars can be shown as the actual data point value plus some amount, minus some amount, or both plus and minus some amount. Use the options in the Display section to indicate which of these error bar styles you want. Use the Error Amount options-Fixed Value, Percentage (of the data point value), Standard Deviation(s), Standard Error, and Custom (an amount specified manually)-to calculate the amount depicted by the error bar.

Dragging Chart Markers to Change Data

Because charts are linked to worksheet cells, you can use them to construct visual "what-if" scenarios with your data. For example, if you set up a break-even analysis in a worksheet and then plot fixed costs, variable costs, total costs, and gross margin in a chart, you can change fixed-cost assumptions in the worksheet and immediately see the effect on the gross-margin line in the chart.

You can also reverse this process in two-dimensional bar, column, line, and xy charts. You can drag chart data markers-including picture markers-upward or downward and have Excel adjust the underlying worksheet. In the break-even analysis, for example, you can drag the chart's gross-margin line upward so it crosses 0 at a different point and then find out on the worksheet exactly how much you would need to reduce your fixed costs to achieve the increase in profit. This process is called graphical goal-seeking.

Let's look at a simple example. Suppose that, after examining the chart in Figure 23-14, you decide that the fourth-quarter data doesn't look quite right. Product B appears to lag behind Products A and C in that quarter, when in fact you expected B to do better than C-if not quite as well as A. Instead of going back to the worksheet and entering new values, you can change the chart directly.

FIGURE 23-14.
This simple chart demonstrates Excel's graphical goal-seeking capabilities.

To adjust the worksheet's values from the chart, follow these steps:

1. Select the data marker you want to adjust. (You may have to click twice.) Black handles appear on the marker. In our example, you would select the fourth-quarter marker for Product B.

2. Point to the top center black handle. The pointer becomes a double-headed arrow. Drag the marker upward until the marker's height is somewhere between that of Product A and Product C. As you drag Product B's marker, your screen looks like Figure 23-15.

   
   

   FIGURE 23-15.
   As you drag a marker, Excel displays the marker's current value. Here we've dragged the marker from its original value to 126.9. If we release the mouse button, Excel changes the associated worksheet cell to 126.9.

3. Release the mouse button when the data marker reflects the value you want. Excel redraws the chart and updates the underlying worksheet cell.

In this example, all Excel must do to make the chart display the new value is modify one cell in the underlying worksheet. If that cell contains a formula instead of a constant, however, the situation is different. Excel understands then that you want the result of the formula producing the data point value to change, but you don't want to replace the formula with a constant. To clarify your intentions, Excel presents the Goal Seek dialog box-the same dialog box you see when you use the Goal Seek command on the Tools menu. In the Goal Seek dialog box, specify what value the formula should return and which formula input the Goal Seek command should adjust.