Scatterplot of categorical values from pivot table

Question

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):

Categorical XY Chart with Highlighted Categorical Ranges

Solution

In Excel, a Scatter Chart never displays categories on the horizontal axis. If you look closely at Jon Peltier's sample chart, you can see how he worked around this issue: by using numeric values as a proxy for categories. You'll need to do the same thing.

Then you can recreate labels for your X-axis values/categories. A typical method for that is to create an additional series with corresponding x-values and 0 for their associated y-values, then using Rob Bovey's excellent XY Chart Labeler to label that series' data points with your category labels.

Alternatively, you could use a Line Chart type, which will accommodate category values in the X-axis. You'll need to make sure your values line up appropriately in your data source, then format your data point markers on and your lines off.

Comments

[Scott Davis]

Jon's data points are in a linear line, while mine will be spread across the four quadrants of the xy plot. My x-axis counts the number of occurrences for each type of State in the Amount group. I still cannot figure out how he created the x-axis for the plot.

[Jon Peltier]

The X-Axis in my chart is the categorical axis from the stacked column chart that produces the floating bars.