Using data to create a graph organising frequency by month.

Hi there, I'm pretty new to using excel in depth and was wondering if anyone could help me with creating a frequency graph that might be produced through formulas/macro on an existing data sheet.

It's a complaints log, so contains the date of complaint (Cell B2 downwards, in a "custom" date format that includes hours and minutes) and country (Cell L2 downwards), so I was hoping to organise it so I could see how many complaints took place in a given month, and if possible separate the results by country (to produce multiple lines on the same axes). Does anybody know of a way to do this with functions or something more complex? The simpler the better, as I can't work Visual Basic at all.

Thanks for any help!!

Create a pivottable from your data, have the date as a row field, and a count of country as the data, and country as column field. Group the date by month and year ... create a graph, and that's it.

Thank you! Pivot Tables are great and I never knew about them! But I just have a couple issues.

To get it to organise the table by month I had to use this formula to create another of column of data in my original table

=MONTH(B13)&"/"&YEAR(B13)

so it would just organise it by month/year (as the custom format in the table I originally received also includes, day, hour and minutes). Is that the best way to do this or is there some clever function of Pivot tables that would do it automatically but I can't find?

Also, I think there are some problems because before creating my pivot table I filtered the data by another criteria (under "Nature" column I have selected "Product Complaint"), should this create problems and how can I get around it (so I just create a pivot table for "Product Complaint" data.

Finally, in my pivot table although the month/year format runs down the rows on the left hand side of the table they are not in the correct order and I can't seem to fix them. Is there a way to correct this?

Thanks for all your help!!

Just to say that I figured out how to organise it so that I can filter in the actual pivot chart by "Nature" (by dragging that column title into the "Page" section of the pivot table layout), I just hope it works ok when creating a graph from the data!

If you put your date/time field in the row area of the pivottable, and you right-click that area, and choose Group - does it allow you to group by month ( and year )?

Unfortunately not, I right-click "Enquiry Date" > "Group and Show Detail" > "Group..." and it gives the error message "Cannot Group that selection".

Using the other method to create a separate "month/year" column I still can't get it to display the rows in chronological order either. It has me totally stumped.