If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
cannot save form after creating based on a specific table | jes | Using Forms | 1 | June 14th, 2004 12:29 PM |
Problems creating Line-Column chart (Excel 2000) | Trell | Charts and Charting | 1 | April 17th, 2004 12:33 PM |
2 Axis Pivot Table Based Chart Problem | Ted Pearlman | Charts and Charting | 1 | October 3rd, 2003 11:16 PM |
Styles for chart | Debra Dalgleish | Charts and Charting | 1 | October 3rd, 2003 12:27 PM |