Excel 2013 Chart – Multiple Series on Pivot Chart

For a PivotTable to work as expected, you must arrange your data like a database: a row is a record, and a column is a field providing information on that record.

So first off, having several columns containing multiple values for a specific date appears wrong by my understanding of the situation. That translates to having many records (values) crammed into one record. By definition, having several columns for different categories prevent you from using this data meaningfully in a PivotTable.

If you can modify your source data on a permanent basis, here's a suggested layout:

 | Date       | Category | Value
 | 01/01/2011 | 1        | x
 | 01/01/2011 | 2        | y
 | 01/01/2011 | 25       | z
 | 01/02/2011 | 1        | x

To which you can append the TYPE by using a VLOOKUP formula that fetches the proper type for your category to avoid duplication and automatically reflect any change in your TYPE table. (Note that this assumes inverted columns in your type table: CATEGORY | TYPE for VLOOKUP to work)

 | Date       | Category | Type                           | Value
 | 01/01/2011 | 1        | VLOOKUP(B2, 'TypeTable', 2, 0) | x
 | 01/01/2011 | 2        | VLOOKUP(C2, 'TypeTable', 2, 0) | y
 | 01/01/2011 | 25       | VLOOKUP(D2, 'TypeTable', 2, 0) | z
 | 01/02/2011 | 1        | VLOOKUP(E2, 'TypeTable', 2, 0) | x

Such a table would contain lots of rows but feeding this to a PivotTable will allow you to manipulate it in every way: by date, by category, by type.

In fact, your source data would be a good RESULT of a PivotTable summarizing values by date with a column label on category! And God knows it can be hard to take a PivotTable result and translate it back to data that can then be used to make another PivotTable. Solutions including manual labor, macros, or a bunch of intermediate formulas.

Hope that helps.

I presume you know that Excel can extract the month and year from a date:

To answer your question, you may want to keep the Year column as a source for data series names and/or values.  As far as I can tell, you don’t need the Month column.  However, you may need to insert enough blank rows before your data so it looks like the first year begins with January (see illustration below).  (Also, insert a blank row where any month is skipped.)

I’ve read that the best way to create a chart in Excel is to click in an empty cell and Insert Chart, and then build it up from nothing.  Excel’s defaults when it thinks it knows that data you want to plot are not always useful.  So insert your line chart, right-click in the chart, and click on Select Data….  Click “Add” once for each year:

I got High-Low-Average bars with a bit more work.  First, create these four new columns:

Then insert a chart with the AVERAGE value (G) as Y and the Year (F) as X:

Then,

1. Select the data series (click on the line in the chart).
2. In the Layout tab, Analysis Group, click on Error Bars -> More Error Bar Options.
3. Select Custom and click on “Specify Values”.
4. Enter the H (MAX) value for the “Positive Error Value” and I (MIN) for the “Negative Error Value”:

(Disclosure: As in the TV commercials, the above image was manipulated.)  So, is this:

what you wanted?

Oops: I just noticed that I may have misread part of the question: you want a High-Low-Average bar for each month (even though you didn't indicate that you have data to support that).  I hope the above is close enough to what you want that you can adapt it.