Excel chart with a single x-axis but two different ranges (combining horizontal clustered bar and horizontal stacked bar), possible?

Question

I am new here. I have a data set of density values (phytoplankton community) according to depth and the composition of it in percentage. I want to ask if it is possible to construct the density values in clustered horizontal graph on the left side (not in negative value) while the composition of it in stacked horizontal bar on the right side, in a single graph. The graph have a single x-axis (0 in the middle and both sides have different units)--I am not sure this uses the primary secondary axis method or not. The depth becomes the y-axis.

I am using Excel 2010 and have seen an example of such a graph, just I could not figure out how it is made but to end up in two separate graphs. Please, can anyone help out? Hope my explanation is clear enough. TQ

Answer 1

You need to assign one series to a secondary axis in order to make this type of change. The trick to get a single-axis chart is to hide the secondary axis, so your chart looks like it has only one.

To get a secondary axis: Right click on your series and select Format Data Series. Under Select Options check Plot Series on Secondary Axis.

Now you can change the chart type, etc for each series.

The next step is to hide the axis. But first make sure that the axis are actually identically scaled. If they are not, right click on the right handside scale and select Format Axis. Under Axis Options you can change the scaling using fix values. Once you made sure the axes are identical, select None for Major tick mark type and Axis labels

Answer 2

Late to this party, but here's my suggestion.

First, some semantics. In a horizontal bar chart, the independent variable (X) is the vertical axis, and the dependent variable (Y) is the horizontal axis.

Here is a data range with depth in the first column, density in the second, and compositions of three constituents in the third through fifth. Note the blank cell where the "depth" label would be.

Step 1 - Select the data, insert a horizontal bar chart. When you leave the top left cell blank, Excel knows the first row and column are special, and uses one for X values (category labels) and the other for series names. With more rows than columns, it creates more categories than series, which is what we want.

Step 2 - Assign all of the Comp series to the secondary axis.

Step 3 - Change any one of the Comp series to stacked bar, all series on its axis (secondary in this case) will also change. Density bars are hidden behind the stacks.

Step 4 - Show the secondary Vertical Axis (Plus icon beside the chart in Excel 2013, Chart Tools > Layout tab otherwise).

Step 5 - Format the primary (left) vertical axis so its categories are in reverse order.

Step 6 - Repeat for secondary (right) vertical axis.

Step 7 - Format primary (top) horizontal axis: minimum 0, maximum 40 (twice what we need), apply custom number format to the labels:

[<=20]0;;;

This means if the values are less than or equal to 20, use a number format of 0 (Number with no decimal digits), otherwise don't show the values.

Step 8 - Format the secondary (bottom) horizontal axis: minimum -1, maximum 1, number format of

0%;;0%;

This means, for positive numbers, use 0% (percentage with no decimal digits), don't show negative numbers, for zero also use 0%, and don't show text.

Step 9 - Add axis labels for both horizontal axes and primary (left) vertical axis. Format secondary (right) vertical axis so there are no labels and no tick marks.

Step 10 - Move the chart title, legend, and axis titles towards the edges of the chart. Center the horizontal axis labels over their respective halves of the chart. Enlarge the plot area (the data-containing inner part of the chart) to fill the space.

Answer 3

Ive been able to plot two series with varying x values over one x-axis by using a scatter plot instead. In order to make the scatter plot look like a line graph one should use the smooth marked scatter plot, and format the markers so that they are no markers. This will result in a smooth curve.

Answer 4

I have not found a way to combine column data and stacked data against a single axis using the COMBO option in EXCEL. I resorted to creating two separate graphs and overlaying them to appear as a single graph. You can alternately bring them to the front to adjust them. Once adjusted, GROUP them so that the graph can be moved or resized as a single graph.