CODEWITHSUNDEEP
excelvba
antoine
antoine

Reputation: 115

Sheet to graph, need of a macro?

I have this data in a spreadsheet (picture 1) et I'd like to know how can I make it look like the data graph in the second picture. I tried to insert a chart but I can't find a way to make it look similar to the picture 2 graph. Do I need a macro to do this ? Thanks.

excel_1 excel_2

Upvotes: 2

Views: 49

Answers (1)

Aprillion
Aprillion

Reputation: 22324

No need for a macro.

  • add an extra helper column with values 1, 2, 3 ... n (even for the empty rows)
  • insert an empty Scatter chart type
  • via Select Data > Add, add 4 series, each column in turn will be Series X values, while the new helper column will be always the Series Y values
  • adjust formatting as needed, e.g.:
    • for the big blue separators you will need to change vertical axis major units to 5 and then format major gridlines line style to be very thick and color to be solid blue with transparency, then delete the vertical axis itself to hide the helper numbers
    • format data series > marker options to change the arbitrary shapes to circles - but you might want to preserve the different shapes and use a transparent color to make the overlapping values visible

There is no way to automatically make the shapes move a little to avoid overlapping, but you can create 4 helper columns instead of 1 with slighly different values (1.1, 2.1, 3.1 ... n+ 0.1 and so on) to work around this limitation.

Upvotes: 1

Related Questions