How do I indicate nested data groups side-by-side in an Excel stacked column chart?

Question

I am preparing a chart for an academic journal and am struggling with formatting.

In the original chart I am trying to reproduce, nested data for two year periods, 2017 and 2020/21, are displayed side-by-side and then grouped by age range and ultimately gender.

On trying to reproduce the chart to align with the journal's requirements for editable figures, I am able to create the following, but as you can see it is not ideal.

While I am able to get the data to group correctly, I'm not sure how I would go about displaying the data from two time periods side-by-side so that there are only three clusters altogether. This this possible? Any help in this regard would be greatly appreciated.

		0-14 years	15-24 years	25-34 years	35-44 years	45+ years
Female	2017	0.64	6.56	5.52	4.96	4.74
Female	2020/21	0.93	8.58	5.56	4.57	4.12
Male	2017	1.61	19.88	32.72	25.05	21.59
Male	2020/21	1.73	22.01	31.95	27.09	21.59
Total	2017	1.13	13.21	19.23	14.93	12.06
Total	2020/21	1.33	15.33	18.83	15.85	11.78

Answer 1

To achieve your desired result you need to slightly restructure your data as below. The empty line is not necessary but leads to a better visualisation. Using this data structure, you will be able to closesly get to your desired result. To produce the chart, highlight the data and insert a "Clustered Column Chart", then simply change the colors and set the custom angle of the axis to -45°.

The resulting chart looks as follows:

To get even closer to the chart you provided, you can do the following modifications: Once you created the chart, delete the category labels from the table (i.e. female, male, and total), and insert text boxes at the specific position with the relevant labels. The removal from the table will keep the grouping but remove the labels. Thereby, you may also increase the maximum of the axis bounds (via right-click on the axis) for better visualization. And then you simply select the bars and insert via the + symbol the error bars depending on your needs. The resulting chart looks as follows:

Data:

category	year	2017	2020/21
female	0-14	0.64	0.93
	15-24	6.56	8.58
	25-34	5.52	5.56
	35-44	4.96	4.57
	45+	4.74	4.12
male	0-14	1.61	1.73
	15-24	19.88	22.01
	25-34	32.72	31.95
	35-44	25.05	27.09
	45+	21.59	21.59
total	0-14	1.13	1.33
	15-24	13.21	15.33
	25-34	19.23	18.83
	35-44	14.93	15.85
	45+	12.06	11.78