Back
Excel

Scatter charts with PowerPivot

Rob over at http://powerpivotpro.com has a interesting post on scatter charts and PowerPivot:

At right is a list of all chart types in Excel.  But not all of them are supported as PivotCharts.  Try to use XY (Scatter), Bubble, or Stock as a PivotChart, and you’ll get an alert saying you cannot create it with pivot data.

So, do you give up?

Nope.  Remember the post where I converted a PivotTable to Cube Formulas using that button on the ribbon?  This is a wonderful opportunity to use that feature in an unexpected way.

Once it’s converted to formulas, you can create any chart type you want against it, no restrictions.  It’s just regular old cells now.

You can read more of Rob’s post here:

http://powerpivotpro.com/2010/02/05/powerpivot-data-in-scatter-charts-etc/

As Colin states in one of the comments to the post, there’s multiple ways to get a scatter chart on PivotTable data.  From creating formulas based on the PivotTable to inserting a blank chart and using the add series dialog to select the cells.  Attached to this post is a simple example.