Ask Your Question

How to create bubble chart from pivot table? [closed]

asked 2013-08-21 11:01:37 +0100

rveerd gravatar image

updated 2013-10-22 19:20:47 +0100

manj_k gravatar image

I know how to create a bubble chart when I have columns seperate columns with the X and Y coordinates and the bubble sizes.

However I now have created a pivot table and I want to create a bubble chart which uses the column labels for the X axis, the row labels for the Y axis and the pivot table values for the bubble sizes.

Any ideas on how to achieve this?

Edit: I want to achieve something similar to GitHub punch cards.

Edit: I cannot provide my original file but my pivot table looks something like this:

Items sold
     Hour of the day
User 09 10 11 12 13 14 15 16 17
John  5  7 12 15 21  7  9 12  2
Jane  0  2  7 23 31 16 12  7  7

And then I need a bubble graph with Hour of the day on the X axis, User on the Y axis and Items sold as the bubble size.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-11-07 10:59:12.155171


Can you provide access to your file? --- Please consider not to expose confidential data; this is a public site.

ROSt52 gravatar imageROSt52 ( 2013-08-21 15:10:41 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2013-08-26 17:07:27 +0100

rveerd gravatar image

I found a way to do it manually with the following recipe.

  1. Select the data and row headers and copy to clipboard.
  2. Paste special to another sheet and select transpose data option.
  3. First column (hour of the day) becomes the X coordinate for all data series.
  4. Add dummy columns for the Y coordinate before each users data columns.
  5. Select all and create a new bubble chart.

It is probably possible to create a macro for this.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2013-08-21 11:01:37 +0100

Seen: 7,564 times

Last updated: Aug 26 '13