Difficulty Aligning Series Data With Proper Dates

I ‘m building a column graph using Excel 2007 and I’m having difficulty 
aligning a series of data with the proper dates.  I have 2 series of data – 
Series 1:  Includes a value for each quarter in 2009 (e.g., Mar-09, Jun-09, 
etc).  Series 2: Includes a value for each quarter in 2010 (e.g., Mar-10, 
Jun-10, etc).  After creating each series, I select the appropriate dates 
(Series 1 – 2009 dates / Series 2 – 2010 dates).  The first issue that I’m 
having is that when I select the second set of dates (for Series 2), it 
overrides the first – so the x-axis initially shows 2009 dates, but then 
changes to 2010.  To get around this, I just reset the dates using the Format 
Axis option – resulting in all the 2009 and 2010 dates appearing on the 
x-axis..  Unfortunately, when I reset the dates in this manner, the 2009 
columns of data are moved over to the 2010 dates – resulting in the Mar-09 
column positioned next to the Mar-10 column.  And the same applies to the 
other dates as well – Jun 09 is positioned next to Jun-10.  What I want is 
the 4 quarterly columns for 2009 to be aligned with the 2009 dates and the 4 
quarterly columns for 2010 to be aligned with the 2010 dates.  

Any assistance you can provide would be greatly appreciated.

Mike M.

0
Utf
9/29/2009 3:09:03 PM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
1376 Views

Similar Articles

[PageSpeed] 42

You can only use one set of dates. But it's possible to fake out Excel:

Plot Two Time Series With Different Dates
http://peltiertech.com/WordPress/plot-two-time-series-with-different-dates/

Line charts and column charts work the same way.

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/



Mike M. wrote:
> I ‘m building a column graph using Excel 2007 and I’m having difficulty 
> aligning a series of data with the proper dates.  I have 2 series of data – 
> Series 1:  Includes a value for each quarter in 2009 (e.g., Mar-09, Jun-09, 
> etc).  Series 2: Includes a value for each quarter in 2010 (e.g., Mar-10, 
> Jun-10, etc).  After creating each series, I select the appropriate dates 
> (Series 1 – 2009 dates / Series 2 – 2010 dates).  The first issue that I’m 
> having is that when I select the second set of dates (for Series 2), it 
> overrides the first – so the x-axis initially shows 2009 dates, but then 
> changes to 2010.  To get around this, I just reset the dates using the Format 
> Axis option – resulting in all the 2009 and 2010 dates appearing on the 
> x-axis..  Unfortunately, when I reset the dates in this manner, the 2009 
> columns of data are moved over to the 2010 dates – resulting in the Mar-09 
> column positioned next to the Mar-10 column.  And the same applies to the 
> other dates as well – Jun 09 is positioned next to Jun-10.  What I want is 
> the 4 quarterly columns for 2009 to be aligned with the 2009 dates and the 4 
> quarterly columns for 2010 to be aligned with the 2010 dates.  
> 
> Any assistance you can provide would be greatly appreciated.
> 
> Mike M.
> 
0
jonNO1 (306)
9/29/2009 6:04:43 PM
Jon,

Thanks for your response.  I read thru the suggestion and I will definitely 
try applying it to my graph.

BTW, the way I got around this limitation was similar to your suggestion - 
by adding a secondary axis.  I created a column chart for both series, then 
converted the second series (2010 data) to a line chart.  I then added a 
secondary axis that aligned with the values of the second series.  Its not as 
'clean' as your suggestion, but with the legend clearly indicating the line 
graph represents 2010 data, the reader should be able to comprehend what 
they're seeing in the graph.

Thanks again for your help.

Mike M.

"Jon Peltier" wrote:

> You can only use one set of dates. But it's possible to fake out Excel:
> 
> Plot Two Time Series With Different Dates
> http://peltiertech.com/WordPress/plot-two-time-series-with-different-dates/
> 
> Line charts and column charts work the same way.
> 
> - Jon
> -------
> Jon Peltier
> Peltier Technical Services, Inc.
> http://peltiertech.com/
> 
> 
> 
> Mike M. wrote:
> > I ‘m building a column graph using Excel 2007 and I’m having difficulty 
> > aligning a series of data with the proper dates.  I have 2 series of data – 
> > Series 1:  Includes a value for each quarter in 2009 (e.g., Mar-09, Jun-09, 
> > etc).  Series 2: Includes a value for each quarter in 2010 (e.g., Mar-10, 
> > Jun-10, etc).  After creating each series, I select the appropriate dates 
> > (Series 1 – 2009 dates / Series 2 – 2010 dates).  The first issue that I’m 
> > having is that when I select the second set of dates (for Series 2), it 
> > overrides the first – so the x-axis initially shows 2009 dates, but then 
> > changes to 2010.  To get around this, I just reset the dates using the Format 
> > Axis option – resulting in all the 2009 and 2010 dates appearing on the 
> > x-axis..  Unfortunately, when I reset the dates in this manner, the 2009 
> > columns of data are moved over to the 2010 dates – resulting in the Mar-09 
> > column positioned next to the Mar-10 column.  And the same applies to the 
> > other dates as well – Jun 09 is positioned next to Jun-10.  What I want is 
> > the 4 quarterly columns for 2009 to be aligned with the 2009 dates and the 4 
> > quarterly columns for 2010 to be aligned with the 2010 dates.  
> > 
> > Any assistance you can provide would be greatly appreciated.
> > 
> > Mike M.
> > 
> 
0
MikeM (74)
9/29/2009 6:39:02 PM
Many ways to skin that cat. You could also convert the chart to an XY 
chart. If you want the nice date formatting of the X axis, you can keep 
the first series as a line chart series and convert any others to XY. (I 
know you talk of column charts, but that doesn't seem appropriate for a 
time series.)

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/



Mike M. wrote:
> Jon,
> 
> Thanks for your response.  I read thru the suggestion and I will definitely 
> try applying it to my graph.
> 
> BTW, the way I got around this limitation was similar to your suggestion - 
> by adding a secondary axis.  I created a column chart for both series, then 
> converted the second series (2010 data) to a line chart.  I then added a 
> secondary axis that aligned with the values of the second series.  Its not as 
> 'clean' as your suggestion, but with the legend clearly indicating the line 
> graph represents 2010 data, the reader should be able to comprehend what 
> they're seeing in the graph.
> 
> Thanks again for your help.
> 
> Mike M.
> 
> "Jon Peltier" wrote:
> 
>> You can only use one set of dates. But it's possible to fake out Excel:
>>
>> Plot Two Time Series With Different Dates
>> http://peltiertech.com/WordPress/plot-two-time-series-with-different-dates/
>>
>> Line charts and column charts work the same way.
>>
>> - Jon
>> -------
>> Jon Peltier
>> Peltier Technical Services, Inc.
>> http://peltiertech.com/
>>
>>
>>
>> Mike M. wrote:
>>> I ‘m building a column graph using Excel 2007 and I’m having difficulty 
>>> aligning a series of data with the proper dates.  I have 2 series of data – 
>>> Series 1:  Includes a value for each quarter in 2009 (e.g., Mar-09, Jun-09, 
>>> etc).  Series 2: Includes a value for each quarter in 2010 (e.g., Mar-10, 
>>> Jun-10, etc).  After creating each series, I select the appropriate dates 
>>> (Series 1 – 2009 dates / Series 2 – 2010 dates).  The first issue that I’m 
>>> having is that when I select the second set of dates (for Series 2), it 
>>> overrides the first – so the x-axis initially shows 2009 dates, but then 
>>> changes to 2010.  To get around this, I just reset the dates using the Format 
>>> Axis option – resulting in all the 2009 and 2010 dates appearing on the 
>>> x-axis..  Unfortunately, when I reset the dates in this manner, the 2009 
>>> columns of data are moved over to the 2010 dates – resulting in the Mar-09 
>>> column positioned next to the Mar-10 column.  And the same applies to the 
>>> other dates as well – Jun 09 is positioned next to Jun-10.  What I want is 
>>> the 4 quarterly columns for 2009 to be aligned with the 2009 dates and the 4 
>>> quarterly columns for 2010 to be aligned with the 2010 dates.  
>>>
>>> Any assistance you can provide would be greatly appreciated.
>>>
>>> Mike M.
>>>
0
jonNO1 (306)
9/30/2009 2:49:52 AM
Reply:

Similar Artilces:

Date of entry
Dear Discussion Group My supervisor wants to keep track of how much information I enter into a spreadsheet everyday. Is there a function so that whenever a type data into "A1", the date I did this would appear in "A2." Thank you, Mark Joseph see http://www.mcgimpsey.com/excel/timestamp.html In article <678586B8-9972-4D3B-8017-6B249194C6E7@microsoft.com>, Mark <Mark@discussions.microsoft.com> wrote: > Dear Discussion Group > My supervisor wants to keep track of how much information I enter into a > spreadsheet everyday. > Is t...

Data validation master cell
Is it possible to change what list is in a cell by selecting the name o that list from a "master" cell? For instance, say the master cell is A1, and it's validation lis contains the list "cats, dogs, horses". The list in B1 would be based on what list is selected in A1. Fo instance, if A1 was set to "cats", then the choices in B1 would b something like, "Calico, Tomcat, housecat, Siamese". What I really want to do though, is only have one "master" cell an have dependent cells that change depending on the master. So, if A1 is the master,...

Changing a charts series order
In eariler version of excel I was able to rearrange the series order of my charts. This allowed me to view data that existed under multiple data series. How do I change the series order? Additionally, how do I change the order of the series included in the legend? -- Csmith Hi, You can change the series order via the select data dialog. Select chart, Chart Tools > Design > Data > Select Data The order of the items in the legend will change automatically. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Csmith" <Csmith@discussions.micros...

HELP- Copy a Data Series to Other Graphs?
Hi- I'm using Excel 2002 and I've got a lot of XY (Scatter) graphs. When I add a new data series to one graph, it is defined by 3 values (Name, X Value and Y Value) If I want to add this data series to 7 or 8 add'l graphs, I know that I can copy/paste these 3 values to each of them. Is there a simpler way to accomplish this? thnx ...

Flip Difficulty
I am running Visio 2003 SP1. I have drawn a home floor plan. I select the entire floorplan, and try to "flip" it (for example, I want the garage on the right instead of the left, so I mirror, or flip, the entire plan). I get an error message and then the drawing is mis-configured with lines going in odd directions, like it flipped some objects but not others. Here are two of the error messages 01. "An error (318) occurred during the action Flip Horizontal. Referenced cell Wall.629!Connections.X4 does not exist". 02. "An error (318) occurred during the action F...

Access 2007 date function not working
I had a database in Access 2003 that I imported into 2007 and all works well except the date function in Reports. I have tried to go into reports and repair by adding a date field, but when I try to open the report and error box comes up asks for update parameter value for the date. The currrent date will not display in my reports, just (#name?) is in the date field. You would think trying to put the current date in a report would be easy. -- Thanks, Mark Use this --- =Date() -- KARL DEWEY Build a little - Test a little "Mark K" wrote: > I had a database in Access ...

Sort data to other worksheets
Looking for a way to have a master worksheet that has approximately 10 colums by 400 rows. We constantly add rows to the sheet and then need to view it sorted in various ways. Is there a way to have the data from the master sheet displayed on other tabs in various sorts without copying the data to the other worksheets every time data is added. Maybe you can just apply Data|Filter|Autofilter and hide/show the data you want to see. Ben Bazian wrote: > > Looking for a way to have a master worksheet that has approximately 10 > colums by 400 rows. We constantly add rows to the...

Adding a Formula as a data variable in a pivot
Is it possible to create a formula as data column in a pivot table? For eamaple let's say I have the following Table: Name Billed Charges Paid Charges George 1000 500 Pilar 2000 750 Fred 1211 602 I would like to add a data variable of [Billed Charges] - [Paid Charges] and another [Billed Charges] / [Paid Charges] -1 I know that one way to do this is to add columns to the raw data and refresh the pivot, but I am wondering whether I can create a calculated formula as...

Parsing data
I have looked at all the posts I could find but still have questions about this. I have a need to pull zipcode out of a 30 byte field. It could start in the 10 position or the 25th position. It is a length of five, except if it is an international address, then who knows! Let's just stick with zipcode for now. How do I go to the 30th position (the last blank) and keep going left until I get a 5 digit zip? Ideas? Something like this would extract a 5 character zip code that starts at the 25th position. Zipcode25: Mid([TheString],25, 5) However you mentioned a spa...

how to import data from outlook express for mac
Hello, I'm changing my net from macintosh to windows xp, and I was wondering how to import all the data (messages, contacts, etc) from outlook express for macintosh to outlook express or outlook 2002 on my windows xp I have the outlook express macintosh files, but I can't import any data. Maybe I have to do another step before... Any idea? Thank you Xavi ...

Input dates into excell with out typing the blackslash eg 030505
I want to input dates without typing backslash eg 030505 to represent 03/05/2005? can that be done? if so how? any info would be appreciated... Only through VBA http://www.cpearson.com/excel/DateTimeEntry.htm -- Regards, Peo Sjoblom "Bill" <Bill@discussions.microsoft.com> wrote in message news:986692F0-8636-4A2C-B300-9ACE76E0B596@microsoft.com... > I want to input dates without typing backslash eg 030505 to represent > 03/05/2005? can that be done? if so how? any info would be appreciated... If you want to use a helper column, you can enter your text &quo...

Contract line invoice date changing when line is cancelled
Is there a way to keep the contract line invoice date from changing to the cancelled "as of" date when cancelling a contract line? We bill in advance and would like the invoice date to remain as is. Example: inv dt: 12/1/06 coverage dates: 12/1/06 - 12/31/06 After cancelling the line as of 12/14/06: inv dt: 12/14/06 coverage dates: 12/1/06 - 12/14/06 ...

Worksheet alignment is out on pc
I tried opening different workbooks on my PC and the alignment all seemed to 'overflow' from the A4 size setting. I had opened the same workbooks on a few other PCs running Excel 2003 but they are all ok. The resoultions and other settings seemed to be the same. I had to change the setup scaling size in print preview if I were to get the size I wanted. Is there any settings or options which needed to be changed in either Excel or my PC to rectify this problem? Hope anyone who knows the solution can help. THank you! Hi see your other post -- Regards Frank Kabel Frankfurt, Germany ...

Unable to update free/busy data
A user received the following error message when using the calendar feature: "unable to update free/busy data. Operation failed." According to microsoft knowledge base there is a fix they have been testing. Does anyone know if this fix costs anything or if there is another workaround this error message." ...

Count Data in Series
Hi, I'm trying to write a formula that will go down a column and count the number of one value cells that occur between those of another value. For example, given: P S S P S S S S P P S P in column A, I'd like a formula in column B that gives: P S S P 2 S S S S P 4 P 0 S P 1 I want the columns opposite the "S" to remain empty. I've been playing with the =FREQUENCY function, but haven't been able to figure it out. Thanks, Demosthenes wrote: > Hi, > > I'm trying to write a formula that will...

How can I plot a data series from multiple worksheets #2
...

strange bug -> data not stored in SQL Server but it is in MSCRM app!!!
Hi all, I have a weird problem yet don't know whether it is a universal MSCRM bug or just my development environment. I have a few custom picklists in the opporunity form ie. Status which has 3 values, x,y & z. The default value given is x. It is a business required field When i create an opportunity from the form, the data is saved without any problem. When i go into Sql Server > MSCRM Database and list the values of the Status fields, it contains the right value that was entered. When I convert a lead into an opportunity, the picklist shows the default of x and it can be saved...

.PST Difficulty
If you could follow the dialogue and help me out in anyway....I am a littl concerned as this is my only copy... Dialogue: Question I am getting this error when I attempt to copy the .pst file I have burned to a cd rom to my locasl hard disk: Cannot Copy Sept22: Invalid MS DOS Function I am a little concerned since since the machine I have taken this information from and burned to cd is no lonbger available...Any ideas? Response: How are you trying to copy the file? My Response I am just selecting it from the cd rom..and dragging it to a folder...As well I have tried Copy and Past...

Changing cell alignment defaults.
Is there a way to change cell alignment defaults in Excel 2002? I get frustrated because the default alignment is at the "general" and "bottom" of the cell and I always prefer "top" and "left". Any ideas? Save styles to use in new workbooks Open a new blank workbook. Open the workbook that contains the styles you want to save. Copy the styles from your workbook to the blank workbook. How? Open the workbook that contains the styles you want to copy. Open the workbook you want to copy the styles to, and then click Style on the Format menu. C...

Data out of Range
Hi There, I don't know why i am getting this error message while adding a new user to the CRM. and what should i do in this scenario. "The maximum character limit has been exceeded for at least one field". Thanks, bas. ...

Data series not showing
I want to create a simple clustered column chart, which is not working (although another one is). It only shows two of the three series of data in two columns. Here are the data: 2005 10,573,416 2006 11,735,926 2007 12,645,042 The chart that is working correctly contains monthly data for which the above data are totals. Can anyone help? -- susan ...

archiving data #2
does money support archiving old data? I don't want to have to start a new account, but would like to remove a bunch of the data from my current money file. -- Thanks Wayne Sepega Jacksonville, Fl Enterprise Library Configuration Console Module Generator http://workspaces.gotdotnet.com/elccmg "When a man sits with a pretty girl for an hour, it seems like a minute. But let him sit on a hot stove for a minute and it's longer than any hour. That's relativity." - Albert Einstein Yes it does but BEWARE! See http://umpmfaq.info/faq.html#Q95. There seems to be more pain ...

Fill in Column with a series of repeating numbers
I am trying to fill in an amortization years column. The first 12 rows are "1", the next 12 rows are "2", the next twelve rows are "3" and so on. I've got 360 rows to fill. Any suggestions? A1: 1 A2: =IF(MOD(ROW(),12)=1,A1+1,A1) Fill down -- Jim Cone Portland, Oregon USA ( Review of Special Sort add-in: http://www.contextures.com/excel-sort-addin.html ) "mimi" <mimi@discussions.microsoft.com> wrote in message I am trying to fill in an amortization years column. The first 12 rows are "1", the next 12 ro...

printing filtered data
My worksheet's size varies from 12 to 100 rows. To identify rows with data, I have a hidden column "A" which displays a character if the row has any data entered. I have entered a "fixed" character in cell A100. To print, I used a macro to unhide column "A", activate Autofilter, show rows with a character in the hidden column (row 100 always appears due to the "fixed character), and print the displayed rows. My problem is that rows without a character in column "A" are being printed. Usually, my worksheet should print on 1 page. I cannot ...

Date format in Receipts
Hello: My boss wants to add the Due Date on each receipt that uses Account as tender. I created the variable on XML code using: <SET name="DueDate" type="vbDate"> Transaction.Date + 20 </SET> And print it with: <IF> <CONDITION> (Tender.Descriptor.TenderType = tenderAccount)</CONDITION> <THEN> <ROW> "Due Date|" DueDate </ROW> </THEN> </IF> This works, but the DueDate is printed as a number. How can I change its format to MM/DD/YYYY. I tried using all VB Date function, but no one worked. Please, hel...