Graphing survey data

Hello,

I hope this is in the correct place, since it is a newbie/general question regarding charts and tables.

I'm conducting a survey of insects that visit particular specimens in a garden. I'm not able to keep track of each individual insect, so I would like to graph the data I have into a compound bar chart that shows every instance a species is present on a list as a value of '1' on the Y-Axis and have them associate to a plant specimen in the X-Axis.

I have two columns 'Location' and 'Species'. As the rows extended down each plant specimen is listed repeatedly together with every new insect species identified in a set of photographs until it changes location and there is a blank cell, and then continues this way with the next plant specimen. I'd like to sift through the list of insect species entries so that it recognizes one instance of species name being present for all the rows associated with a given 'Location' value.

How can I create this graph without creating a second table where I have to manually list every specimen? When I try to create a compound bar chart it says something like 'two data sets necessary'.

Thanks

Welcome to the forum.

I think you would have to manipulate your data into a second summary table, and to use that to draw your charts. Other than creating a list of the plants in a column and a list of the species across a row, the rest of the table could be produced automatically by formulae using perhaps COUNTIFS, or a pivot table.

If you attach a sample Excel workbook by following the details in the yellow banner at the top of the screen, then we might be able to suggest the way forward more specifically.

Hope this helps.

Pete