Pivot Scattergraph

[Gblack686]

Hi All,

I've been struggling to convert my Excel line graph to a dynamic pivot chart. The formatting should look like it does in sheet "Excel Chart".

When I convert the data to a pivot table I'm not sure which fields to drag where... or if it's even possible with the arrangement of my data table.

I'd like to keep my data in the given format (rows exported from a CSV file).

Thanks in advance,

Greg

[MrShorty]

In our older versions of Excel (still may be true for the newest version, I don't know), you cannot make pivot chart be a scatter chart. To workaround this limitation, you must create a regular chart and use the ranges from the pivot table as the source data. Instructions here: https://peltiertech.com/regular-char...-pivot-tables/

[Gblack686]

My apologies, I believe it is actually a line chart iwth markers. We just refer to it as a "scatterplot" in the office.

[MrShorty]

You would know better than me, but the chart in the "Excel Chart" tab of your sample says it is an XY scatter chart and not a line chart. Your sample chart also suggests that you are trying to plot base price (a number) against home size (in square feet -- another number), so a scatter chart seems like it would be the most meaningful. Is your sample file accurate?

If there is any confusion about the differences between line and scatter charts, I recommend https://peltiertech.com/line-charts-vs-xy-charts/ even though it was written back when 2002/2003 were the main Excel versions, the main ideas haven't changed.

[Gblack686]

Ah you're absolutely right, we use the XY scatter format.

So in this instance, dynamic pivot tables are not my friend...

The only thing I can think of is pasting the data values into cells that are already being referenced in the chart, hence overwriting and updating them with every new CSV dump.

So that begs the question, is there an easier way to select that data rather than going in to each row and selecting the X and Y values for the 1000 rows that I'll be downloading? Will Excel even allow that much data in a chart? The idea is to then filter the table by market and have the chart adjust automatically.

[MrShorty]

Did you read through Jon's tutorial that I linked to? I don't see any reason to need to copy data from the pivot table into a new range -- just reference the pivot table.

In your sample file, I followed Jon's instructions for creating a scatter chart using B5:B30 as the X values and C5:C30 as the Y values on the Pivot Chart tab. I got rid of the grand total row. Then I could filter the table by row labels as much as I wanted and the scatter chart would show only the filtered data.

My old version of Excel will handle something like 32k data points in a chart. I believe your newer version has no real limit (other than available memory), so it should be able to handle 1000 or 2000 data points.