3

enter image description here

No, excel. I can, it's just you that won't let me. *copy/paste, create chart, done*.

Is there a way to bypass this check? Copy/paste works, but I'd like a scatter chart from a pivot table to automatically update when I change the source data.

  • Are you saying the error message is incorrect and it creates the chart as expected? – Dave Sep 16 '14 at 7:28
  • 1
    @Dave No, I can't create the chart and I can't think of a sane reason why I shouldn't be able to. Copying the same data to another sheet and creating data from that works just fine, but I'd like a more automatic method. – jozxyqk Sep 16 '14 at 7:37
  • I know not ideal, but as a work around, maybe AutoHotKey so it can do a lot of the copying pasting via a single command? – Dave Sep 16 '14 at 7:41
4

Select a blank cell a few rows or columns away from the pivot table.

Insert an XY Scatter chart, which will be blank, because no data was selected.

Right click the chart, choose Edit Data.

Add a new series, Edit this series, select the ranges with the series name, X Values, and Y Values.

Click OK a couple times to return to Excel.

This chart should change if the pivot table data is refreshed, though it will not keep up if the pivot table size changes.


You can use named ranges to ensure the graph updates properly if the pivot table size changes.

| improve this answer | |
0

If you want to make the graph able to change with the pivot table filters, you can create a pivot table with the 2 columns of data (Let's say Column A has the label, and B and C have the data), and then reference those two columns in another two columns( ex. make the cells in Column E, for example make E2 contain =B2 and then make the cells in Column F, for example make F2 contian =C2).

This will give you two columns of data, and you can use the instructions from the above post to make it into a scatterplot.

Filtering Adjustment: Let's say I have a pivot table with a 100 rows. Make sure these reference columns (Columns E and F) drag the formulas down to 100. Now, once you filter, using a slicer, you'll notice only some of Column E and F are populated, and the are zero (because the filtered pivot table doesn't go that far down.

The problem? Now you're table will have a bunch of Points at (0,0) which might mess up trendlines, etc.

So, to fix this, change the formulas in column e and f to, for example for B2, make D2 contain =if(B2>0,B2,NA())

The cool thing about NA() is that it is not included in the graph. Thus, you have a pivot table you can filter and the scatterplot graph automatically gets updated without any unnecessary zeros.

| improve this answer | |
0

XY scatter chart requires that both X and Y axes are numeric values. Generally X and Y axes of pivot table are not numeric, they are grouped by selected fields. That is the reason XY scatter chart can not associated with pivot table.

If you want to show dot like featured chart associated with pivot table, you should choose chart type to line with markers. Then set line to none for each series in the chart.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.