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.
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.
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.
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.
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.