Combine Bar with Single Line Chart

Hello,
Please can someone help me with this question.  I am trying to create
a bar chart with a single line chart.

The bars will display the totals for various years i.e.

2004 104
2005 107
2006 108
2007 110
2008 101

The line chart will show how 2008 is performing on a weekly basis i.e.

01/01/2008 = 100
07/01/2008 = 101
14/01/2008 = 102

The bar chart for 2008 will reflect this also, updating each time a
new week is added.

The Y Axis I will assume display a total i.e. 99-111 for example.

The X Axis needs to show the dates plotted for the line chart
01/01/2008 - 31/12/2008 (weekly) but how can I display the bar charts
on this axis?

If anyone can help I would be extremely grateful.

Brett
0
1/10/2008 10:06:08 AM
excel.charting 18370 articles. 0 followers. Follow

5 Replies
785 Views

Similar Articles

[PageSpeed] 42

On 10 Jan, 10:06, brettmannin...@gmail.com wrote:
> Hello,
> Please can someone help me with this question. =A0I am trying to create
> a bar chart with a single line chart.
>
> The bars will display the totals for various years i.e.
>
> 2004 104
> 2005 107
> 2006 108
> 2007 110
> 2008 101
>
> The line chart will show how 2008 is performing on a weekly basis i.e.
>
> 01/01/2008 =3D 100
> 07/01/2008 =3D 101
> 14/01/2008 =3D 102
>
> The bar chart for 2008 will reflect this also, updating each time a
> new week is added.
>
> The Y Axis I will assume display a total i.e. 99-111 for example.
>
> The X Axis needs to show the dates plotted for the line chart
> 01/01/2008 - 31/12/2008 (weekly) but how can I display the bar charts
> on this axis?
>
> If anyone can help I would be extremely grateful.
>
> Brett

Sorry it needs to be a column chart not bar!
0
1/10/2008 10:09:50 AM
So the line will overlay the column for 2008?

Make your column chart with the 2004 to 2008 data. Note that 2004 is the 
first category and 2008 the fifth. The year 2008 spans 4.5 to 5.5 on the 
category axis.

I converted the week ending dates to this 4.5 to 5.5 scale using this 
formula:

=(A10-DATE(2007,12,31))/366+4.5

to convert for example the date in cell A10 (1/1/08) to a value of 3.5027. I 
added this data to the chart, which added another column series. I selected 
the new column series, and using Chart menu > Chart Type, converted the 
series to an XY type (not a line type). This gave me my XY series on 
secondary axes, which did not align with the column chart axes. I double 
clicked the line series, and on the Axis tab, chose Primary. Now everything 
is aligned and all that's needed it suitable formatting. I found it worked 
best if I double clicked the column series, and on the Options tab, changed 
the gap width to zero. I also changed the formatting so the columns had a 
fill color but no border.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


<brettmanning24@gmail.com> wrote in message 
news:414693ce-9d8b-47c9-966b-69f2bfbf1dea@v29g2000hsf.googlegroups.com...
> Hello,
> Please can someone help me with this question.  I am trying to create
> a bar chart with a single line chart.
>
> The bars will display the totals for various years i.e.
>
> 2004 104
> 2005 107
> 2006 108
> 2007 110
> 2008 101
>
> The line chart will show how 2008 is performing on a weekly basis i.e.
>
> 01/01/2008 = 100
> 07/01/2008 = 101
> 14/01/2008 = 102
>
> The bar chart for 2008 will reflect this also, updating each time a
> new week is added.
>
> The Y Axis I will assume display a total i.e. 99-111 for example.
>
> The X Axis needs to show the dates plotted for the line chart
> 01/01/2008 - 31/12/2008 (weekly) but how can I display the bar charts
> on this axis?
>
> If anyone can help I would be extremely grateful.
>
> Brett 


0
jonxlmvpNO (4558)
1/10/2008 12:46:54 PM
I should add, change the Y axis scale so it starts at zero, otherwise 
interpretation of the column values will be severely distorted. Excel 
autoscales the axis to start at 94, which makes the 2008 value of 101 appear 
to be less than half of 2007's 110.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Jon Peltier" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message 
news:exPFmb4UIHA.4532@TK2MSFTNGP02.phx.gbl...
> So the line will overlay the column for 2008?
>
> Make your column chart with the 2004 to 2008 data. Note that 2004 is the 
> first category and 2008 the fifth. The year 2008 spans 4.5 to 5.5 on the 
> category axis.
>
> I converted the week ending dates to this 4.5 to 5.5 scale using this 
> formula:
>
> =(A10-DATE(2007,12,31))/366+4.5
>
> to convert for example the date in cell A10 (1/1/08) to a value of 3.5027. 
> I added this data to the chart, which added another column series. I 
> selected the new column series, and using Chart menu > Chart Type, 
> converted the series to an XY type (not a line type). This gave me my XY 
> series on secondary axes, which did not align with the column chart axes. 
> I double clicked the line series, and on the Axis tab, chose Primary. Now 
> everything is aligned and all that's needed it suitable formatting. I 
> found it worked best if I double clicked the column series, and on the 
> Options tab, changed the gap width to zero. I also changed the formatting 
> so the columns had a fill color but no border.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
> <brettmanning24@gmail.com> wrote in message 
> news:414693ce-9d8b-47c9-966b-69f2bfbf1dea@v29g2000hsf.googlegroups.com...
>> Hello,
>> Please can someone help me with this question.  I am trying to create
>> a bar chart with a single line chart.
>>
>> The bars will display the totals for various years i.e.
>>
>> 2004 104
>> 2005 107
>> 2006 108
>> 2007 110
>> 2008 101
>>
>> The line chart will show how 2008 is performing on a weekly basis i.e.
>>
>> 01/01/2008 = 100
>> 07/01/2008 = 101
>> 14/01/2008 = 102
>>
>> The bar chart for 2008 will reflect this also, updating each time a
>> new week is added.
>>
>> The Y Axis I will assume display a total i.e. 99-111 for example.
>>
>> The X Axis needs to show the dates plotted for the line chart
>> 01/01/2008 - 31/12/2008 (weekly) but how can I display the bar charts
>> on this axis?
>>
>> If anyone can help I would be extremely grateful.
>>
>> Brett
>
> 


0
jonxlmvpNO (4558)
1/10/2008 12:54:51 PM
On 10 Jan, 12:54, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
wrote:
> I should add, change the Y axis scale so it starts at zero, otherwise
> interpretation of the column values will be severely distorted. Excel
> autoscales the axis to start at 94, which makes the 2008 value of 101 appe=
ar
> to be less than half of 2007's 110.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. -http://PeltierTech.com
> _______
>
> "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com> wrote in message
>
> news:exPFmb4UIHA.4532@TK2MSFTNGP02.phx.gbl...
>
>
>
> > So the line will overlay the column for 2008?
>
> > Make your column chart with the 2004 to 2008 data. Note that 2004 is the=

> > first category and 2008 the fifth. The year 2008 spans 4.5 to 5.5 on the=

> > category axis.
>
> > I converted the week ending dates to this 4.5 to 5.5 scale using this
> > formula:
>
> > =3D(A10-DATE(2007,12,31))/366+4.5
>
> > to convert for example the date in cell A10 (1/1/08) to a value of 3.502=
7.
> > I added this data to the chart, which added another column series. I
> > selected the new column series, and using Chart menu > Chart Type,
> > converted the series to an XY type (not a line type). This gave me my XY=

> > series on secondary axes, which did not align with the column chart axes=
..
> > I double clicked the line series, and on the Axis tab, chose Primary. No=
w
> > everything is aligned and all that's needed it suitable formatting. I
> > found it worked best if I double clicked the column series, and on the
> > Options tab, changed the gap width to zero. I also changed the formattin=
g
> > so the columns had a fill color but no border.
>
> > - Jon
> > -------
> > Jon Peltier, Microsoft Excel MVP
> > Tutorials and Custom Solutions
> > Peltier Technical Services, Inc. -http://PeltierTech.com
> > _______
>
> > <brettmannin...@gmail.com> wrote in message
> >news:414693ce-9d8b-47c9-966b-69f2bfbf1dea@v29g2000hsf.googlegroups.com...=

> >> Hello,
> >> Please can someone help me with this question. =A0I am trying to create=

> >> a bar chart with a single line chart.
>
> >> The bars will display the totals for various years i.e.
>
> >> 2004 104
> >> 2005 107
> >> 2006 108
> >> 2007 110
> >> 2008 101
>
> >> The line chart will show how 2008 is performing on a weekly basis i.e.
>
> >> 01/01/2008 =3D 100
> >> 07/01/2008 =3D 101
> >> 14/01/2008 =3D 102
>
> >> The bar chart for 2008 will reflect this also, updating each time a
> >> new week is added.
>
> >> The Y Axis I will assume display a total i.e. 99-111 for example.
>
> >> The X Axis needs to show the dates plotted for the line chart
> >> 01/01/2008 - 31/12/2008 (weekly) but how can I display the bar charts
> >> on this axis?
>
> >> If anyone can help I would be extremely grateful.
>
> >> Brett- Hide quoted text -
>
> - Show quoted text -

Thanks John.

I will give this a try.

0
1/10/2008 1:02:47 PM
On Thu, 10 Jan 2008, in microsoft.public.excel.charting,
brettmanning24@gmail.com said:

>"Jon Peltier" <jonxlmv...@SPAMpeltiertech.com> wrote:
>> I should add, change the Y axis scale so it starts at zero, otherwise
>> interpretation of the column values will be severely distorted. Excel
>> autoscales the axis to start at 94, which makes the 2008 value of 101 appear
>> to be less than half of 2007's 110.
>
>Thanks John.
>
>I will give this a try.

I noticed that the annual averages are close to 100 for most years,
making them a little hard to tell apart if the y axis goes down to zero.

Jon is quite right that a zero origin y axis is essential for column
charts, and all visualisations that depend on a perception of area from
the origin. But have you considered abandoning the idea of bars for the
annual average, and using lines instead?  If you format your data like
this...

                Annual    Weekly
31/12/2003
31/12/2003
31/12/2003      104
31/12/2004      104
31/12/2004
31/12/2004      107
31/12/2005      107
31/12/2005
31/12/2005      108
31/12/2006      108
31/12/2006
31/12/2006      110
31/12/2007      110
31/12/2007
31/12/2007      101
01/01/2008      101       100
07/01/2008      101       101
14/01/2008      101       102
31/12/2008      101
31/12/2008
31/12/2008

....then the chart wizard should automatically detect the dates and apply
a Time-scale x axis type, producing the correct graph type if you select
"Line Chart" in the wizard. This would let you have the y axis start
around 90, giving you a good separation of annual averages, without
misleading anyone (lines and symbols don't need a zero origin because
they don't depend on an area, only a difference in position).

And if you still want to see columns, you can quickly select the annual
series, choose "Chart Type.. Area", and columns will appear (they're
areas really, but they look like columns :-)

-- 
Del Cotter
NB Personal replies to this post will send email to del@branta.demon.co.uk,
   which goes to a spam folder-- please send your email to del3 instead.
0
del1907 (586)
1/10/2008 5:55:58 PM
Reply:

Similar Artilces:

How can I create a chart to compare multiple data series?
Using Excel, is there a way to merge multiple charts with different data series into one to demonstrate a direct comparison of the data series within one single chart. I've got 4 charts of the stacked column type. magnoliak77 Tufte calls them small multiples, Cleveland calls them trellis displays. I call them panel charts. Take a look at the examples on this page. http://processtrends.com/toc_panel_charts.htm Another option is to stick with your 4 charts, however, you can size and align them. Here's a link to a simple macro that sizes and aligns all charts on a single sheet....

Mails Combined
HI, I mistakenly delete all mails from Inbox and then found them on the deleted Items and selected all and ask to move folders back to Inbox. the problem is all mails were combined into one email and moved back in to Inbox as a single email. How do i go back to the original version. How do i uncombined all the mails in to individual mails from the right recipient? Please help ASAP.. Submitted using http://www.outlookforums.com ...

outlook icon in status bar?
I have a small icon in the outlook status bar since 1 day. It is a harddisk connected to a network and there is a red cross thorugh the network. ANy idea what it means. Everything seems to be working fine. It probably means you're working offline. Try clicking "Work Offline" on the File menu to toggle back to Online mode. -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:047e01c3cd30$c1da2850$a301280a@phx.gbl, robp wrote: > I have a small ico...

CScrollView with flat scroll bars?
Hello, Is it possible to get CScrollView class window with flat scroll bars? Thanks in advance. Michael ...

merge/combine workshhets
I need to merge about 75 worksheets into one spreadsheet on a regular basis - all have exactly the same format/layout etc back can vary in the number of rows Try this Jeff http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Jeff224" <Jeff224@discussions.microsoft.com> wrote in message news:C6790506-2888-4F4D-80EA-BB0D9F9B27A2@microsoft.com... >I need to merge about 75 worksheets into one spreadsheet on a regular basis - > all have exactly the same format/layout etc back can vary in the number of > rows Or http://www.rondebru...

How can I copy a list from word into a single cell in excel
Data in word is presented as a bullet list. I have copied this data into a new doc and removed the formatting to leave just the list of items. I now wish to copy this data into excel but want it to appear in one single cell retaining its list format rather than continuous text Well, you can save it as TEXT and then read into WORD. If you really want it in one cell, you can then concatenate the values together using a formula similar to this =A1&B1 If you want a space between the data, use =A1&" "&B1 Remember that there is a cell length limit that you may come...

numbers in data sheet of stacked bar chart
I'm trying to label segments in a stacked bar chart, example: 1-10, 11-20, etc. I'm not able to enter the number with the dash. In most cases it automatically changes to Jan-10. Any tips on how I can fix this? Thanks! Format the cell as text and see if that helps. -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2010? http://www.echosvoice.com/2010.htm Fixing PowerPoint Annoyances http://tinyurl.com/36grcd PowerPoint 2007 Complete Makeover Kit http://tinyurl.com/32a7nx "Genevieve" <Genevieve@discussions.microsoft.com> wrote in me...

Adjusting menu bars at top of IE 8 screen
I just downloaded IE 8 after using IE 6 for many years because many sites are starting to require a more up to date browser. It seems that the menu bars now take up more space at the top of the screen and the things that I need to use (home page, etc) are smaller and harder to find. I don't want to lose space at the top of the screen and make the ions I use larger. After looking around I noticed that under tools I can select full screen which gives me more space but then I lose my boxes for different screens at the bottom of the screen. Also there is a McAfee bar that tak...

Finding the combination that appears more times
Hi everybody i have a list on excel 2007 that displays the purchase of items on columns A:E, each column showing one (1) item I want to find what combination of items appears more times, especifically, which combination of four (4) is the favorite mix eg: A B C D F A C D E F B C D E F B C D F Z in this example, the winner would be (B, C, D, E) as it appears 3 times the main problem is that I have over 1,000 rows and trying with =SUMPRODUCT(COUNTIF(A1:E1,$N$1:$R$1)) por each combination would take forever... is there a simpler, quicker ...

Problems with Stacked Bar charts
Seeing a weird thing. When I create a stacked bar or column chart, I get bars that are one pixel wide so it looks like a vertical line. Closed the sheet, re-opened, new sheet, new data, nothing works. If I embed a worksheet in Powerpoint, and in that sheet, create a new worksheet with the data, it works fine... Baffles me !! Also, excel seems incapable of creating a graph with dates on the X axis, li lists them as ascending numbers, 1.2.3 etc. Again, works fine in an embedded spreadsheet in Powerpoint. As they use the same graph engine, I'm stumped. Hi, Excel is assuming you want yo...

Purchasing Error: You can't change the status of this line item; it has unposted receipts assigned to it.
User is trying to close a PO. Goes into Edit PO Status window and searched on PO, and returns two line items (two different receipts). First item has status of Released. Tries to change to Closed, and gets error: You can't change the stauts of the line item; it has unposted receipts assigned to it. Have searched through interface and SQL, and cannot locate any unposted receipts for this PO/item. Noticed that the qty on the original PO for this line item is 32 and qty on receipt is 31 (not sure if relevant or not). Have also Check Links on Purchasing module, but did not resolve. Any su...

Combining 2 Excel (xls) files
I have 2 xls workbooks with different sheet names and both with different range names. I would like to combine them both into one workbook so that I don't have to re-create all of the range names. Is there a way to do that? -- LAF ------------------------------------------------------------------------ LAF's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9656 View this thread: http://www.excelforum.com/showthread.php?threadid=346017 Hi LAF- Open both files (let's call them A & B, and assume A will be your "combined" file). Working i...

Can I format an entire row based on the value of a single cell?
I would like to use the conditional formatting feature to format an entire row rather than just a single cell. For example: If the cell value = "Total" then the entire row is bold. select your range (multiple rows???). Then with A3 (say) the activecell in that selection: format|Conditional formatting formula is: =$a3="total" apply a nice format LTShelley wrote: > > I would like to use the conditional formatting feature to format an entire > row rather than just a single cell. For example: If the cell value = "Total" > then the entire row is bo...

How do I lock down a mailbox so it can only send to a single addre
We have set up an account that several people will share, but only to send single mails to a single, fixed email address. Is there a way that I can lock this down so that mail cannot be sent elsewhere? Thanks in advance. John B On Tue, 25 Oct 2005 03:27:04 -0700, John B <John B@discussions.microsoft.com> wrote: >We have set up an account that several people will share, but only to send >single mails to a single, fixed email address. Is there a way that I can lock >this down so that mail cannot be sent elsewhere? > >Thanks in advance. > >John B You would real...

Combining Publications
Can I combine two different publications into one? Suzi wrote: > Can I combine two different publications into one? ========================================= Personally, I would simply open two instances of Publisher (one document in each) / tile the two screens...and copy/paste. Maybe the following link will be useful: Combining Publisher Documents http://tinyurl.com/2lpj5w -- John Inzer MS Picture It! - Digital Image MVP Digital Image Highlights and FAQs http://tinyurl.com/aczzp Notice This is not tech support I am a volunteer Solutions that wo...

How do you force another line in a text box?
When using text boxes. how do you force another line when character spacing reaches a certain point. A text box is a single line. Full stop. A text area will automatically wrap when the user types to the end of the visible space - this does not, however, add a line feed. To add a line feed after a preset number of characters, as I said in an earlier post, you need server side scripting which parses the data, and adds the line feed at the required intervals. Of course, this is complicated by needing to account for spaces between words - and avoiding the line feed in the middl...

Sliding navigation bar on right of word docs is missing?
How can I get the navigation bar to show on the right side of my word docs? It is hard to go from page to page by using the wheel when a document is 90 pages. Maybe the document window has been dragged so that it is wider than the screen and then positioned so that the scroll bar is off the screen. Click on the button to the left of the White X in the red background at the top right of the screen and see if that restores the scroll bar. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug R...

Getting Pivot Chart to Display Correctly
I am using Access 2000. I have created a database using a form. When I attempt to create a pivot chart it doesn't display the data just the primary key for each data field. How do I resolve this? -- Thanks, MichaelA ...

Dual Axis stacked bar.
Hello, Thank you in advance for any and all the help. I have 4 sets of data. I have: The date which i want to be on the X-Axis I have two $ values that i want stacked in a bar chart with the $ number on the left Y-Axis And finally i have the % value which will be shown on the right Y-Axis but as a line chart. Can this be done and how? Again, thank you. Hi, Easiest way is to create a stacked bar chart using the 3 sets of data and then format the 3rd data series to be on the secondary axis with a chart style of line. So for example your 4 sets of data are in the range A1:D13. B1:D1 ...

How do I update charts in excel 2007
Hi - I'm tracking and charting real estate data in three cities, by month. I can add updated info into the worksheet columns, but I can't get the corresponding points into the line chart that I made three months ago from the columns. Now using excel 2007. Had no trouble with this task in excel 2003, becouse I could easily change the data range and then the chart followed the changes. I'm new in 2007, and when I find the data range and change it to correspond the with the updated columns nothing happens in the chart Appreciate help I have a line chart on the s...

How to Combine Data in Different Columns
Hi Everyone I need help in this issue. I have tried many ways though some wer successful but they were not efficient. I have say 5 columns of data and the data could be on the same rows o all 5 columns or on different rows hence if I need to combine all th data of these 5 columns, I would have to sort or filter each of the manually and copy and paste to a different worksheet or column. This i very tedious and time consuming. I also tried to write a macro to loo for data in one column then put them into another column but i was no successful. However, experts like you guys shouldn't have ...

Command Line Interface
Hi, I have a rather peculiar problem, I need to impliement a command-line style interface into my MFC dialog program which can output, read / write strings etc. The thing is the functions I need to step them through are extensive, and do need to be written in C++ function forms, in effect I need to write a console app, within a dialog app, and have the console as part of the dialog window. Does anyone have any idea's how I could impliment such an interface? I understand this may be pretty vuage, - MR Read the second question in http://msdn.microsoft.com/msdnmag/issues/04/0...

combinations
I have a finite number of values that represent length (right now I have less than 20 values). Can Excel take these values and list all possible combinations of the values? Any pointers appreciated. Stephen R. Stephen, No. There are 2,432,902,008,176,640,000 possible ways to combine 20 different values. That is, unless you have another limitation, like only using 4 at a time.... HTH, Bernie MS Excel MVP "Stephen R" <nospam@nospam.com> wrote in message news:u9ZiYhOeFHA.1456@TK2MSFTNGP15.phx.gbl... > I have a finite number of values that represent length (right...

Scatter chart for real-time tracking of a single cell value ?
Hi. I use Excel 2003. On a spreadsheet connected to a DDE data feed, I have a cell containing an integer number that updates and changes constantly during the day. I would like to chart the number in that cell in real-time second by second between certain hours of the day. I have been told that it may be possible to use a scatter chart to do this but I have not been able to make it work or to find useful help files. It seems to me that the problem has to do with charting a single cell rather than a range but I'm not sure. Any practical advice or pointer to a "how-to" web site w...

Hiding lines in detail
Hi, I'd like to hide a line when a field from a query = a 0 or IS NULL. Meaning: Client_name Bill# Total Joe Brown 123456 $1.500 (this line should appear) Joe Brown (this line shouldn't appear) HOWTO do it? TIA Ana Access 2003 Ana wrote: > Hi, > I'd like to hide a line when a field from a query = a 0 or IS NULL. > Meaning: > Client_name Bill# Total > Joe Brown 123456 $1.500 (this line should appear) > Joe Brown (this line shouldn't appear) > > HOWTO do it? > TIA > Ana > Acce...