Column Chart to use dual horizontal range

[kryssie.e]

Untitled.jpg

Hello,
I have a clustered column chart (see image) where I have 2 series.
It only allows a single axis.

I'd like to have the range from both column B and E since you can see if i just use one, I miss some labels.

I tried to add a secondary axis but couldn't get the formatting of the plot to look normal (i.e. as it does when using 1 axis, with the columns neatly aligned).

[Andy Pope]

Not sure what the chart is trying to show because you are literally comparing apples with oranges.

First you will need to alter the named ranged formula so the data and label ranges are the same length for each series. I added MAX of the ranges being used to determine length.
In order to get the chart to use 2 sets of category labels you will need to move 1 of the series to the secondary axis.
Now to avoid overlapping columns you will need to add 2 dummy series. 1 to each vertical axis.

You now have a design choice on how to display the labels.
If you want to use the axis then you can enable the secondary horizontal axis. Draw back of axis approach is the name will be centered below the columns, which is why I colour coded the text to make it easier to associate with value.

I think it's better to use data labels, showing category name, to name the columns.

[kryssie.e]

Hi Andy,
What i want is to plot the data as if the data set were:
topicListCumulative topicCOUNT1 topicCOUNT2
apple 4 0
pear 2 1
kiwi 0 1
mango 0 2

This way, like any sensible chart should be, the 2 series have columns in the same label, not mismatched.
Maybe the best way to do this isn't through the chart itself, but to have another column that does create a cumulative list of topics and match to the count from the original columns.
I have not found a way for this to work dynamically.

Thanks

[Andy Pope]

You can use a pivot table/chart.
Add a couple of fields to you source data table to determine whether a fruit is within first or second date ranges.

For the example I manually entered 1 or 0. you can add formula

[kryssie.e]

Hello,
I could not get this to work since my data MUST be formulated in this automated way.
I've not been able to find a way to have the table update automatically with my dynamic ranges on SETUP.chartTopics sheet (which is dynamically changed based on entries on database page).
Maybe if there was a way to auto update a table this could work.
I also have the restriction of not using macros since I will need this to be placed in a shared site where online excel has no macro capability.

Thanks

[JeteMc]

Expanding on Andy's pivot table proposal:
I moved the start and end dates to the data sheet [optional]
The Date1 and Date2 columns are populated using: =IF(AND($A2>=J$2,A2<=J$3),1,0) and =IF(AND($A2>=M$2,B2<=M$3),1,0)
According to myOnlineTraininghub: "Refreshing PivotTables in Excel Online requires a right-click of the PivotTable > Refresh:"
Let us know if you have any questions.

[kryssie.e]

Hello
I created a 0,1 column as suggested and hid the columns.
With this, i was able to create my pivot table and chart.
Right click refresh works (but doesn't make it less annoying).
Thanks for help.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.