1

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?

  • I don’t understand the chart you got, but I suggest that, to get a stacked bar chart that makes sense, you’ll need to change the structure of the spreadsheet to have one row per month and one column per vendor.  If you search Super User, you can probably find questions and answers discussing how to convert what you have now into that format. – Scott Nov 20 '15 at 2:50
2

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.

PivotChart generated from my original table

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.