General Pivot Table Question

[geospatial]

Is a pivot table the best way to go when trying to pull how many of each event by month there is? The problem I seem to be having is that if a new catergory name pops up in the column I am using it auto adds it to the pivot table and I do not like that.

Also it seems like to get the data I want I end up having around 50 pivot tables which I think adds alot to the size of the excel workbook. I was thinking if there was a way to do a formula or to a macro that pulls the data for me and places the number in a field it would work more smoothly.

For instance column B has different categories of crimes such as murder, kidnapping, etc. For every instance there is a new row recorded with the DTG in column A, and category in column B.

What my pivot tables do is break it out by month, day, week, day of week, and hour of day.

Then I have another table that makes all this more readable than looking into the pivot tables, but i pull all the information using a getpivotdata formula.

Just trying to make things work easier rather than harder.

 

[JPM]

If you are having questions about how to optimize the performance of your pivot table, Microsoft, has some good help here: http://support.microsoft.com/kb/104308