Help with Bar-Line Chart..not aligned??

Hi All,

I am trying to plot a line chart over a column chart (4 categories). The 
columns on the primary y-axis, while the lines on the secondary y-axis. The 
problem is that the line charts starting points are always at the middle of 
the data series but not properly aligned with the columns. I want to shift 
them and align them with the column charts. Here is the data. 

	               2011          2011	2012	 2012
	               Volume    % of sector	Volume	% Sector
Company 1	150           7%	310	11%
Company 2	170	5%	320	12%
Company 3	160	4%	330	13%

If you plot them in columns, you will see 4 groups on the X-axis: Volume 
2020, % Sector 2020, Volume 2030, % Sector.

But I want it to be showing only 2 groups (representing years)on the X-axis: 
2020 and 2030, and in each year, there are 3 columns (for volume on the 
primary y-axis), and over them there is the line chart (on the secondary 
y-axis):

one blue line starting from Company 1 column at 7% on the secondary y-axis 
in 2011, extending to Company 1 blue column in 2012 at 11%. Another red line 
for company 2 following the same idea. And a green line for the last green 
column for company 3.

I look forward to your feedback.

Thanks in advance
0
Dream (1)
1/13/2010 6:52:01 AM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
470 Views

Similar Articles

[PageSpeed] 3

under 'format axis' for a horizontal axis you will find a check box for 'on 
tick' or 'between tick' modify each one (primary and secondary) and you 
should be able to get near to what you want

"Dream" wrote:

> Hi All,
> 
> I am trying to plot a line chart over a column chart (4 categories). The 
> columns on the primary y-axis, while the lines on the secondary y-axis. The 
> problem is that the line charts starting points are always at the middle of 
> the data series but not properly aligned with the columns. I want to shift 
> them and align them with the column charts. Here is the data. 
> 
> 	               2011          2011	2012	 2012
> 	               Volume    % of sector	Volume	% Sector
> Company 1	150           7%	310	11%
> Company 2	170	5%	320	12%
> Company 3	160	4%	330	13%
> 
> If you plot them in columns, you will see 4 groups on the X-axis: Volume 
> 2020, % Sector 2020, Volume 2030, % Sector.
> 
> But I want it to be showing only 2 groups (representing years)on the X-axis: 
> 2020 and 2030, and in each year, there are 3 columns (for volume on the 
> primary y-axis), and over them there is the line chart (on the secondary 
> y-axis):
> 
> one blue line starting from Company 1 column at 7% on the secondary y-axis 
> in 2011, extending to Company 1 blue column in 2012 at 11%. Another red line 
> for company 2 following the same idea. And a green line for the last green 
> column for company 3.
> 
> I look forward to your feedback.
> 
> Thanks in advance
0
Utf
1/13/2010 4:19:01 PM
Reply:

Similar Artilces:

excel resets charts SizeWithWindow and Zoom
I have a series of procedures which generate a lot of statistics and charts and set the charts' property .SizeWithWindow = False and the window magnification .Zoom = 100 Everything works fine and the charts are output as specified in the vba code. But after I close the workbooks and reopen them, excel has reset all the chart Views to Sized With Window (checked) and Zoom to 127%. Is there a way to prevent excel from resetting the View and Zoom properties? Thanks for any help or suggestions. ...

Getting exact cell / Range from the pie chart
By selecting data point on the pie chart, I want to get corresponding cell/range ( e.g E6 or E6:E8). I can get the values ( ActiveChart.SeriesCollection(1).Values), but can't seem to find how to get exact cell. Will appreciate your reply. Thanks Suyog Suyog - Excel doesn't make it easy. You can get the series formula, and parse it to extract the range of interest. John Walkenbach shows how to use a class module to do just this on his web site, http://j-walk.com. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://Peltie...

PLEASE HELP! WMM to DVD problem
Hi All, First let me apologize for not having the time to make a formal introduction post but I have been pulling my hair out here trying to take what is a nice and clear 30 minute video in WMM and transfer it to DVD with custom menu and such. I am using Winodws Vista on a Sony Vaio laptop. Now the film as I see it in WMM is nice and clear and the sound is nice and stereo-sounding. It is an .avi in the timeline and is only one large file that is already edited. As I said, it looks clear and sounds clear in the preview pane. Now I have ripped through an entire 50 pack of blank ...

How long "IF" can work? -help
Hi, I'm using the "IF" command to automatically set my students gred from A,B+,B,C+,C,D, and F. It's all work well until this semester the institution revises the gredding scheme to A,A-,B+,B,B-,C+,C,C-,D+,D, and F. My problem is that I can't seems to program more than 8 (9 if including the final arguments) but i need to be able to put 11 arguments in the formula. Is 9 the final limit of the "IF" command in Excel 2000? How to overcome this problem and how about Excel 2002/XP? If anybody can clarify this? thank you. Fawaz, Malaysia. Try using VLOO...

Display max and min on bar graph #2
Hello all. I am constructing bar graph to display the numerical results of our quality metrics, and my boss would like the maximun and the mininum displayed for each category as dots over and in the individual bars. I know I can add error bars, but is there any way to do it as a simple dot and not have the lines? I hope that makes sense. Add a series for the max and a series for the min. Change the chart type for each series to LINE and change the point display as needed. "SWeyer" <SWeyer@discussions.microsoft.com> wrote in message news:022A0A3A-C4C9-4700-88F4-DDEFD5...

Macro help needed... #2
New to VBA, I'm struggling to do the following in a worksheet:- For all the cells in the sheet; Begin. If the cell.interior.colorindex is 1 (black) or 15 (light grey), do nothing except move on to the next cell and go back to Begin. If the cell value has anything other than 2 or 3 characters or letters, do nothing except move on to the next cell and go back to Begin. Otherwise, using Select Case, set the background color to an index linked to a particular string in the value, except that if the 2-or-3 character value is not found in the case list, do not change the backgroun...

Chart copying messes up number formats...
I have a workbook with charts that I update each month. The data used are included in this workbook. When I finish updating the charts I select the sheets with the charts and use the copy sheet functions to create a copy of the charts to a new file. When I save the file everything looks ok with the number formats and everything. When I reopen the file I get another number format. If I open the original chart file, everything sorts out OK (!!!) and when I close it everything messes up again! I tried breaking the links (office 2003 links functions) when I still have open the original chart file,...

HELP Needed: AddJob function not working from XP desktop
I have an application that has been running for many years. It uses AddJob to send an ai file to the printer. Now we are starting to put some XP machines on our desktops and the print functions are not working. My code calls AddJob with Null as the buffer. The return value in cbNeeded tells me how large the buffer should be. So then, I create the buffer and call AddJob again accordingly. This works fine under other operating systems.... 95, 98, etc.... With XP, I'm getting error 1784: The supplied user buffer is not valid for the requested operation. code is similar to: BYTE* bBuf = ...

Disabled AD account--Help!!
We had recently termed an employee, and our first step was to disable the account. We re-enabled it shortly thereafter to back up the email, however, it will not let us log into that account. I've reset passwords, updated RUS several times, and waited about 45 minutes. I just disabled it and enabled it again to see if it would kick into gear with no success. So as it stands now, the account is Enabled, with a fresh password reset, but I cannot get into the account. Our only logon attempts thus far have been through OWA... [UPDATE] I can log into a domain computer with the account,...

adding item to title bar & system menu for proprty page/sheet dial
Hi, How can I add the minimize button on the propertysheet/page dialog? I tried to override the Create/CreateEx member of sheet/page class, but it didn't help. Also, which CWnd should be used to add items to the dialog's system menu? and which class should handle the OnSysCommand? Regards, Ashutosh See below... "Ashutosh" wrote: > Hi, > How can I add the minimize button on the propertysheet/page dialog? I tried > to override the Create/CreateEx member of sheet/page class, but it didn't > help. ********** What "didn't help"? Why don&...

Owners of HP dv6-1299er Artist Edition help please
Could someone with HP dv6-1299er Artist Edition laptop send a file for me with its original Artist Edition 2 theme. I reinstalled my Vista and lost it. Thanks a lot. -- AFletcher "AFletcher" <guest@unknown-email.com> wrote in message news:8e33ff091635227c7f93be803cea0d17@nntp-gateway.com... > > Could someone with HP dv6-1299er Artist Edition laptop send a file for > me with its original Artist Edition 2 theme. I reinstalled my Vista and > lost it. Thanks a lot. > > > -- > AFletcher ....and your Vista problem is? -- "...

how can I show 0 values in a pivot chart?
I want to see 0 values on my pivot table, ie: 10 8 6 x 4 x 2 x Jan Feb Mar June 6 4 0 2 But March lable disappears, I get: 10 8 6 x 4 x 2 x Jan Feb June 6 4 2 Can anyone help? Hi Liz, Turn on the Show items with no data option. What version of Excel are you using? -- Cheers, Shane Devenshire "LizPM" wrote: > I want to see 0 values on my pivot table, ie: > 10 > 8 > 6 x > 4 x > 2 x > Jan Feb Mar June > > 6 4 0 2 > But March lable disappears, I get: > > 10 > 8 ...

Still have probs with Milestone Chart
I need a chart with a list of Project Milestones going up the Y axis and dates (months) across the bottom X axis. I will then plot series with markers showing when each Milestone was completed. I can get the chart to work with the Milestones as the X axis and the dates as the Y axis, but this will not do. If I could rotate the chart through 90 degrees I may be able to do something with it?? It was suggested to me that I use a scatter chart, but I have had no luck in doing so. Can anyone help? Rgds John Hi John, John Walkenbach has a timeline sample at http://www.j-walk.com/ss/exce...

How do I get rid of this black box in my Excel charts?
I keep getting this crazy black box on my Excel charts. Distorts the chart, then jumps from one to another. Cannot get rid of it and it's driving me crazy! Help! On Thu, 3 Nov 2005 17:10:11 +0000, Margi wrote (in article <D1D4C9A9-1A0F-46E3-8816-FFDD5F6F9757@microsoft.com>): > I keep getting this crazy black box on my Excel charts. Distorts the chart, > then jumps from one to another. Cannot get rid of it and it's driving me > crazy! Help! Why in Hades do we get Excel posts in here?! Outlook posts I can sort of understand, being that O comes directly before...

bringing a Publisher 2000 file into 2003
I brought in a file created in Publisher 2000 and the charts (pie, bar, etc.) now have values that are printing on top of each other, stretch as if justified (they aren't), and just look different. Same printer, font, size as before. Tried inserting as picture, same results. What do I need to do to get them looking like they were in 2000? I'm sure there is an explanation but I can't find it in help. Thanks in advance for any and all help. How large is the file? Can you send it to me so I can take a look? Is the text wrapping correctly? Select a picture, right-click, format p...

HELP! Seeking Instructions to Create Report of POS (NOT RMS) Discounts Applied #3
We need to run monthly reports to determine how many of our different Discounts are claimed/used and the proceeds not tendered. We have offered a number of discounts to customers and employees as incentives and cannot find a method to track how each discount is applied (e.g., buy one, get one free with coupon). The built-in Discounted Sales report in POS 2.0 lists only the resulting Sale Price; we seek to have the report sorted by PresetDiscount type but the Advanced Report Filter dialog does not list Discount in the Field dropdown box and we can't locate the table in which transactions...

Creating bar charts using value ranges for each entry
I want to create a bar / column graph using Microsoft Graph where each data entry consists of a range of values (i.e. has a lower and upper value). For example: I have 3 different products. The price of each product will vary (have a lower and upper price - a range). Let's say Product one's price ranges from $75 - $100, Product two's price ranges from $65 - $120, and Product three from $60 - $115. I want to show graphically the three product prices as three blocks/bars next to each other (for comparison). Hope you understand what I'm asking here. Please help as I&...

Error setting Rowsource using VBA for Chart
I have a report that contains an unbound Microsoft Graph Chart. I want the chart's row source to be set based on a query plus some user input from a previous screen. I've tried putting the code in the Activate() Event, Open() Event, and Page() event and I get errors on all of them. The Activate and Open events don't even seem to see my report and I'm thinking it's because it's not technically "open" at the time they run...? The Page event gives me a different error that I can't set the Rowsource property after "printing" has started (I...

Trendline Chart Data
Hi, Besides guessing, can I get the data points from my Trendline on my Chart? I am looking for amounts from the Trendline. Thanks, Stephanie In article <76A74657-1675-4CB6-A751-F89EC23A5279@microsoft.com>, steph44haf@discussions.microsoft.com says... > Hi, > > Besides guessing, can I get the data points from my Trendline on my Chart? > I am looking for amounts from the Trendline. > > Thanks, > > Stephanie > I am not sure what exactly you want. Do you want to see the equation of the trendline? If so, double-click it and from the Options tab set ...

Display chart data on selected employees
Greetings, Experts! (Sadly, I am not one of you) While working at a company here in San Diego, a gal in our departmen created a chart that could display company computer sales by model b selecting the model from a combo box located right there on the chart. For example, if the viewer selected "Model ABC123" from the combo box the chart would automatically change to only reflect daily sales value for that model only. I don't know anything about visual basic, and that is what the chart' macro is written in. Her macro looks up the models from a list o models, then gathers t...

Displaying a TOTAL (using a data label) on a Stacked Bar Graph
Please forgive me if this problem has already been solved. I need to display a data label at the end of a stacked bar that represents the Total of the individual components of the stacked bar. Any help would be greatly appreciated. Thanks. Bob - You could add one more series, call it Totals or anything, but plot all zeroes. Put the totals into another range in the sheet. Then use Rob Bovey's Chart Labeler (http://appspro.com) or John Walkenbach's Chart Tools (http://j-walk.com) to apply the total values in the worksheet range to the Totals series. The series won't show...

Line charts with missing data points in Excel 2007
The data points will not connect in a line chart due to missing data in between. I see a thread where you can solve this problem by selecting the interpolation option in Tools menu > Options > Chart tab. But I can not find this Chart tab options under Excel option in Excel 2007. Could someone please tell me how to deal with this problem in Excel 2007? Thank you very much. turen turen - Right-click a point of the data series, choose Select Data from the shortcut menu, on the Select Data Source dialog box click Hidden and Empty Cells, click (Show empty cells as:) Connect da...

VLOOKUP Reference Help
Currently, I have a cell referencing a table on another sheet like so: =VLOOKUP(A5,'Sheet2'!$J$4:$J$1998,1,TRUE) But what I want to do is instead of referencing the sheet explicitly (i.e. Sheet2), I'd like to reference a sheet based on another cell's value. E.g.: =VLOOKUP(A5,'A2'!$J$4:$J$1998,1,TRUE) where A2 would have the value "Sheet2". Is this possible within excel? -- Thanks! Max Use the INDIRECT() function, like so: =VLOOKUP(A5,INDIRECT(A2&"!$J$4:$J$1998"),1,TRUE) -- Regards, Dave "Max" wrote: > Currently, I have...

Help with Status Table
Hi, I have a table with the following fiels ID, Date, Time, Program, Status 1,11/25/09, 6:45 AM, 6261000, Send 2,11/25/09, 6:47 AM, 6261000, Send 3,11/26/09, 7:30 AM, 6261000, Receive 4,11/26/09, 7:30 AM, 6261400, Send 5,11/26/09, 7:40 AM, 6261400, Recive 6,11/26/09, 7:30 AM, 6261500, Send 7,11/26/09, 7:40 AM, 6261600, Receive 7,11/26/09, 8:40 AM, 6261800, Send 7,11/27/09, 8:50 AM, 6261800, Receive 7,11/27/09, 8:52 AM, 6261800, Receive I would like to show the "Status" of this records, so I end up with a table like: Program, Status 6261000, OK (doesnt matter h...

Help!! How do I fix mistake in statement download set up
I have Money 2004 Premium. I have 2 accounts with the same account number. One for 2004 and the other fro previous years. The first time I downloaded a statement from my bank Money asked me if I wanted to change the account to download the data to. I mistakenly didn't specify the correct account. How do I change things to specify the correct account? Thanks much, Joe See http://umpmfaq.info/faqdb.php?q=150. "Joe Shannon" <anonymous@discussions.microsoft.com> wrote in message news:227c301c45d82$491d11b0$a301280a@phx.gbl... > I have Money 2004 Premium. I have 2 ac...