Tables and X Y Scatter Charts

[sherriwatkins]

I need some help! I am usually really fluent in all things Excel, but this has me stumped. I have a table with over 1350 rows of data. I am trying to create an XY scatter chart that will allow me to chart only the information that has been sorted in the table. This table has 6 sets of data for the Y and 6 sets of data for the X axis. Y is the power and X is the flow (see below). I know I can manually selecteach row to put in the chart but is there a way to have Excel do this for me since there is so much data? I need to beable to create a master scatter chart that will take my raw data and as Ifilter rows out, will automatically adjust the chart. I hope this makes sense.

[TABLE="width: 686"]
<colgroup><col width="92" style="width: 69pt; mso-width-source: userset; mso-width-alt: 3364;"> <col width="102" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3730;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;"> <col width="37" style="width: 28pt; mso-width-source: userset; mso-width-alt: 1353;"> <col width="40" style="width: 30pt;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;"> <col width="40" style="width: 30pt;" span="13"> <tbody>[TR]
[TD="width: 92, bgcolor: transparent"]Manufacturer[/TD]
[TD="width: 102, bgcolor: transparent"]Model Number[/TD]
[TD="width: 61, bgcolor: transparent"]Date[/TD]
[TD="width: 37, bgcolor: transparent"]Type[/TD]
[TD="width: 40, bgcolor: transparent"]Stage[/TD]
[TD="width: 61, bgcolor: transparent"]Pressure[/TD]
[TD="width: 40, bgcolor: transparent"]HP[/TD]
[TD="width: 240, bgcolor: transparent, colspan: 6"]Flow[/TD]
[TD="width: 240, bgcolor: transparent, colspan: 6"]Power[/TD]
[/TR]
[TR]
[TD="bgcolor: #E2EFDA"]AC[/TD]
[TD="bgcolor: #E2EFDA"]G36[/TD]
[TD="bgcolor: #E2EFDA"]11/20/13[/TD]
[TD="bgcolor: #E2EFDA"]A[/TD]
[TD="bgcolor: #E2EFDA"]1[/TD]
[TD="bgcolor: #E2EFDA"]102[/TD]
[TD="bgcolor: #E2EFDA"]50[/TD]
[TD="bgcolor: #E2EFDA"]240.0[/TD]
[TD="bgcolor: #E2EFDA"]220.0[/TD]
[TD="bgcolor: #E2EFDA"]150.0[/TD]
[TD="bgcolor: #E2EFDA"]100.0[/TD]
[TD="bgcolor: #E2EFDA"]50.0[/TD]
[TD="bgcolor: #E2EFDA"]30.0[/TD]
[TD="bgcolor: #E2EFDA"]21.00[/TD]
[TD="bgcolor: #E2EFDA"]20.00[/TD]
[TD="bgcolor: #E2EFDA"]19.00[/TD]
[TD="bgcolor: #E2EFDA"]19.00[/TD]
[TD="bgcolor: #E2EFDA"]25.00[/TD]
[TD="bgcolor: #E2EFDA"]27.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AC[/TD]
[TD="bgcolor: transparent"]G37[/TD]
[TD="bgcolor: transparent"]1/16/14[/TD]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]115[/TD]
[TD="bgcolor: transparent"]50[/TD]
[TD="bgcolor: transparent"]235.0[/TD]
[TD="bgcolor: transparent"]220.0[/TD]
[TD="bgcolor: transparent"]150.0[/TD]
[TD="bgcolor: transparent"]100.0[/TD]
[TD="bgcolor: transparent"]45.0[/TD]
[TD="bgcolor: transparent"]35.0[/TD]
[TD="bgcolor: transparent"]21.00[/TD]
[TD="bgcolor: transparent"]21.00[/TD]
[TD="bgcolor: transparent"]19.00[/TD]
[TD="bgcolor: transparent"]19.00[/TD]
[TD="bgcolor: transparent"]24.00[/TD]
[TD="bgcolor: transparent"]25.00[/TD]
[/TR]
[TR]
[TD="bgcolor: #E2EFDA"]BC[/TD]
[TD="bgcolor: #E2EFDA"]M23[/TD]
[TD="bgcolor: #E2EFDA"]1/16/14[/TD]
[TD="bgcolor: #E2EFDA"]W[/TD]
[TD="bgcolor: #E2EFDA"]1[/TD]
[TD="bgcolor: #E2EFDA"]125[/TD]
[TD="bgcolor: #E2EFDA"]50[/TD]
[TD="bgcolor: #E2EFDA"]225.0[/TD]
[TD="bgcolor: #E2EFDA"]215.0[/TD]
[TD="bgcolor: #E2EFDA"]150.0[/TD]
[TD="bgcolor: #E2EFDA"]95.0[/TD]
[TD="bgcolor: #E2EFDA"]35.0[/TD]
[TD="bgcolor: #E2EFDA"]30.0[/TD]
[TD="bgcolor: #E2EFDA"]22.00[/TD]
[TD="bgcolor: #E2EFDA"]21.00[/TD]
[TD="bgcolor: #E2EFDA"]20.00[/TD]
[TD="bgcolor: #E2EFDA"]20.00[/TD]
[TD="bgcolor: #E2EFDA"]26.00[/TD]
[TD="bgcolor: #E2EFDA"]26.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]BC[/TD]
[TD="bgcolor: transparent"]M24[/TD]
[TD="bgcolor: transparent"]7/16/14[/TD]
[TD="bgcolor: transparent"]W[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]102[/TD]
[TD="bgcolor: transparent"]40[/TD]
[TD="bgcolor: transparent"]205.0[/TD]
[TD="bgcolor: transparent"]185.0[/TD]
[TD="bgcolor: transparent"]150.0[/TD]
[TD="bgcolor: transparent"]95.0[/TD]
[TD="bgcolor: transparent"]40.0[/TD]
[TD="bgcolor: transparent"]30.0[/TD]
[TD="bgcolor: transparent"]19.00[/TD]
[TD="bgcolor: transparent"]18.00[/TD]
[TD="bgcolor: transparent"]18.00[/TD]
[TD="bgcolor: transparent"]17.00[/TD]
[TD="bgcolor: transparent"]21.00[/TD]
[TD="bgcolor: transparent"]22.00[/TD]
[/TR]
</tbody>[/TABLE]

Thanks,
Sherri

 

[Worf]

Hi Sherri

If you filter a chart’s source table or manually hide some rows, the corresponding data disappears from the chart. Does this solve your problem?
For a more refined approach, we can use VBA.
Note that the maximum number of data series per chart is 255.

 

[sherriwatkins]

@ Worf Thanks, Is there an easy way to add the data to the chart rather than one at a time? Since there are over 1300 rows of data in this one file (I have 4 others), it would take months to enter each row into the chart.

Thanks!
Sherri

 

[Worf]

This can be automated with VBA. The code below adds only the filtered rows from the source table to the chart, what do you think?

[FONT=Courier New][SIZE=3][COLOR=#000000] 
Sub Sherri()
Dim t As ListObject, r As Range, i%, ch As ChartObject, j%
Set ch = ActiveSheet.ChartObjects("chart 1")                ' your chart name here
Set t = ActiveSheet.ListObjects("table3")                   ' your table name here
Set r = t.DataBodyRange.SpecialCells(xlCellTypeVisible)
Do While ch.Chart.SeriesCollection.Count > 0
    ch.Chart.SeriesCollection(ch.Chart.SeriesCollection.Count).Delete
Loop
ch.Chart.HasLegend = True
For i = 1 To r.Areas.Count
    For j = 1 To r.Areas(i).Rows.Count
        With ch.Chart.SeriesCollection.NewSeries
            .XValues = r.Areas(i).Rows(j).Cells(1, 8).Resize(1, 6)  ' columns H-M
            .Values = r.Areas(i).Rows(j).Cells(1, 14).Resize(1, 6)  ' columns N-S
            .Name = r.Areas(i).Rows(j).Cells(1, 2)
        End With
Next j, i
End Sub<strike></strike>
[/COLOR][/SIZE][/FONT]
<strike></strike>