MS-Excel: How to produce a chart from data organized this way?

Question

I have an MS-Excel chart, the data looks like this:

I want to produce a line-chart from this data.

The Y-axis (horizontal) will be the date range. The dates will span much more time than is shown in this little snip - it will be several years' worth of data.

On the X-axis, I would like to chart subsets of the rows, one for each person (Matt, Jim, Bob, etc). The result is like this:

In fact that is the actual chart I want. I'm able to produce this chart, obviously, but the way I'm doing it is fairly labor- and click-intensive. Here's how I do it: I use the data filter "tool" in Excel to filter the source data table on "person", then cut/paste that filtered data into a new sheet. I do that once for each person (4 or 5 people currently). Then I create a chart, and add the 4 or 5 ranges from all those new sheets into the chart. Ba-da-boom.

But every time I add new rows to the raw source data, I have to go through the cut/paste again, 4 or 5 times, then fiddle with the data range settings on the chart (expanding them all by one cell).

How can I update the chart more simply? Ideally I just want the chart to magically update when I add a new set of rows to the raw source data.

Is there a way for me to automate the data extraction? Is there a way to use named data ranges to make it simpler? Should I de-normalize the data or something?

I have no problem writing VBA if that is what it takes, but I'd like to minimize the effort involved.

Thanks for any suggestions.

(using MS Excel 2010 "Starter Edition" - it came with the PC)

---

EDIT
From reading various webpages, apparently developer features have been disabled in Office Starter Edition. This includes automation as well as VBA-macros. So, I guess that option is out, for me. Anyone with further info, I'd appreciate it.

Answer 1

If you can re-arrange the layout of your data so that your rows are each person, and your columns are the dates like this:

          2/28/2011  3/15/2011  3/31/2011  4/15/2011
Jim T.    24%        35%        24%        29%
Bob                  24%        25%        26%
Matt G.              26%        27%        20%
Demko                                      20%

then charting that the normal way will produce what you're looking for.

Answer 2

First convert the data into a Table, by selecting it and typing Ctrl+L.

Then make a pivot table from this original Table. Use names as column headers, dates as row headers, values in the bulk of the table. Add new data at the bottom of the Table, and refresh the pivot table. Since you converted the data into a Table, the pivot table source data range will expand as the Table expands.