Does anyone know how I can possibly create a graph exactly like this one in excel? It's Figure 9 on this paper (https://www.mdpi.com/1999-4923/12/12/1132/htm)

I am totally lost. I've tried and lost hours on excel, but I just can't seem to figure out how I should input my data since it's so complex...

Any help would be greatly appreciated!


I assume this is the chart you're talking about (if it isn't, please put the chart into your question): enter image description here

This is quite easy to do in Excel, and it looks like it has been done in Excel.

It's a combined stacked column chart with a line chart.

The column chart has four series, as does the line chart. The easiest way to chart this is to lay out the data source to have one column for each series.

Lay out the data in a grid with a column for each of the eight series and all the X axis labels in the first column. Then insert a chart, for e.g. a stacked bar chart. The X axis values are numbers so you need to make sure Excel does not interpret these as another data series. To do that, make sure the cell above the column is blank. After the chart has been created, you can put the text back into that cell. I you want to use a hierarchical axis, then you will have two columns

enter image description here

Next, change the chart type to Combo. Make sure the first four series are Stacked Column, (not Clustered). Make the last four series Line and tick the secondary axis box.

enter image description here

For the hierarchical axis, make sure the hierarchy levels are in the worksheet. I had already taken the screenshots, so I inserted column A as an afterthought. Then edit the data source and click the button to edit the horizontal axis labels.

enter image description here

Change the range that the axis refers to to include two columns, not just one.

enter image description here

The rest is formatting. Change the colours, add markers to the line, add error bars.

You need to select each series one after the other, then click the + button and add the error bars.

enter image description here

The legend is a bit trickier to achieve. It looks like it has been put together with the drawing tools and the image superimposed onto the chart.

  • If that resolved your issue, please mark the answer as described in the tour. If it didn't, please leave a comment, so I can follow up. – teylyn May 8 at 23:11
  • Thank you for your prompt reply! Following your guide I was able to construct a chart as the one on the paper I referenced. However, I am having trouble adding the error bars for each of the series after the first "type a" (that is, I am not being able to put errors bars to what would correspond to "type b" and "type c" on your screenshots). I input the values but the bars just don't appear. Any idea has to what may be the issue? – Maria Teixeira May 9 at 11:09
  • Error bars are only added to the selected series. That means you need to select each of the 8 series individually and then add error bars. If that is not the solution, please close this question by accepting the answer and start a new question for the error bar issue. – teylyn May 9 at 20:44
  • I edited my answer to include details about error bars. – teylyn May 9 at 20:46

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.