combo chart

Question

Level 1

8 points

combo chart

Hi, I am trying to create a chart in Numbers 10.1 on MacBook to plot monthly income, expenses and account balance which I'd like displayed as follows: income as a column, expenses as a stacked column of 3 expense components, account balance as a line chart.

I managed to combine a line chart with a column chart (though annoyingly Numbers insists in putting the Y-axis for the line chart on the left whilst I want it on the right), but I don't manage to stack only 3 of the 4 columns. I saw a tutorial on YouTube where the guy was changing the type of graph for one series by selecting it individually but, when I try this, Numbers changes the type of all columns! (i.e. toggling between stacked and splayed). Am I doing it wrong or is it something that Numbers doesn't support?

MacBook Pro 16″, macOS 11.2

Posted on Apr 26, 2021 3:10 AM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

28,624 points

Apr 26, 2021 9:17 AM in response to PhilippeGB

I was curious to see how this worked in Excel. I tried to follow some directions I found but by version of Excel (16.48 for Mac) doesn't work the same as what was being explained and the controls were not the same. I could get stacked bars or single bars but not both and I could get some kind of screwed up mess with wide bars in front of narrow bars but I couldn't figure out how to get the mix.

If you are looking for a trick to do it in Numbers, you can create two charts that overlap, one on top of the other. It takes a good bit of work to get the bar widths matching and everything lined up properly and you turn off all the axis lines and labels on one chart. The min and max Y axis setting for the one chart and the Y1 axis setting for the combo chart must be set manually and the same. The one shown below is a stacked bar chart of expenses and a 2-D two axis chart of the income (as a bar), balance (as a line), one blank column (as a bar). The blank column was required to get the data points for the line to sit between the bars vs on top of the expense bar. I set it to no color and no border so it won't show in the legend. You could use any of the other columns, not a blank one; it doesn't show on the chart anyway. This chart was not so easy to make and you wouldn't want to have to edit it too often but if it is absolutely needed, it can be done.

Hopefully someone can figure out how to do this all in the one 2d two axis chart but I did not see how.

Reply

Answer 2

PhilippeGB Author

Level 1

8 points

Apr 26, 2021 6:39 AM in response to SGIII

Hi, here is for illustration a mockup of the type of graph I'm trying to create:

I have since found on an Excel forum a way to combine single column and stacked columns by putting all series for the stacked column in the same column of the data table and the other series (Income in the example) in another column of the table, then selecting stacked columns for the graph type.

This however doesn't solve the problem of adding the line chart for the 5th series.

Looking closer to the 13 diagrams that appear in the list brought up by clicking on the chart button at the top of the Numbers window, there is one showing a line on top of splayed columns, but none showing such line along with stacked column. Therefore either this option is well hidden somewhere or, as I fear, it just isn't accessible!

Reply

Answer 3

SGIII

Level 8

36,146 points

Apr 26, 2021 5:37 AM in response to PhilippeGB

It's hard to follow what you are trying to do without more details, perhaps screenshots, example data in table, link to video, etc.

Have you had a look at the 'Charting Basics' template at File > New in your menu? The '2-Axis Chart' seems to be part of the solution.

SG

Reply

Answer 4

PhilippeGB Author

Level 1

8 points

Apr 26, 2021 7:11 AM in response to Badunit

Thank you Badunit for pointing out that I can choose for each series which axis it should use. I have now found it and it does indeed answer my grumble about the axis used for the line chart.

I concur with you that the list of options does not readily show a line - stacked columns combination.

I was wondering however if there is any trick that allows to achieve it (after all, it is possible to combine stacked columns and separate columns although there is no such option shown in the list under the graph button).

Reply