Free Microsoft Excel 2013 Quick Reference

Chart with Secondary Axis but Bars are Overlapping

Hi – I am trying to chart two data series in a bar chart with two axis. I want the bars to be adjacent to each other, not stacked one on top of the other.I have tried editing the gap overlaps but this does not seem to help. Any ideas?


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.

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'm looking to create a stacked bar chart with primary axis and secondary axis side by side, with my primary values (percentages) to the right of zero and secondary values (currency) to the left of zero. I almost want a Positive-Negative/Tornado chart but I want to be able to graph two different variables/number formats on the X-axis adjacent to each other.

I'm trying to create a stacked column chart, but with column D requests on the secondary axis.

I used a tutorial on-line to create a stacked column chart with the total displayed above the bar, but it seems in 2007 I can't get even this to display the data label above the bar.

http://www.ozgrid.com/Excel/stacked-column.htm

What am I missing here?

hi folks
Apologies for this but I am absolutely stumped. I have some simple data and i want to create a horisizontal bar chart with 2 axis (X axis) the top one showing number of stocks number up to 270 and the bottom with Market Cap up to 1,200. (See attached)

I set up the chart and it looks fine but it has only one axis so in order to create a second axis i right click on the blue series (no of Stocks) and go through the motions of setting up a secondary axis. However when i do that it ends up looking like the chart to the right with the Bars Cojoined (which is not what i want) I cannot seem to achieve 2 axis with separated bars.

Sorry about the long explanation but this one has me exasperated..!

Cheers
James

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

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'm trying to do a stacked chart, based on reasons for returns. The chart for January is okay for last month, but going forward they want to see the reasons stacked and Customer Returns in $ on one axis and % Monthly Sales on the other axis.

I can do a stacked chart, but I can't get it to add the secondary axis correctly.

Any idea how I can do this going forward?

Daily Returns Report (Excel Forum).xlsx

Is it possible to make a 3D column chart where one series is plotted on a secondary axis?

Ideally, I would like to use the 3D column column subchart type (bottom left hand column of the chart type selector). This chart type allows comparison across categories and series. Alternatively, a clustered 3D chart type would also work (middle left hand column of chart type selector).

I can get a flat 2D clustered chart to plot series2 on a secondary axis. But when I try doing the same thing on either of the 3D options, when formatting series2, the axis tab is missing, so I can't specify the secondary axis.

I've attached my example of what I've tried.

Any ideas or fixes?

Thanks, cat

I have a chart with two axis. One showing value and one showing percent. When the curve or column goes of the chart I update the scale but then the category axis is not aligned (0 value and 0% is not on the same horisontal axis any longer). I then need to recalculate and manually adjust the scale to have 0 on same horisontal axis.

Is there any way of having this done automatically, it's currently not an optimal situation since it's time consuming to correct all the charts (the original file has many charts).

Attached is an example with data sheets and chart sheet.

Regards,
Niklas

Financial Analyst

Using:
Excel 2003, Win XP

Hello,
is it possible to create a 3D chart with x axis in log scale? The other two axes are linear. I have Excell 2007.

Many thanks for help.

Lubica

I have two data series, one for 'this month' and one for 'year to date'. I am plotting these in a clustered column type graph. I am try to plot the 'year to date' series on a secondary axis but whenever I select the series and use 'format data deries' 'axis' 'plot series on - secondary axis' the graph converts to a stacked column. I am using Excel 2003 via a thin client to Windows server 2003.

Hi all,

I'm working with one bar chart with two sets of data. I've set up a secondary value axis, and now all I need to do is get the bars next to each other instead of overlapping each other. I've attached my example.

Any help is much appreciated!

I want to plot # of deliveries and $ by quarter. The # of deliveries needs
to be on the y-axis and the $ need to be on the secondary y-axis. Both
series have the same x-axis data (quarter). I need to have them show up as
bar charts with a gap between range data.

Every time I do it, it just sets one set of data on top of the other.

Is there a better way??

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


Hi All,

I have made a stacked bar chart with a nested category axis (x-axis). For each main category there are 2 subcategories. eg Stage1 Project1 and Project2, Stage2 Project1 and Project2, etc.

The bar for each Project is a stack of 2 costs: primary cost and secondary cost.

I would like to put my projects on the z-axis, maintain my stages on the x-axis and keep my stacking.

Any thoughts?

Thanks,

Jen

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

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

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

Hi,

What I'm trying, in vain, to do is create a chart with 4 columns with one of those columns representing a stack of 2 sets of data.

e.g. Category A 5
Category B 8
Category C 4
Category D 3
Category E 15

Category E should be stacked on-top of D. I have tried doing this by putting D and E on the secondary axis, but this positions the stacked column in the middle of the other data on the primary axis. I have got it to work by placing blank data on the primary axis in the position that the stacked column appears, but this is not perfect because it means the categories are out of their natural order.

Does anyone have any ideas?

Thanks,
Matt

Hello all,

I'm creating a small macro to set a chart's secondary axis acccording to the primary axes (I need to use dual-unit labeling, ie kWh/m˛ and kBTU/sqft, and keep it updated if the primary autoscales itself). So i've got the following, but it doesn't like it. Says runtime 438, object doesn't support this property or method, at the .MaximumScale = ActiveChart...

Code:
    With ActiveChart.Axes(xlValue, xlSecondary)
        .MinimumScale = 0
        .MaximumScale = ActiveChart.Axes(xlValue, xlPrimary) * 0.317
        .MinorUnitIsAuto = True
        .MajorUnit = 3.17
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        .DisplayUnit = xlNone
    End With
What should I be using instead of ActiveChart. Also, what's the best chart event to put this under to keep it updated?

Thanks!

First post from a "casual" Excel user (more familiar with SigmaPlot)...

Client wants a line chart with x-axis linear (baeline to 36 months) but
there are only data for 2 baseline conditions and certain months:
on-drug
off-drug
M3
M6
M12
M24
M36

The Y axis (# of pellets) is nothing special.

I entered blank rows, corresponding to the missing months, into the
datasheet, like so:
on-drug
off-drug

M3

M6

M12 (etc.)

This "method" plots the data points okay, but there are 2 problems:
(a) there are no lines connecting them, and
(b) the x-axis labels are lousy

I worked around "b" by manually adding text boxes instead of x-axis labels,
but the missing lines problem is a major pain (I manually added lines, but
sometimes they don't hit the data points on center, and any resizing is a
bear!).

Is there a better way for Excel to deal with missing data and draw the lines
automatically, as it does if there are no empty rows in my datasheet?

Any help appreciated. Sorry for the length, but I figured it's better to be
specific.

Thanx

Hi,

I am trying to create a chart that looks something like this:

process1 process2 process3 process4
project a 9/11/2006 10/9/2006 11/6/2006 11/20/2006
project b 9/11/2006 10/9/2006 11/6/2006 11/20/2006
project c 9/15/2006 10/13/200611/10/200611/24/2006
project d 10/30/200611/27/2006 12/25/20061/8/2007

and then on the value (y) axis have dates listed( at half intervals,
1st of the month and 15th of the month) listed
or
have just each month listed.

basically each project has its own time line, every project has the
some process but at different dates.

i have been spinning my wheels on this all day to get a pretty chart.
with no luck, on getting the exact information in the right place so it
all makes sense.

if anyone has any ideas. i would appreciate it very much.

Hi...I am trying to create a dual axes column chart in MS Excel 2003. But the
moment I add the secondary axis the bars are getting overlapped.