Stacked Bar Chart columns are in reverse order

Hello,

A bar chart will list the first value in the source data range closest to the intersection of the x and y axes.

Most of the times we organise the data from the top down, and then are confused when the data is charted in the wrong order.

Chart "values in reverse order" will do the trick. Otherwise, re-arrange your source data so that the item you want to be closest to the x axis is at the top of the list.

I don't understand how the "values in reverse order" does not work for you. Maybe you could post a small data sample in a workbook, with the corresponding chart and explain in context.

cheers,

Hi,

Thanks so much for the quick reply.

I attached an image from excel to describe the issue. Any suggestions would be appreciated.

The data sample is in the upper left, it is dummy data for this demonstration.

The actual data is dynamically generated and the number of data series changes. The data always start at the top row, but because of the number of rows changes, at the bottom there will be unused 'blank' data series rows. This is the reason I added "Blank" series to the dummy data.

The middle 2 charts are the default "stacked bar chart" and default "pie chart" excel 2007 generates.

The pie chart lists the data series correctly from the top row down, and clockwise starting at 12 o'clock.

The issue is the bar chart lists the data series in reverse order. This 1) puts the blank rows at the top 2) makes it hard to visually align to the pie chart.
In this case both the pie and bar charts are looked at together. The colors match which is great, but it is hard to look at because the order is backwards (there are many series with long names in the actual data).

The bottom left chart has "Values in Reverse Order" selected.
The bottom right chart added "Maximum axis value"

As you can see, the bottom right now has the correct order in the chart but: 1) it "grows downward" 2) the ledgend is still backwards from the pie chart.

I'm trying to make it look like the original bar chart in the middle, just with the series in reverse order.

If any part of this explaination is not clear, please let me know.

Thanks again for the help!

OK,

first, these are not bar charts, but column charts (in Excel speak). It helps if you get the terminology right.

Second: an image is good for Photoshop. If you want help with Excel, then you'd be much better off posting a workbook instead of an image.

If you post the workbook instead of the picture, I can inspect how you define the chart data source and give you suggestions on how to improve this, for example by using a series definition with dynamic range names that will exclude the blanks, so they are not even included in the chart.

I still don't understand what you consider wrong with the top left chart.

If you want the A series to be stacked at the top, you just need to re-arrange the order of the data in your source table and then go with the defaults.

You could engage a helper column in the source data to sort your data and then use the standard stacked column chart without any axis manipulation.

See attached

Without altering data layout you will need to alter the plot order of the stacked columns. This is done via the Select data dialog. In order to work correctly you will need to use code to sort out the chart.

If you change the layout of the data table the stacked column will work as required but the Pie chart will not plot incorrectly. So you will need 2 sets of chart data for the 2 chart types.

As I said you will need 2 chart tables in order to get the consistent legend order.

You will still be left with the colouration problem as the first series in each chart is actual different.