Simplify Pivottable and graphing process in Excel 2010

[zeek87]

I have created a pivottable regarding gas well production set up as follows:
The Row Labels:
Column A: Well number
Column B: State
Column C: County
Column D: Date well started

Column Label:
Production Date (It is monthly and is labeled as the last day of each month)

Values:
Average Water Production (for each month)
Average Gas Production (for each month)

I need to graph the average decline of the wells by state and then by county. The Water and Gas production needs to start at the same time instead of a date. They need to be average 1st, 2nd, 3rd and so on months instead of being listed as individual dated months.

I have used a filter to get the state and county data I needed seperated. I copy and pasted the data and deleted all the empty cells so the production started at month 1. I then graphed the results.

This method is time consuming and the data and graphs will need updated a couple of times a year.

There is no field I can pull instead of production date in the pivottable that would allow all of the production to start at an initial time. I have also thought of using vlookup in the cells of the tables I have already made to get the new production data but any new wells will need to be copied and pasted to the various tables.

Any recommendations for making the process of refreshing the data and graphs simpler?
Thanks in advance.

[zeek87]

Bump to top