Excel Magic Trick 1337: No X-Y Scatter Chart From PivotTable!?!? Use Power Query Instead!!!

Download Files:

If you try to make X-Y Scatter Chart from PivotTable is will NOT work!!!!! See how to use Power Query to create the same output as a PivotTable in order to make an X-Y Scatter Chart:
1. (00:15) Introduction
2. (00:47) PivotTable to Adding Units by Day. But when we try to create an X Y Scatter Chart from PivotTable, we will get the error “You can’t create this chart type with data inside a PivotTable. Please select a different chart type, or copy the data outside the PivotTable”.
3. (02:35) Use Power Query to build PivotTable output, and then the X Y Scatter Chart will work perfectly!!
4. (05:30) Build X-Y Scatter Chart with Proper Labels.
5. (07:14) Summary

[katemckeown]

I am trying to crunch a VERY large data set from multiple text files and was struggling until I watched your video. Thank you so much!

[muhammadfathi]

Many thanks, You are really a source of learning

[bill]

Thanks for the consistently high-quality help, and thanks for selling me my new favorite coffee mug from your merch store ;).

[franckquetier]

Very helpfull - I come from an Access age, now understanding why embedded into Excel. Thanks

[Centola]

What if I don't want to sum up all the sales from one day, or generally want to keep x values the same while varying the y values. this is a true xy scatterplot. to make this table you summed data, but many xy charts, as commented elsewhere here, plot raw individual data points. this was not done here.

[Pegasus]

It's crazy how much you know and how much you produce

[TheJoshtheboss]

Or you could just workaround inserting an empty Scatter plot, which will allow you to select data from the Pivot table into series 😂.

You can use offset function to make it more dynamic.

But don't get me wrong, I love power Power Query, and certainly works for this too.

[CocoaComa]

You made it very easy to understand, and I learned a lot. It helped me a bunch! Thank you so much for making this!

[himanshudalai]

Excellent one !! Thank you Mike for this awesome video !!

[tylerdavis]

Good stuff - only recommendation is to add step in query to sort by date so your chart is chronological

[ripudamansingh]

How do u know, what I'm looking for. Boom ur educational video will appear with solutions.. Pl teach us sunburn chart as Im currently working on Consolidation of monthly spent from many locations..

[nck]

great video, great detail. very clear

[arindambhattacharya]

Not sure if this is a scatter plot example - it is more of a line chart. Scatter plot shows relationships between two variables

[jasonwoodward]

Thanks for this. What happens if you need a filter on the chart?

[CrimsonTuber]

Nice trick indeed. Wonder why excel doesn't allow scatter graph directly from pivot table.

[GeorgeRon]

Hi mate, great lesson. But how do you get those filters back on the graph like a pivot chart can place?

[eriol]

thank you for this cool trick, but it's amazing microsoft disables this feature on pivot table. for what reason I wonder?

[harvaradsinha]

I have a doubt - I am trying to make a scatter plot where the x and y axis are either mean or average of filtered column values. Also, i want the values in 4 columns to be of different colors. Is there a way to do that in Power Query scatter chart?

[carlosmorales]

I´m trying to create an XY scatter from a data base, but with Power Query I can´t edit the data in the table with slicers, any idea how can this be possible?

[warrenmckechnie]

Hi, I have have a bunch of data which I need to plot between the dates (X) and hours (Y) to show a visual of the busiest times. Would this be possible using a Scatter Chart?