Controlling a data series by using Autofiltering

Figure 18-21 shows a chart that displays daily data for 365 days. What if you only want to plot, say, the data for February? You could, of course, redefine the chart's data range. Or, you could take advantage of Excel's AutoFilter command.

Figure 18-21: You can use Excel's AutoFilter feature to plot only a subset of the data.

By default, a chart does not display data that's hidden. Because Excel's AutoFilter feature works by hiding rows that don't meet your criteria, it's a simple solution. Choose Data ^ Filter ^ AutoFilter to turn on the AutoFilter mode. Each row heading in the filtered list displays a drop-down arrow. Click the arrow and select Custom from the list. Then enter filter criteria that will select the dates that you want to plot. The setting shown in Figure 18-22, for example, hides all rows except those that have a date in February.

Custom AutoFilter

I Show rows where:

1 1

iii is greater than or equal to

▼ ¡[12/1/2003

MW

And C Or

||i5 less than

V] (3/1/2003

m

Use ? to represent any single character Use * to represent any series of characters

Use ? to represent any single character Use * to represent any series of characters

Figure 18-22: Use the Custom AutoFilter dialog box to filter a list.

This workbook is available on the companion CD-ROM.

The resulting chart is shown in Figure 18-23.

autofilter chart.xls

A

B

c I

D I E F G H I I

j

-JT

1

Date TI

Calls <r|

33

02/01/03

428

34

02/02/03

438

35

02/03/Ü3

428

600 -

36

02/04/03

429

37

02/05/03

413

500 -

38

02/06/03

408

39

02/07/03

403

40

02/08/Ü3

417

400 -

41

02/09/03

409

42

02/10/03

400

300 -

43

02/11/03

422

44

02/12/03

439

45

02/13/03

428

200 -

46

02/14/03

454

47

02/15/Ü3

471

02/16/03

100 -

"49"

02/17/03

492

50

02/18/03

486

0 -

51

02/19/03

465

-

52

02/20/03

473

53

02/21/03

497

• ir

54

m mm?

4PR

► M |\ Sheet 1/

M I

Figure 18-23: Only visible cells are displayed in a chart.

Figure 18-23: Only visible cells are displayed in a chart.

If this technique doesn't seem to be working, you need to change a setting for the chart. Activate the chart and then choose Tools ^ Options. In the Options dialog box, click the Chart tab and place a check mark next to Plot Visible Cells Only. Also, to ensure that the chart doesn't disappear when its rows are hidden, set its positioning to Don't Move or Size with Cells. Choose the Format ^ Selected Chart Area command to change this setting.

0 0

Post a comment