Can I pivot this worksheet

Hi, the worksheet below is fictional. I created it to show the structure of the table I am trying to analyse.

I am a total newbie to pivot tables & charts. I have read a lot, but have no practical experience. What I am trying to do is not as simple as the examples I have seen.

The fictional sheet covers 3 months daily sightings of birds from 3 regions of the UK. There are several blank elements & rows. (The blank elements actually contain zero values, but have been formatted to show blank.)

My 1st question is ....... Can I pivot this worksheet to show a stacked bar chart with months along the x-axis, and vertical stacked bars showing the total counts for each species, for the UK as a whole (apologies to Ireland), or do I need to simplify the original sheet?

Hope this is clear, I repeat I am a total newbie. KK

This looks good, please give me awhile to study it and explore.

Mega thanks. KK

I am OK to restructure the original sheet, I'm OK with excel, just totally ignorant with pivot tables. KK

etaf thanks, I have seen some, as well as a book I bought, and downloads. I will study your suggestions and get back to you all in a day or so. I always suspected that the source data was not 'flat' enough. please allow me time to investigate, I'm not as young as I used to be

Originally Posted by etaf

i have restructerd into a format that can be pivottabled
see attached - still needs a little cleanup
BUT

we now need to see the results you need



should be able to - i'll try

Etaf thanks. I'll take this step by step please, I am not as quick as you. The restructured table you sent is clearly simpler than my original (more like a database I suspect) and I can see it is much easier to pivot.

How did you create the restructured table? manually ? or did you use the tools suggested by Marvin?

This is very helpful, mega thanks. KK

Originally Posted by etaf

heres a pivot on the restructured data

This is what I am looking for, except I need a stacked bar chart rather than separate bars for each bird. Otherwise it's spot - on. KK

It might be worthwhile to mention that my actual table covers a total of 2.5 years, so restructuring the table must be automated I think?

Thanks all, I think I understand. I'll mark this as solved and apply it to my real data. K

Originally Posted by etaf

i copied the columns manually
if you have 2.5 years in the format , wont take long to restructure
as you can copy the column with all the dates, and then auto fill

I did not use the tools

stacked attached

Etaf, just for info I have now reproduced your pivot table and chart. Mega-thanks once again. K