Four bars - two should overlap

[RookA1]

Hello,

This is thread is based on a former recent thread (linked directly right here).

I learned how to use the 'series overlap' tool to shift bars in front of one another for comparison purposes. However, when I use that tool, it applies the shift to every bar in the chart. I have an example chart attached.

My end goal for the attached is for each holiday to have two overlapping bars each (2018 rev/exp; 2019 rev/exp).

Thank you for your time

[MrShorty]

What I see you describing is a clustered and stacked column chart -- though you've opted to not use an actual stacked chart for this as we discussed in the other thread. The standard approach that I think of for clustered and stacked column/bar charts is this one: https://peltiertech.com/clustered-st...mn-bar-charts/ Note that much of the work is arranging the data in the spreadsheet. I think it can work with your "overlapped" clustered column chart -- if you will do the same kind of thing with the data arrangement in the spreadsheet. Spread it out so there are blank cells and rows throughout the data so that each column ends up where you need it to be.

Here's what I did in your sample file:

1) Insert two blank rows between each holiday
2) Move the 2019 data down 1 row
3) Select each data series in the chart and adjust the ranges to account for the added rows.
4) Select a data series and format with no gap (to push the adjacent "columns" next to each other). This step is probably not necessary, but I thought the chart would be more readable.
5) You can then go through the process of improving the horizontal axis labels (using Peltier's tutorial) if desired.

questions?

[Roel Jongman]

It takes a bit (or a lot ) of fidling with a secondairy axis and placing 2 of the 4 bars on that secondairy axis and then adjust both series overlap and gapwidth until you get the paired bars view..

I moved 2019 revenue and 2018 Expence to secondairy axis
that gave me the paired look.

have a look if that works for you..

[RookA1]

Thank you both. I'm getting very close. Right now I'm encountering an issue where the bigger bar covers the smaller one entirely (see Halloween - expenses exceeded revenue). Is there a way to bypass this?

I'm guessing this is occurring because it's a clustered instead of stacked column.

[MrShorty]

Not really. This occurs because Excel plots each data series in the specified order (1st it plots data series 1, then it plots data series 2 on top of data series 1, and so on). In order to get a smaller column to be visible (in front of) a larger column, that column must be plotted after the larger column. But plot order applies to entire data series, not individual points.

I like what Roel did -- partial overlap rather than 100% overlap. The partial overlap allows the reader to see both columns, but also be able to see which pairs are for the same year. I would probably recommend something where you do partial overlap rather than full overlap.

If you decide that you must have full overlap, then you will probably need to add additional data series for each of the 8 scenarios:
1) 2018 revenue that is larger than 2018 expense
2) 2018 expense that is larger than 2018 revenue
3) 2019 revenue that is larger than 2019 expense
4) 2019 expense that is larger than 2019 revenue
5) 2018 revenue that is smaller than 2018 expense
6) 2018 expense that is smaller than 2018 revenue
7) 2019 revenue that is smaller than 2019 expense
8) 2019 expense that is smaller than 2019 revenue

Make sure to plot those 8 series so that the "larger than" series are the first 4 data series and the "smaller than" data series are the last 4. Then format so that the appropriate columns have the same color/formatting. Most of the work is in the spreadsheet -- getting the data into the 8 columns. Once the spreadsheet work is done, creating the chart is basically the same as creating any of the other charts we've looked at over the two threads.

It's up to you how you exactly how you want to do it -- this idea will apply to any of the chart strategies we have talked about. How would you like to proceed? What difficulties do you encounter in implementing your choice?

[RookA1]

Yes - agreed that it makes more sense to represent that data that way. Thank you (both) for pointing that out. Going to reach a final product on my end that looks nice and clean, then will mark this as solved. Thanks for taking the time to assist.

[RookA1]

Mission complete on this. I ended up using some advice from this video guide and decided to make the expenses part of a secondary axis and kept the revenue on the primary axis. Then, I made sure the min and max on both were identical.

Thanks to all who provided advice.