I am trying to create a scatterplot of two categorical values in Windows Excel 2013.
I tried putting the values into a pivot table, but cannot create an XY chart type with a chart that has been created from PivotTable data. To make a pivot table, I created a duplicate of Amount
and put the sum in axis and count in legend. Unless I created a column showing the sum for each category of Amount
, I do not how to do it any other way except for a pivot table.
My data:
I put in a sub-set, but the original data has State
with the 50 U.S. states and Amount
with 1k-5k, 5k-10k, 10k-15k, 15-20k, 20k-25k, and >25k.
LOCATION Amount
Illinois more than 25k
New York more than 25k
New York more than 25k
New York more than 25k
New York more than 25k
New York more than 25k
Texas 20k-25k
New York 20k-25k
Missouri 20k-25k
Colorado 20k-25k
Illinois 1k-5k
Massachusetts 10k-15k
Florida 15k-20k
Ideal chart:
The x-axis with the sum Amount
, y-axis with count of Amount
, and values as count of State
. I copied and pasted the values of the pivot table and created a scatterplot of the results. I would have my desired plot if the x and y axis were flipped and the format was scatter.
I do not have enough reps to add a url, but after putting the data in a pivot chart, the xy plot looks like the description described above.
Here's a link of an ideal chart from peltiertech (look at the second chart from the top):