Free Microsoft Excel 2013 Quick Reference

Secondary Axis without overlapping bars

I have a bar chart with 2 series of data using separate axis
But I cant seem to get the bars to be side by side, they are always overlapping.

Sorry but my chart is embedded inside a PowerPoint presentation, i cant seem to get it back out.

Hope you can help


Post your answer or comment

comments powered by Disqus
When ever I add a secondary axis to my chart my bar chart sub type changes to a stacked bar chart. How do I keep the sub type bar chart? I have attached an example. I have tried playing with the gap and width of the bars but to no avail.

Thanks for any help on this

Hi -
I've read several postings regarding adding a secondary axis to a bar
chart, but I am having a problem. When I select the data series I want
to plot on a secondary axis, the "Axis" tab does not come up, just
Patterns, Shape, Data Labels, Series Order and Options. I can get it
to work on a line graph, but I need this to be a bar chart. Help?

Thanks

--
Angivan
------------------------------------------------------------------------
Angivan's Profile: http://www.excelforum.com/member.php...o&userid=27835
View this thread: http://www.excelforum.com/showthread...hreadid=473432

How do you create a column chart with a secondary axis without having the
bars overplotting? I would like the bars to be adjacent to one another.

I am having trouble adding a secondary axis to a bar graph. Can you help?
What do I highlight? please walk me through this .thanks

Hi -
I've read several postings regarding adding a secondary axis to a bar chart, but I am having a problem. When I select the data series I want to plot on a secondary axis, the "Axis" tab does not come up, just Patterns, Shape, Data Labels, Series Order and Options. I can get it to work on a line graph, but I need this to be a bar chart. Help?

Thanks

I am trying to graph 4 series of data. 3 series are scatter plots and are
working fine no matter what i do. The other is a bar graph for rainfall data
(in). I want to put it on a secondary axis. I am taking a large set of data
and looking at only a few days in my range. When I change this series to a
bar graph it no longer corresponds to the range selected on the xaxis but
shows all the data available. When I put data on the secondary axis as a bar
graph, again it does not correspond with the xaxis. How can I make this work?

Hey everyone,
Quick question. Stuck on 2003, and I want to do a 4-column chart with a primary and secondary axis, with 2 columns each. Every time I choose "secondary Axis," it overlaps the columns. Any idea how to get them to be shown side by side?

Thanks!

I have a bunch of charts that I need to plot voltage and current measurments on primay and secondary axis respectivly.

Is there anyway to do this when I create the chart? Each chart will have four different series on the primary axis and eight on the secondary.

The problem I have is when creating the chart everything gets put on the primary axis. when I select a data set and place it on the secondary axis Excel automatically adds markers to the line. Now I have to go it and remove the markers.

This just creates more work for me.

Is there any way to place a data set on the secondary axis without Excel adding markers or doing anything else I did not ask for?

I have posted this a year ago on MrExcel, but without much response. I solved the issue then by putting in a short delay in the code. This workaround has suddenly stopped being effective though. So I want to get to the root.

In the code I create a graph (bars) then add an extra data set which I want to display as a horizontal line (XYscatter). Then I delete the secondary axis information to get a clean graph. This method comes from Peltier.

Now this used to work fine under Excel 2003, but when moving to 2010 it failed. Partly because the various chart items are addressed differently under 2010.

The version I came up with works perfectly when stepping through or even when running with the Editor open. However when the editor is closed an errorbox comes up to say basically that the secondary axis doesn't exist and so it can't set the properties.

initially I got by this by creating a delay between creating the new data set (for the line) and telling it to display this on a secondary axis. But that doesn't always work, sometimes the message still comes up.
When youcomment out the Wait loop in the code the error becomes apparent.


	VB:
	
 withc() 
     
    ActiveSheet.ChartObjects("Chart 1").Activate ' activate current combined chart
    ActiveChart.SeriesCollection(2).Delete ' delete horizontal line
    create_NewSer ' recreate horizontal line
     
End Sub 
Sub create_NewSer() 'the macro that doesn't run under excel 2010, only step wise
    Dim WAIT As Double 
    With ActiveChart 
        With .SeriesCollection.NewSeries 
            .Values = "=Sheet1!$B$9:$B$10" 
            .XValues = "=Sheet1!$A$9:$A$10" 
             
            WAIT = Timer 
            While Timer < WAIT + 0.01 'put in a delay. Used to work
                DoEvents 'do nothing
            Wend ' if you comment out this While loop the code will fail
             
            .ChartType = xlXYScatterLinesNoMarkers ' se the new points to XY line
            With .Border 
                .ColorIndex = 17 
                .Weight = xlThin 
                .LineStyle = xlDash 
            End With 
        End With 
        With .Axes(xlCategory, xlSecondary) ' this is where the code fails with
             ' "the specified dimension is not valid for the chart type" and
             ' "Method Axis of object _Chart failed
            .MajorTickMark = xlNone 
            .TickLabelPosition = xlNone 
            .MinimumScale = 0 
            .MaximumScale = 1 
        End With 
        With .Axes(xlValue, xlSecondary) 
            .Select 
            Selection.Delete 
        End With 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


When creating a bar chart for profit $ with a secondary axis to show % of revenue I cannot get x axis to cross both y axis at zero. I have tried formating each axis and selecting the box Category (x) axis crosses at and inputting zero. It always defaults to having the primary y axis cross the x at zero only. I have tried multiple combinations of formating axis choices and played with the gap width and overlap of the data points. Either the data points get stretched out and are not all viewable on the chart or one of the y axis lables are missing. Any ideas?

I am using excel 2003

Trying to prepare graph

Have % on primary axis
and values 0 -30 on secondary axis
x axis is locations

Attempting to prepare bar chart, the two series are just plot on top of each other

Just want the two columns side by for example

location A
%
number

location b
%
number

Many Thanks
Simon

Hello
I have two sets of data that need to be plotted, as a bar chart, on the same x-axis but with a different y-axis (one for volume of people and one for total hours worked per month).
I obviously need a secondary axis for this but when I go Format Data Series>Axis>Secondary Axis, although the secondary axis appears on the right as it should do, the data stacks itself so that I have series 1 on top of series 2.
Does anyone know how I can put the data side-by-side with series 1 referencing the left axis and series 2 referencing the right axis?
Thanks

Tom

I have made a bar chart with x axis as all the dates of a month. I want that all the in the X axis dates, cases where the day of the week is either sat or sun should be conditionally highlighted in bold orange & red respectively and the bar charts also be respectively in the same color.
How do I do this ?
Can it be done without the use of secondary axis ?

Need to create a stacked bar chart and utilize the secondary axis for data
with a different value range.

Data Table:
Yr 1 YR 2 Pipeline
Small Deals 7 1 13
Med Deals 2 6 14
Large Deals 0 2 20

Primary axis should be Yr 1 & 2 and secondary axis should be pipeline.
Having created the stacked bar chart I change the pipeline series to the
secondary axis and end up with a simple bar chart. How can I create a
stacked bar chart with a secondary axis with a different scale?

Thanks.

I've tried a search and seen this question several times, but no successful answers. Does anyone know how I can add a secondary y-axis and not have the columns in my bar graph stack? I have a 2X2 data set that results in a total of 4 bars.

Hi all. I have some data that I need to plot (Excel v.X running on Mac OS
Panther). I've currently got two charts: one is a bar graph with two groups,
and each bar has two colors (one for each of the groups) - 100% stacked
column. I have another graph which is a very simple line graph. Both graphs
have the same X axis (with 7 data points), but different Y axes. How do I
combine them into the same chart? I know how to make a secondary axis, and
the example in Excel (a "custom" chart) has a bar graph plus a line graph on
the secondary Y axis, but its bar graph is a simple bar graph, not the
stacked kind that I have, so it's not quite what I want. Can anyone help? I
can provide examples (PDF, but I know people don't like to download Usenet
messages with attachments...). Thanks in advance!

Mike

Dear All

In Excel 2000, I have an Excel stacked bar chart, with a secondary axis
setup, without area or line, so as to show extra data above the chart
(sales figures above the stacked bar percentages)

Having upgraded to Excel 2003, these extra numbers have disappeared,
although the series is still there, and all setting seem the same.

Apart from the fact of having to change hundreds of charts.....I can't
even manually recreate my chart! - any time I try to have the data
labels above the chart - they vanish.

Any advice on a) how to manually acheive this...and b) would be
brilliant to find a way to automatically convert through code etc(or
stop Excel 2003 messing it up for me!)

Cheers

I have a bar chart in a spreadsheet with 3 different data series. I want to
have the 3er series plotted agains a secondary axis, but when I do it, the
primary axis bar goes behind the bar series at the secondary axis. Is there
any way I can have both, primary and secondary axis, side-by-side ?

Thanks,
Luiz

I need to have a bar graph with two data sets of different scales, yet when I
change one data set to an secondary axis, the two bars combine. I need them
to be sitting side by side.

thanks

Need to create a stacked bar chart and utilize the secondary axis for data
with a different value range.

Data Table:
Yr 1 YR 2 Pipeline
Small Deals 7 1 13
Med Deals 2 6 14
Large Deals 0 2 20

Primary axis should be Yr 1 & 2 and secondary axis should be pipeline.
Having created the stacked bar chart I change the pipeline series to the
secondary axis and end up with a simple bar chart. How can I create a
stacked bar chart with a secondary axis with a different scale?

Thanks.

Hi!
I am using primary and secondary axis for creating overlapping bars using VBA. The problem is that there should be same scale for both axis, which changes (every time I run the code, there can be different Max value). I was thinking - is it possible:

1) to find max value in column E and assign this value to variable "variable_containing_max_value";
2) Put this variable as maximum scale something like this:

With .Axes(xlValue)
.MaximumScale = variable_containing_max_value
.MinimumScale = I do not know how it affect it (I hope Excel will adjust it automatically)
.MajorUnit = I do not know how it affect it (I hope Excel will adjust it automatically)
End With
3) The same for secondary axis.

Thank you very much!

I am trying to create a dynamic graph where the user can select which series they want to be plotted for the primary and secondary axis (line graph), and what they want the X-axis to be. I am importing data from another source (that code is finished) and have a sheet with a variable number of columns depending on what was imported. I want a checkbox for primary and secondary y-axis series, and a drop-down menu for what the X-axis is.

The method I use right now is to create a second table of data that the graph comes from and create a checkbox based on turning those columns from values to NA()...when I do that it includes all of the series (anywhere from 5-15) on my legend though and I would really like to just include what I am graphing in the legend. I can only figure this out for the primary axis and don't know how to do the secondary axis (there may not always be one).

I can attach a sample worksheet if that would help, but it will basically be a blank graph and big data table.

Things I can't figure out:
-Selecting the x-axis from a drop-down
-Selecting which series to include on a secondary axis
-How to select the series without including every possible series in the legend

Any suggestions or help would be appreciated!

So, i know how to create a chart with a bar and a line and two axis....can do a stacked bar chart....but, can you create a stacked bar chart and include a line over the top of that with a secondary axis?

Hello,

I'm not sure if this is possible but if anyone knows how to do it, I would
be extremely grateful. I have an existing stacked bar chart that I would
like to show two more series. I would like the series in a line format using
percentage values on a secondary axis. I'm working with Excel 2003 if it
makes a difference.

Thanks for your help


No luck finding an answer? You could always try Google.