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

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.

5 Replies
968 Views

Similar Articles

[PageSpeed] 4

```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
>
> 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
> 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
>
> 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
>> 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
>>
>> 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

> >> 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,
```
 0
del1907 (586)
1/10/2008 5:55:58 PM

Similar Artilces:

Need to show percentage reduction on bar chart
Hi. I have a chart with two stacked columns on it. Say the values on the left stack are 200, 100, 80 and on the right stack 120, 40, 20. I want to display the totals on top of each stack ie 380 and 180. Secondly to the right of the right hand stack I want to show the percent reduction in each component. So 40%, etc. Is there a way to do these two tasks? Thanks. Put the totals (380, 180) into the worksheet, and add these as a new series. The series will be added as another stack, but right click on it, choose Chart Type, and select a line type to change it. The points are aligned ...

saving a chart
When I make an excel chart, and save it, then try to later retrieve it, I get back to the spreadsheet and have to start over with a new chart. Can you save a chart with the properties you selected for the chart intact? The only way around this so far is to copy the chart to the clipboard and then open in an image program I am using PAINT for that. What version of Excel are you using? I do not have this problem. "Glenn Vatter" wrote: > When I make an excel chart, and save it, then try to later retrieve it, I > get back to the spreadsheet and have to start over with a ...

Trendlines in Pivot Charts
How do I add a trendline to a chart based on a pivot table? Is it possible Thanks Leroy - Did you try this? Right click on a series, and select Add Trendline from the pop up menu. This works in Excel 2000 and XP (2002), and presumably for 2003 as well; Excel 97 didn't support pivot charts. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Leroy wrote: > How do I add a trendline to a chart based on a pivot table? Is it possible? > > Thanks Jon - I am having a similar problem - I hav...

A "C" in the middle of my chart
There is a "C" or "(" that is showing up in the middle of my Excel bar chart, and I don't know how to get rid of it. Any ideas? Thanks, Em Move the cursor slowly up to the character (it's a textbox) until the cursor changes to the four arrow icon, click once to select the textbox, and press Delete. It might be easier to select the textbox if you select the "Select Objects" button (looks like a cursor arrow) on the Drawing toolbar. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://Peltier...

Common word in line
I am analyzing a report with about 10000 transactions (Cheques paid) and I want to know which lines contains the word "Rent" or "Rental". Any help would be very much appreciated. Fernando Gomez Assuming your transactions are in col. A, put this in col. B and fill down: =IF(SUM(COUNTIF(A1,{"*rent*","*rental*"}))>0,1,"") Any row with a 1 next to it is means "rent" or "rental" appears. HTH Jason Atlanta, GA >-----Original Message----- >I am analyzing a report with about 10000 transactions (Cheques paid) and ...

How do I get rid of new lines?
I have an excel spreadsheet that has a column where the text has been entered in multiple lines. I need to convert this spreadsheet to a .dbf or an excel document. Whenever I try, I lose the data after the first line. Any ideas on how to keep all of the multi-line data? Thanks. Melanie To clarify, your excel document has "carriage returns" (multiple lines of data) within a single excel cell? Excel's CLEAN function should strip out the carriage returns and leave you with a single, long line of text. Syntax is simple =CLEAN(A1) takes out the carriage returns in cell A1, al...

insert blank line when text changes
Hi, I was wondering if there was a way to write a macro or something else... so while scrolling down a list everytime the text changes (in this case a name) a blank line would be inserted. Hi try the following macro. It tests column A and inserts a blank row if the values change Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "A").End(xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index, "A").Value <> Cells(row_index + 1, "A").Value Then Cells(row_index + 1, "A").Ent...

Table and Chart
I have three variables: Screen (month and year), Eligible (yes/no), and Enrolled (yes/no). Screen starts on March 2005 and will continue for several years to come. I want to create a column chart that shows for each month and year the number of people who were eligible and enrolled. Below is a sample of the data. I think I want something like this: IF A:A = Jan-05, then count B:B and C:C of the same row where A:A is the column for Screen B:B is the column for Eligible C:C is the column for Enrolled Once I have the table I should be able to do the graph. I'm sure though I&...

Creating Bubble Charts
How do you create a bubble chart that displays the legend as a description of the 3rd variable (the bubble size)? For example, how would you create the following bubble chart where the x-axis is column B, y-axis is column C, bubble size is column D and legend is column A? A1: B1:Gross Rev C1:Net Income D1:# of Plants A2:East B2:830,000 C2:35,000 D2:26 A3:West B3:620,000 C3:54,000 D3:13 A4:North B4:150,000 C4:80,000 D4:40 A5:South B5:41,000 C5:15,000 D5:35 Any insight will be helpful! Hi Tina I would create a bubble chart and use one of the following tools for labelling the...

Access 2007 graph/chart
I have created an access graph in Microsoft Office XP Professional using a union query. I have now moved the database into Access 2007. When I look at the graph in design view everything is fine. When I switch to form view, the window around the graph is present but no graph. If I click on the window, the graph appears as seen in design view (correct). How can I make the graph load properly? Is this a focus issue? Would appreciate any help. Thanks. Arnold ...

bubble chart
Hi ... Have about 10 bubbles there. Need to format data labels of ALL 10 bubbles at the same time. (I wanna select Alignment > label position > center, that means moving all 10 data labels to the center of the bubbles) Any method that i can do it in one go, without the need to select and format it one by one ? Using Excel 2002. Thanks. Hi, If the bubbles are all different series then no. You can reduce the time by using F4 which will repeat the command. So format one data label and then select another and press F4. Cheers Andy dstdst123@excite.com wrote: > Hi ... > >...

How do I keep chart/data link when copying worksheet
I have a worksheet for each month within a file - each worksheet has data tables, and some charts generated from that data. With Excel 2003, when I copied a worksheet within the same file (for example, to create the July sheet, by copying the June sheet) the charts in the new (copied) July sheet correctly linked to the July data. When I do the same in Excel 2007, the charts in the July sheet link back to the June data instead. I have looked through the options and cannot see how to do this. Is the data on the same sheet as the chart? In 2003, if the chart was on a different worksheet, ...

Using Defined Names in Charts
I am using Office 2002. I am trying to create a chart that has only one Series called Data. The values for DATA is a name called SCROLL_DATA. The name has been defined using the Insert-Name-Define action. The workbook is called Quality Escapes.xls I have entered the following in the values box as I have seen many instruction as follows =Quality Escapes.xls!SCROLL_DATA What am I doing wrong. -- bobburg ------------------------------------------------------------------------ bobburg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=22006 View this thread: http:/...

Scroll Bar Delay
I want to use a scroll bar to control which 18 months of data are viewable. Right now I have an ActiveX scroll bar linked to a cell with the minimum being 11 since the earliest data I want is 11/1/07. In the cell below that I have the formula =DATE(2007,AB3,1), which gives me the starting reference date. Back in Column C I put the formula =\$AB\$4 in cell C7 then below that (and each subsequent row) =DATE(YEAR(C7),MONTH(C7)+1,1) for 18 months. Then the 4 columns to the right of the date has the various information for that month populated through a pivot table on another sheet usin...

how can I attach a single access database form to an email
I want to attach a single database form to an email, im not sure if this is possible, Do you really mean a Form or a Report? Steve "tony" wrote: > I want to attach a single database form to an email, im not sure if this is > possible, Hi Steve, yes a report "SteveM" wrote: > Do you really mean a Form or a Report? > > Steve > > "tony" wrote: > > > I want to attach a single database form to an email, im not sure if this is > > possible, ...

Paste a chart as a link?
Can you paste a chart as a link within the same workbook? I have a large workbook, with a different worksheet for each location. Each of these worksheets has a large data range which I add to each month. Next to the data range I have a corresponding chart. This makes it easy to update the chart each month, by dragging the color-coded range for the entire data series. What I need to do is display all of the charts together in one worksheet. I’ve tried moving them all to a separate worksheet, but then it’s difficult to update them. (no more color ranges) Is there an easier way to ...

Live Charts in PPT 2007
Is it possible to do the following: 1. Create an Excel Spreadsheet in PPT 2. Create a chart from the data in that spreadsheet 3. Link the embedded spreadsheet to the chart so that data changed in the PPT reflects in the chart. 4. In presentation mode, keep the spreadsheet live to that I can change data and have it reflect in the chart without dropping out of presentation mode? I do a lot of sales presentations and I'm hoping to be able to update information on the fly without "going behind the curtain." Thank you. 1. Yes. 2. Yes. 3. Yes. 4. Don'...

Loss of pictures, lines and background shading
Pictures, lines and background shading are not always visible. When the cursor points to where those images are, a label is displayed under the cursor noting what is actually there. The picture's outer border is visible but no picture. When changing screen size, the pictures, lines and shading appear, but go away when scrolling. Must constantly adjust screen size to get the images back. Problem started after loading adobe and creating pdf files from publisher. Tried restoring Publisher but same result. -- Ira Ira T wrote: > Pictures, lines and background shading are not alw...

How do Bar chart where Bar colour changes with different data Valu
hi, I would like to develop a Bar chart, which changes its bar colour for different data values. e.g for a cell format and to change colour of a cell for different values we can use conditional format. Hi, Have a look an Jon's example of conditional charts. http://peltiertech.com/Excel/Charts/ConditionalChart1.html Cheers Andy Zeeshan wrote: > hi, > I would like to develop a Bar chart, which changes its bar colour for > different data values. e.g for a cell format and to change colour of a cell > for different values we can use conditional format. -- Andy Pope, Micro...

Timeline chart from text containing cells
I have seen great tips here for creating Excel timeline charts from numerical data. My question may be far afield but here it is: is there a way to create a timeline chart from a table organized as follows: 1) Years 2004 to 2009 as column headings, columns B-G 2) Product descriptions as row titles in column A 3) keywords (such as "RE", "EX", "FS") in the intersecting cells Can Excel convert these key words to distinctive symbols (such as a square bullet, round bullet, diamond shaped bullet) and create a timeline chart? So it's like a Gantt chart with...

How do I create new default color settings for charts in Excel?
I do not like the default color settings for my charts in Excel. I always end up modifying them extensively. I'd like to make as few changes as possible when my charts are created. Can I set new color defaults for charts created in Excel? Hi, You can set the colours for chart lines and fill. Menu Tools > Options. On the Color tab is a set of colours for Line and Fill. Cheers Andy pro user wrote: > I do not like the default color settings for my charts in Excel. I always end > up modifying them extensively. I'd like to make as few changes as possible > when my char...

Combining query and table
Well, I have a table with fields like Quantity Borrowed and Quantity Returned. I have a query with a field QuantityAvailable which is calculated based on some formula... So, problem is I want to build a subform consists of this table and query together yet user can enter themselves the QuantityReturned and next field is the QuantityAvailable showed... How? -- Message posted via http://www.accessmonster.com Hey! Thanks for the idea... But, I have created the query and appended it in my form. It can works but it cannot automatically update as I enter the value in other fields.Any solution f...

Line Spacing #2
How do I vary line spacing within a Cel? Bernie Beal. Hi Bernie, Varying line spacing within a cell can be done by hitting the Alt key followed by the Enter key within the cell to force a wrap around of text in that cell. To add more free lines, just keep hitting Alt- Enter. Hope that helps. ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ Kim Hartnet www.excelwell-excel-templates.110mb.com "The best place on the net for free & inexpensive Excel templates, macros and spreadsheets" ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~...

how to move lines in conjunction with graphs
Hi, I've drawn a dotted line across my scatter graph but whenever i move the graphs the dotted line stays in its original place. is there a way to make the line part of the graph so that it resizes and moves in conjunction with the graph? Thanks -- Shoque ------------------------------------------------------------------------ Shoque's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36320 View this thread: http://www.excelforum.com/showthread.php?threadid=564595 I assume you mean the line is derived from the Drawing tool bar. To have the chart serve as th...

chart hide number in Y axis
Hello I would like to do the following : in the axis of a graph : show the numbers from 1-100, hide the numbers 100-500 and show 500-600 I have been trying to format the axis but there is no option for this I think the only way might be to work wity a secondary y axis Any suggestions ? cheers Petra - Do you want to break the axis between 100 and 500? Try my example: http://peltiertech.com/Excel/Charts/BrokenYAxis.html or a similar example on Tushar Mehta's site (http://tushar-mehta.com). Do you want to keep the axis, complete with tick marks and the proportional spaci...