How to format stacked bar chart correctly

Question

I'm trying to make a stacked bar chart for some transactions made within the year. Ideally, the x-axis is month, the y-axis is amount, and the stacks (layers) are vendor name. But I can't get it formatted correctly. I always select all three columns in my dataset.

This screenshot shows an excerpt of the dataset I'm using:

            ]

This shows what comes up when I click "Select Data Source":

]

... and this is the chart that results:

^{[This links to a full-size, unabridged version of the above.]}

As you can see, the month values are repeated sometimes, as many transactions were made within the same month.

So how can I get my chart so that the correct data is displayed on the correct axes? What's causing it to display with a strangely formatted y-axis?

Answer 1

So as it turns out, Excel 2013 already has a built-in solution to this problem: PivotCharts. They go along with PivotTables. I've included an example picture of the chart I ended up with, and I've also highlighted where to select the PivotChart object under the Insert tab in the Ribbon. I made "Budget Year/Month" the Axis option, "Sum of Actual USD" the Values option, and "Vendor" the Legend option, which gave me this result. Excel gives the option to reformat the axes, so that's why the dates on the X-axis have differently formatted values than the ones in my original post on this thread.