Life expectancy chart

Your picture looks like a butterfly or tornado chart. Most tutorials I have seen for these charts are based on a stacked bar chart. I would start with a tutorial like this, https://best-excel-tutorial.com/56-c...utterfly-chart then adapt to your specific needs.

Show us some sample data that needs to be split in an excel file. See the yellow banner at the top of this page.

Can you present the data in a worksheet and attach as asked using the yellow banner. It is difficult to manipulate the data shown as it is not separated into cells and not easily able to determine which items belong in which column. Help us to help you by giving us workable information.

I, too, am not sure I understand. The only difference between your picture and the tutorial is that you have two "stacks" for each category on each side of the butterfly, where the tutorial's chart only has one "stack" on each side. Is that what you mean by "split each category into two"? If so, then, because the butterfly chart is already a stacked bar chart, it should be as simple as adding additional data series to the chart.

If it helps to place it in the context of the tutorial, the tutorial has their data of interest in columns B and C. Columns D, E, and F are for "dummy" data series that are used to give the butterfly effect. If the goal is to have "two stacks" on each side, then that means four columns instead of two for the data of interest. So, you would be using columns B, C, D, and E for your data of interest and column F, G, and H will contain the dummy data for the unseen dummy series. Am I guessing right? Does that help?

Beyond that, I agree with Alansidman. It could be difficult to help you further without seeing your (dummy) data and your current attempt at the chart. That should help us to explain what you need to do differently.

I'm still not sure I understand. The data in your sample file does not include any male/female indicators, so I'm not sure what you really have or how that is incorporated. I took your data and created a butterfly chart (in LO Calc, hopefully Excel will be able to read the chart information, sometimes it doesn't do it exactly right) with the "a" data on the left side of the butterfly and the "b" data on the right side of the butterfly. Time is increasing as you move up the chart. Each region is a separate data series. Is that even close to what you want to see? Where does male/female fit into the data?

That's a basic stacked column chart -- one of the basic chart types supported by Excel. Have you tried it with your data? Review this, https://www.excel-easy.com/examples/column-chart.html if you are uncertain of the basic mechanics of creating a column chart (their example is for a clustered column. the only difference for you is to select stacked column instead of clustered column).

I believe I need to split the data in the actual table?

Assuming I understand, I would reply, "yes". Charts have no data analysis or generation capabilities of their own. They can only show data that you have entered/calculated in the spreadsheet. Once the necessary data are calculated/entered into the spreadsheet, then adding male/female data to the chart should be as simple as adding enough data series/legend entries to the chart and telling the chart where to find the male/female data for these data series.

I'm sorry that this is frustrating, and I know that frustration makes this sort of thing harder. Take a break or something, if you need to, so you can approach this without frustration muddying things up.

It would help if we had a better idea what data you have. You talk about having "A and B" data, and you also talk about having "male and female" data. Are these the same (A is male and B is female), are they different (there is data for A + male, A + female, B + male, B + female)? Your original example also showed region. Is your current stacked bar example for one region, or are you still wanting to combine multiple regions into a single chart, or are we now ignoring region?

Assuming we are now working on data for a single region, two categories, with male and female within each category, here's how I would expect to create any basic Excel chart:

1) Data entry: Enter the data like this:

Please Login or Register  to view this content.

2) Select this table and insert the desired chart type. Stacked column or line or area or stacked area or clustered bar or whatever chart type you want to try.

3) Look at the chart and see if I like it. If not, I can select the chart (make sure the chart is selected and not a single data series), find the "chart type" command and change the chart type to a different basic chart type.

4) If I cycle through all of the basic chart types and I don't like any of them, then I need to stop and think about what I like and don't like about each chart type and identify what I need different that the basic chart types don't readily offer. Then I can start exploring more advanced charting techniques to get different effects. But everything has to start by being able to create the basic chart types.

Here's a file I created to show this. I started from your data in post #11, removed everything but the Wales data. Then I split Wales a and b into male and female for a total of 4 data columns (plus the time column to make a total of 5 columns). Filled the table with random numbers, then inserted 2 charts from the data. One is a clustered column, the other is a line. As noted in step 3, it should be easy to change either of these charts to any other basic chart type. Do either of these charts look good? If so, what looks good about them? If not, what do you need different? If you enter some reasonable values (rather than my random values) into the table, what do you see? What chart type looks good with real data? What do you want to see different?

It should be exactly like I show in post 21 -- 4 columns of data (Wales HLE M, Wales HLE F, Wales LE @ birth M, Wales LE @ birth F) plus a leftmost column for dates. Select the resulting block of cells and insert a line chart (line without marker subtype). Is there something in your spreadsheet that prevents you from doing the same thing I did in post #21?

I don't know what constitutes a "good enough comparison". As an outsider, I can look at this chart and quickly see:

1) LE is consistently larger (about 20 units) than HLE for both genders.
2) There appears to be little or no trend through time for each group.
3) Female LE and HLE are consistently a little bit higher than male LE and HLE.

If there are other things you want me to see, I don't see them right away as an outsider.

I did not see an extra green block, but I assume you mean the "series 3" gray blocks. You can "hide" the columns in the chart by selecting the data series and formatting the series so it has no border and no fill -- effectively rendering it invisible. You can remove the "series 3" legend entry by selecting the legend, then click on the "series 3" legend entry, then press delete.

How exactly must you match the picture? Easiest, though it would not exactly match, would be to:

1) Calculate the desired differences in an adjacent column in the spreadsheet.
2a) Add error bars to one of the data series (error bar help file: https://support.microsoft.com/en-us/...5-864049a145f0 ).
2b) Format the error bars to have only positive (left column data series) or negative (right column data series) error bars.
2c) While formatting the error bars, select "custom values" and choose the spreadsheet column calculated in step (1) for the error values.
3) Add data labels to the chosen data series https://support.microsoft.com/en-us/...2-f467c9f4eb2d choose "values from cells", and, again, select the column calculated in step (1).

Will that be close enough, or do we need to get more complicated in order to more exactly match the picture?