Creating a chart based on filtered results

I have applied an autofilter to a table and I want to now chart the contents
of this table.

My chart's data source is automatically set for 20 lines...however, after
the filter there are now blank lines that are included in the data source. I
don't want to continually adjust the data source area of my chart.

How can I chart the autofilter results without the blank lines?

You mean the source is set to 20 rows, or the source includes data for
20 series?

Either way, if the chart's default behavior hasn't changed, it does not
plot the hidden rows of an autofiltered list. And even if the default
were changed (chart selected, Tools menu, Options, Chart tab), the
autofilter doesn't change the values to zero, it simply hides them.

So the question is, how does the chart's data always refer to the first
20 visible rows?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Stretch wrote:

I have applied an autofilter to a table and I want to now chart the contents
of this table.

My chart's data source is automatically set for 20 lines...however, after
the filter there are now blank lines that are included in the data source. I
don't want to continually adjust the data source area of my chart.

How can I chart the autofilter results without the blank lines?

I filter the data in Spreadsheet #1, and then cut & paste the filtered
results into Spreadsheet #2, which includes some charts to graphically show
the results.

Sometimes, depending upon the filter results, the chart's data source will
be 5 rows, sometimes it will be 15 rows, it depends...

So I guess my real question is: how can I easily adjust the chart's data
source range to only include the lines I cut and paste in? I would rather not
go into each chart and manually adjust the data source every time. I tried
to use a macro to highlight the cells (click on upper left hand corner of
range, then CNTRL + down arrow to the bottom entry of the list), but the
macro wouldn't let me use the keyboard...only the mouse.

"Jon Peltier" wrote:

You mean the source is set to 20 rows, or the source includes data for
20 series?

Either way, if the chart's default behavior hasn't changed, it does not
plot the hidden rows of an autofiltered list. And even if the default
were changed (chart selected, Tools menu, Options, Chart tab), the
autofilter doesn't change the values to zero, it simply hides them.

So the question is, how does the chart's data always refer to the first
20 visible rows?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Stretch wrote:

I have applied an autofilter to a table and I want to now chart the contents
of this table.

My chart's data source is automatically set for 20 lines...however, after
the filter there are now blank lines that are included in the data source. I
don't want to continually adjust the data source area of my chart.

How can I chart the autofilter results without the blank lines?

You can follow one of two paths. You could use the list itself as your
source data, which means the charts will chart just as many points as
are visible. Or you could set up dynamic charts, based on dynamic ranges
that resize based on the size of the data range. I have some examples
and links to many other examples he

http://peltiertech.com/Excel/Charts/Dynamics.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Stretch wrote:
I filter the data in Spreadsheet #1, and then cut & paste the filtered
results into Spreadsheet #2, which includes some charts to graphically show
the results.

Sometimes, depending upon the filter results, the chart's data source will
be 5 rows, sometimes it will be 15 rows, it depends...

So I guess my real question is: how can I easily adjust the chart's data
source range to only include the lines I cut and paste in? I would rather not
go into each chart and manually adjust the data source every time. I tried
to use a macro to highlight the cells (click on upper left hand corner of
range, then CNTRL + down arrow to the bottom entry of the list), but the
macro wouldn't let me use the keyboard...only the mouse.

"Jon Peltier" wrote:


You mean the source is set to 20 rows, or the source includes data for
20 series?

Either way, if the chart's default behavior hasn't changed, it does not
plot the hidden rows of an autofiltered list. And even if the default
were changed (chart selected, Tools menu, Options, Chart tab), the
autofilter doesn't change the values to zero, it simply hides them.

So the question is, how does the chart's data always refer to the first
20 visible rows?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Stretch wrote:


I have applied an autofilter to a table and I want to now chart the contents
of this table.

My chart's data source is automatically set for 20 lines...however, after
the filter there are now blank lines that are included in the data source. I
don't want to continually adjust the data source area of my chart.

How can I chart the autofilter results without the blank lines?