Clustered Column that organizes by data label

[skynuma]

Hi there!


I am trying to create a clustered column chart that compares multiple clam species population densities at a single beach site over time. Ultimately, I want to compare the densities of the same species clams at different times at the same place.


On my x-axis, I want to have the clam species present; on my y-axis, density. My problem is that when I select the data from different groups that I want to compare, I have different clam species present - ie. my x-axis labels don't necessarily line up.


How do I make a chart that has all possible x-axis labels on the x-axis (all possible clam species in the study), and have the data line up according to the species? I am using a pivot table to organize much of this data.


I'm also using Excel 2011.


Any assistance is much appreciated!


Thank you.

 

[cyrilbrd]

Hi,
How is your raw data?
Similar to this?

Excel 2016 (Windows) 64 bit
	A	B	C	D
1	date	Site	Species	count
2	11/01/2016	B1	Ruditapes decussatus	43
3	11/01/2016	B1	Mercenaria mercenaria	181
4	11/01/2016	B2	Venerupis philippinarum	104
5	11/01/2016	B3	Mya arenaria	178
6	11/01/2016	B2	Spisula solidissima	122
7	12/01/2016	B1	Ruditapes decussatus	83
8	12/01/2016	B1	Mercenaria mercenaria	124
9	12/01/2016	B3	Venerupis philippinarum	180
10	12/01/2016	B2	Mya arenaria	34
11	12/01/2016	B1	Spisula solidissima	129
12	12/01/2016	B3	Arctica islandica	171
13	12/01/2016	B3	Siliqua patula	197
14	12/01/2016	B2	Tivela stultorum	168
15	12/01/2016	B3	Panopea abrupta	33
16	12/01/2016	B2	Ensis directus	111
17	12/01/2016	B1	Panope generosa	148
Sheet8