Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 54 Three Quick Ways to Update Your Charts

Although creating new charts is wonderful,
updating them to reflect new circumstances can take a lot of effort.
You can reduce the amount of work needed to change the data used by a
chart in a number of ways.

Using Drag-and-Drop

You can add data to an existing series or
create a completely new data series by simply dragging and dropping
data onto a chart. Excel will try to decide how to treat the data,
which might mean adding to any existing data series when you really
wanted a new series. You can, however, get Excel to display a dialog
box, which lets you to determine which action you want to use.

Try setting up some data such as that shown in Figure 5-13.

Figure 5-13. Data for clustered column chart

Using the Chart Wizard, create a
clustered column chart for the range $A$1:$D$5 only, producing the
result shown in Figure 5-14.

Figure 5-14. Clustered column chart created with range A1:D5 only

Highlight the range A6:D6, right-click the selection border, press
the right mouse button, and drag onto the chart. When you release the
mouse button, the Paste Special menu will pop up, as shown in Figure 5-15.

Figure 5-15. Clustered column chart showing Paste Special dialog

Select the Columns option and
then click OK. This will add the May data series to the chart, as
shown in Figure 5-16.

Figure 5-16. Clustered column chart with May data series added

The Paste Special dialog takes care of most of the actions you need
in order to use this nifty trick.

Using the Formula Bar

You
also can update your chart by using the Formula bar. When you select
a chart and click a data series within it, look at the Formula bar
and you will see the formula Excel uses for the data series.

Called a
SERIES function, the formula generally uses four
arguments, although a bubble chart requires an additional fifth
argument for [Size].

The syntax (or order of structure) of the SERIES
function is as follows:

=SERIES( [Name] , [X Values] , [Y Values] , [Plot Order] )

So, a valid SERIES function could appear as
follows, and as shown in Figure 5-17:

=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$5,Sheet1!$B$2:$B$5,1)

Figure 5-17. A clustered column chart with the Formula bar highlighted

In terms of Figure 5-17, the first part of the
reference, Sheet1!$B$1, refers to the name, or the
chart title, which is 2004. The second part of the reference,
Sheet1!$A$2:$A$5, refers to the X values, which in
this case are the Months. The third part of the reference,
Sheet1!$B$2:$B$5, refers to the Y values, which
are the values 7.43, 15, 21.3, and 11.6. Finally, the last part of
the formula, the 1, refers to the plot order, or
the order of the series. In this case, there is only one series, so
this series can only take the value 1. If there
were more than one series, the first series would take the number
1, the second series would take the number
2, and so forth.

To make changes to the chart, simply alter
the cell references in the Formula bar.

Besides using cell references, you can
enter explicit values, known as array constants, into your charts
(see "About Array Formulas and Array
Constants" in Excel Help for full details). To
achieve this, add {} (curly brackets) around
the X and Y values, as shown in the following formula:

=SERIES("My Bar",{"A","B","C","D"},{1,2,3,4},1)

In the
previous SERIES formula, A, B, C, and D would be
the X values, while 1, 2, 3, and 4 would be their corresponding Y
values.

By using this method, you can create or update a chart without having
to store data in cells.

Dragging the Bounding Area

If your chart data contains continuous cell
references, you can easily extend or reduce the data in the series by
dragging the bounding area to a desired point.
Slowly click the data series you want to
either extend or reduce. After two slow clicks, black square(s) (also
called handles) will appear around the outside of the series (or in
the middle if you're using a line chart). All you
need to do is click a square and drag the bounding area in the
direction required, as shown in Figure 5-18.

Figure 5-18. A highlighted bounding area for a chart series

If
you either extend or reduce the series data, as shown in Figure 5-19, the original source data as well as the axis
labels (if set to Auto) also will alter to reflect the changes you
made.

Figure 5-19. A highlighted bounding area for a chart series after it is expanded

This is great for testing scenarios, when you want to explore what
the results of different data sets will be.

Andy Pope