3

this is the effect I'm after

I'd like to make a multi-series bubble chart from 17 years of sporadic data with Year on the Y axis and Month+Day on the X axis and the bubble size corresponding to each data point's value.

I've entered the data into Sheets with column A as the date (January-1 ...December-31) and columns B to R as the years (in this case, 2007 to 2023).

Here's a link to the Google Sheets data: https://docs.google.com/spreadsheets/d/1cVhKlkih0pYdKLKAZRQvj1QgNgTzEO3Vjv4yRy8E1tM/edit?usp=sharing

Improve this question
2
  • You can get a bubble chart in the layout you've requested, but it's tricky (and perhaps impossible) to get the bubbles to look the way you want because you can't directly control the scaling of the bubbles, which results in the largest bubbles overlapping multiple years, and the smallest bubbles being bigger than you'd like...
    – The God of Biscuits
    Sep 20 at 13:39
  • I'm still interested in knowing how to get the bubble chart layout I was originally after. If needs be, I can modify the data to fix the scaling problem.
    – flyhigher
    Sep 20 at 16:36

1 Answer 1

Reset to default
1

As I've indicated in the comments, I don't think you can easily get a bubble chart that meets the requirements in the picture because of scaling issues, however to get the general layout requested, your source data needs to reformatted from its current 'crosstab' format to a 'flat' format:

Month&Day Year 'Series' Value
1 January 2020 1 9
... ... ... ...

'Series' is a required column even though you are not plotting multiple series, so just give every data point the same series name (e.g. 1 as I've done in the table). Only include those month&day/year combos which actually have a value (i.e. filter out all the blanks), otherwise Sheets tries to plot the zeros as well. I haven't been able to get the X-axis formatting to quite match your requirements either, but haven't really looked into it that hard. Good luck!

Improve this answer
1
  • If any information in the comments is relevant be sure to include it in the answer. Comments are transient and shouldn't be expect to survive beyond their immediate use.
    – Blindspots ♦
    Sep 20 at 19:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.