Comparative charts in Excel for 3 variables, but another "dimension" so total 6 variables

Hi All,

Sorry but kind of hard to put into words what I am referring to. However, here goes my best shot:

I have multiple bar charts.... they measure 3 main items across many different variables.

For Example:

1. YTD Revenue 2017
2. Budget Revenue 2017
3. YTD Revenue 2016

These 3 main KPIs are then measured across different regions (say 5 regions for simplicity).

That's all fine and dandy. I've got the solution for that already and it's working well.

But let's say we throw another variable into the mix. E.g., discounts.

In this example, I want the same KPIs measured across the same different regions however, I'd like to place 2 different bar charts on the same chart. One chart being somewhat of a "ghost" for lack of a better term.

1. YTD Revenue 2017 (without discounts) AND YTD Revenue 2017 (with discounts)
2. Budget Revenue 2017 (without discounts) AND YTD Revenue 2017 (with discounts)
3. YTD Revenue 2016 (without discounts) AND YTD Revenue 2016 (with discounts)

Visually I would like the ACTUAL REVENUE, I.E, WITH DISCOUNTS, to appear as a solid color, and then the revenue WITHOUT discounts to appear "ghosted" or slightly transparent directly BEHIND, but slightly offset so it is visible.

Am I dreaming or is this possible? Sorry I am unable to upload an example workbook as I have no clue where to start with this one.

Thanks in advance!

Hi
is this what you're after?

Originally Posted by NickyC

Hi
is this what you're after?

Hi Nicky,

Yes that presentation is exactly what I am after. I will need to see if it works with the data I have but that presentation is EXACTLY what I want.

But how do I do it? I am using Microsoft Excel 2010 and the dataset is huge so can't leverage off of the file you've furnished unfortunately.

The main trick is to adjust the series overlap

in more detail:

• create a 2D clustered column with 2 series (with and without discount)
• set the transparency on the "without discount" series to 50%
• right click on either series and set the gap width to 35% (making the columns wider) and the overlap to 60%
• you will have to set the colours for each column manually. Click twice on a column to select the single data point not the series, and choose a solid fill colour. Apply the same colour to both the "with" and "without" discount points ("without discount" should retain its 50% transparency).
• once you have created a chart in the format you like, you can apply it to other similar charts using copy > paste special > paste format

if you need to create lots of similar charts, you can copy and paste repeatedly and adjust the data sources, or use a macro if the data are in a consistent structure (eg successive rows or columns)

or, you could use a macro that applies the above steps to every chart in your worksheet:

Please Login or Register  to view this content.

Originally Posted by NickyC

The main trick is to adjust the series overlap

in more detail:

• create a 2D clustered column with 2 series (with and without discount)
• set the transparency on the "without discount" series to 50%
• right click on either series and set the gap width to 35% (making the columns wider) and the overlap to 60%
• you will have to set the colours for each column manually. Click twice on a column to select the single data point not the series, and choose a solid fill colour. Apply the same colour to both the "with" and "without" discount points ("without discount" should retain its 50% transparency).
• once you have created a chart in the format you like, you can apply it to other similar charts using copy > paste special > paste format

if you need to create lots of similar charts, you can copy and paste repeatedly and adjust the data sources, or use a macro if the data are in a consistent structure (eg successive rows or columns)

or, you could use a macro that applies the above steps to every chart in your worksheet:

Please Login or Register  to view this content.

thank you very much mate! I'll go into work tomorrow to test this out and see if it works and revert with any comments or what not. If this works man it'll be a dream come true!

Originally Posted by NickyC

The main trick is to adjust the series overlap

in more detail:

• create a 2D clustered column with 2 series (with and without discount)
• set the transparency on the "without discount" series to 50%
• right click on either series and set the gap width to 35% (making the columns wider) and the overlap to 60%
• you will have to set the colours for each column manually. Click twice on a column to select the single data point not the series, and choose a solid fill colour. Apply the same colour to both the "with" and "without" discount points ("without discount" should retain its 50% transparency).
• once you have created a chart in the format you like, you can apply it to other similar charts using copy > paste special > paste format

if you need to create lots of similar charts, you can copy and paste repeatedly and adjust the data sources, or use a macro if the data are in a consistent structure (eg successive rows or columns)

or, you could use a macro that applies the above steps to every chart in your worksheet:

Please Login or Register  to view this content.

Hi NickyC, I am working on this now and I've noticed the problem I am having is implementing the second data set into the chart.

I have attached a workbook that gives a more representative view of the data set. Basically I want the same formatting as you
had originally provided but with this type of data set. There are numerous other charts and variables however once I get the
principle down I think I will be able to implement the changes across all charts.

To add more information to the request... I am quickly realizing that this is much more complicated than I anticipated.

I want to Compare YTD 2017 with YTD 2017 Net FAC.... these should be same color and follow the visualization NickyC provided earlier.
Same for Budget 2017 and Budget 2017 NET FAC etc...

Basically, there are 2 different data sets that I would like to visualize on the same chart. With one chart being super imposed on the other chart. Is this possible?

Hi

I think what you want can be done, but you will need to rearrange your data slightly so there are 2 series (net and gross) and your YTD labels and "channels" are on the x axis.

The attached shows how a changed treatment could apply to your data and the random examples I created in the first workbook. It also includes a macro that applies the selected formatting to all the charts in the sheet. Note that the macro now assumes you want 3 colours associated with YTD net, YTD gross, and 2016 gross. If you have more points for each channel you will have to modify it slightly.