Need help creating a chart

jfish1288

Board Regular
Joined
Jun 22, 2011
Messages
116
I am not sure what to call the type of chart I want to create, which has made it very difficult to find any solutions via searches. I am hoping someone here can point me in the right direction.

I have data in the following format:
Book3
ABCDE
22ItemTypeBrandSizePrice
23Item1Type_ABrand 18$499
24Item2Type_ABrand 28$99
25Item3Type_ABrand 38$99
26Item4Type_ABrand 29$99
27Item5Type_ABrand 39$199
28Item6Type_ABrand 110$599
29Item7Type_ABrand 210$99
30Item8Type_BBrand 18$499
31Item9Type_BBrand 39$299
32Item10Type_BBrand 110$599
33Item11Type CBrand 18$499
34Item12Type CBrand 28$299
35Item13Type_CBrand 38$399
36Item14Type_CBrand 29$299
37Item15Type_CBrand 39$499
38Item16Type_CBrand 110$599
39Item17Type_CBrand 210$299
40Item18Type_CBrand 310$599
Sheet1


And I would like to create a chart similar to the one I drew by hand below.
I want to plot all of the items by size (x-axis) and price (y-axis), and I want the x-axis grouped by type.
And it is important that I be able to identify the Item# by hovering or clicking on any particular point (I can't label all of the points because the graph will become illegible).
1612491752517.png


What would you call this type of chart? Can it be made in excel? Any other solutions? It doesn't seem much more complex than a standard scatterplot, but I cant figure it out.
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

jfish1288

Board Regular
Joined
Jun 22, 2011
Messages
116
The only pseudo solution I have found is to rearrange the data like below, and then create a separate chart for each type. But I have a few issues with this:
  1. I cannot identify item# from the points
  2. Creating and maintaining the multiple chats would be very cumbersome as I play with the different data sets I have
  3. If 2 items share the same size, type, and price they cannot be identified

Book3
ABCDEF
1ItemTypeSizeBrand 1Brand 2Brand 3
2Item1Type_A8$499
3Item2Type_A8$99
4Item3Type_A8$99
5Item4Type_A9$99
6Item5Type_A9$199
7Item6Type_A10$599
8Item7Type_A10$99
9Item8Type_B8$499
10Item9Type_B9$299
11Item10Type_B10$599
12Item11Type C8$499
13Item12Type C8$299
14Item13Type_C8$399
15Item14Type_C9$299
16Item15Type_C9$499
17Item16Type_C10$599
18Item17Type_C10$299
19Item18Type_C10$599
Sheet1


1612492060141.png
 

Attachments

  • 1612491956294.png
    1612491956294.png
    39 KB · Views: 0

Watch MrExcel Video

Forum statistics

Threads
1,127,616
Messages
5,625,876
Members
416,141
Latest member
Bartek9q

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top