Hack 54 Three Quick Ways to Update Your Charts

   

Hack 54 Three Quick Ways to Update Your Charts

figs/moderate.gif figs/hack54.gif

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
figs/exhk_0513.gif

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
figs/exhk_0514.gif

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
figs/exhk_0515.gif

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
figs/exhk_0516.gif

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,Sheet1!$A:$A,Sheet1!$B:$B,1) 
Figure 5-17. A clustered column chart with the Formula bar highlighted
figs/exhk_0517.gif

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
figs/exhk_0518.gif

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
figs/exhk_0519.gif

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

Andy Pope



Excel Hacks
Excel Hacks: Tips & Tools for Streamlining Your Spreadsheets
ISBN: 0596528345
EAN: 2147483647
Year: 2003
Pages: 136

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net