Line chart with three different types of data!?!

[Djflare]

Hey everyone,

I'm trying to make an 2d line chart on excel 2010. I have three columns worth of data. One column has a date (mm/dd/yyyy). The second column is time (hh:mm:ss) and the third column has a reason code (text). So I want to make a line chart that tracks the "time" over the "dates". So the dates would be the x axis and the time the y axis. The trick come in with the reason code, the reason it's a line chart is I want different line for different "reason codes". I thought this would be simple, but I can't figure out the way excel has you chose your data source. If i highlight all three columns it gives me either a line chart of time vs date or reason code vs date or gives me an error of "Some chart types cannot be combined with other chart types. Please select a different chart type.

Any help is appreciated, thanks!!!

[JosephP]

a sample workbook would assist us I reckon. can't really picture what you are trying to show.

[Djflare]

Excel.xlsx

The columns I'm talkin about are U( date) AE( Time) and AF(Reason code). The x axis should be the date, they y axis time and the legend reason codes.

[JosephP]

that's not really helpful as there's no data or indication of what the chart should look like. are you trying to aggregate the total time per day for each reason? if so, a pivot chart should work but you'll need to fill in the blank header cell for the reason code column.

[MrShorty]

There isn't enough information in the spreadsheet to put together a complete answer (or maybe it's because I'm using google docs to view it).

In any case, the easiest approach to graphing multiple series like that is to create a table that would look something like this (comma delimits cells):

Please Login or Register  to view this content.

I'm not sure what the best way is to extract a table like that from a single column like you have. A table like this will make creating the chart easy.

[Djflare]

I wanted a line chart, that would indeed indicate the total time per day of each reason code. However, if that can't work, whatever chart can show that is fine. Any idea how i could achieve the line chart?

---------- Post added at 08:57 AM ---------- Previous post was at 08:55 AM ----------

Mr. Shorty, because of the large number of possible reason codes (19) formatting my excel sheet like that would be ...undesirable.

---------- Post added at 08:59 AM ---------- Previous post was at 08:57 AM ----------

I don't know if this helps but I have the totals for each reason code in BM

[JosephP]

a pivot chart like this?

[Djflare]

That would be great! However, did you make it using the format i had? It looks like you used the format MrShorty was recommending.

[JosephP]

it's built directly off the first sheet-the pivot table does the work for you and the chart then runs from the pivot table.

[Djflare]

Great! haha ummI'm having trouble finding pivot chart option now though. Can you walk me through it? I highlight the three columns(does order matter?) then what?

[JosephP]

Insert tab then click arrow beneath the Pivot Table option and choose pivot chart, select the data as all columns from the day column to the reason code column. then you want the reason codes as legend entries, day as axis field and time as value field.

[Djflare]

Oh man, you guys are awesome! I'm mark the thread as solved. Quick question though, does the pivot chart update itself when new data is added in? or is there a way like the other graphs and charts on the worksheet i showed you where it stays blank until needed?

[JosephP]

you can create a dynamic named range for the source of the pivot table: http://www.contextures.com/xlPivot01.html
then all you need to do is refresh the pivot.