Charting Multiple Clusters on same graph

erogalski

New Member
Joined
Mar 31, 2017
Messages
3
Hello,

I don't have much experience with charts in excel and I'm not sure how to build this chart I want with my data set. Below is a sample of the data:

School IDGrade LevelSize$ Per PupilPoverty Level
2010High SchoolMedium 13,679.7482.31%
7100Elementary SchoolMedium 14,730.4787.73%
7730Elementary SchoolMedium 13,561.1389.72%
4020High SchoolMedium 12,323.2976.69%
4140High SchoolSmall 13,463.2585.20%
1260Elementary SchoolMedium 12,483.3289.64%
6350Elementary SchoolMedium 11,607.0978.71%
6360Elementary SchoolMedium 12,554.6090.61%
7360Elementary SchoolMedium 11,491.7490.26%
1300Elementary SchoolMedium 12,475.0185.06%
6390Elementary SchoolMedium 11,548.8986.50%
2000High SchoolMedium 14,660.8175.12%
1100High SchoolMedium 14,590.7182.00%
6060High SchoolMedium 13,464.6376.36%
4350Elementary SchoolMedium 12,954.8687.39%
4320Elementary SchoolMedium 13,003.6073.82%
5350Elementary SchoolMedium 12,761.9292.18%
5620High SchoolSmall 15,053.3480.00%
5610High SchoolSmall 13,822.3182.49%
4300Elementary SchoolMedium 11,357.3986.19%
1460Elementary SchoolMedium 13,305.6381.93%
7010High SchoolLarge 12,056.3179.09%
1410Elementary SchoolMedium 11,239.4689.65%
1420Elementary SchoolMedium 13,267.7987.29%
8010High SchoolLarge 12,559.1069.40%
7120High SchoolLarge 12,801.5073.37%

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

What I'm trying to do is make a cluster chart with the Poverty level on the X axis and the $ per pupil on the Y axis. Then plot each school on the graph, but have 6 distinct clusters (Elementary-small, Elementary-medium, Elementary-large, High School-small, High School-medium and High School-Large).

I can do it as just one big cluster, but do not know how to create the separate clusters.

Any thoughts on this would be greatly appreciated!

Thank you,

Ed
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,096
Hi Ed

You can create a pivot table from your source table and then produce the corresponding pivot chart.
Place the fields in the following areas:


  • Size and grade level on the filters area
  • Poverty level and school ID on the rows area(will be the X axis)
  • Amount per pupil on the values area(will be the Y axis)

The resulting chart is shown below. Would you like it to explicitly display what size and grade level are currently chosen?
I can post my test workbook if you need it.

HYpVi3X.jpg
 

Watch MrExcel Video

Forum statistics

Threads
1,123,169
Messages
5,600,107
Members
414,364
Latest member
KalilMe

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