Updated:Scatterplot Labeling and Pivot Tables

**woontime** · 06-23-2011, 05:10 AM

Hi all,

So I am trying to create a bubble chart using pivot tables. I understand that you must use a regular chart and manually input the data as Excel doesn't allow the user to create bubble or scatterplots with pivot tables. But as you can see in the attachment it would take too much time to add each individual unit (FC-XXX) into the chart.

Is there a way to automatically input the pivot chart data into a bubble or scatterplot chart by any means? Probably some Macro?

Please help I'm so frustrated!

UPDATE:

I was able to format it using a scatterplot to get the result I want. Next problem is there a way I can label each point so that i can identify each unit by simply choosing the point?

This is for a dashboard btw

**teylyn** · 06-23-2011, 05:25 AM

Hello,

the screenshot you attached seems to highlight one of the major reasons not to use bubble charts with 3D effects.

Why use a bubble chart at all in this case? The bubbles are so big that they overlap and obscure each other. Also, they are all the same size, since you don't seem to have a data column for the bubble size. So, this is just a plain XY chart with oversized 3D data markers. Added value? None. Readability? Near zero.

Jon Peltier has info on how to build a normal chart from a Pivot Table here http://peltiertech.com/Excel/Pivots/pivotcharts.htm

**woontime** · 06-23-2011, 05:27 AM

Thanks teylyn

I've updated my 1st post as I was able to fix the problem partially by switching to a scatterplot.

**woontime** · 06-23-2011, 11:22 AM

still can't figure it out

**teylyn** · 06-24-2011, 06:14 AM

You could use dynamic ranges as the source for the chart.

The pivot table complicates things, since it seems that you use the + buttons to drill down into data. That will be very difficult to automate. If you used a page filter instead, the dynamic range name could always start at the same cell and simply count the rows to include in the chart. Change the page filter to a different value and the data will still start in the same cell. But with the dynamic expanding of individual items, pinning down the data source will be really hard.