Excel Question - Trying to generate a graph using two 3 Y value series while have 6 X values.

Occasional Visitor

If anyone could help me out I would greatly appreciate it,


I am trying to generate a bar graph where I have two treatments (i.e. T1 and T2). T1 and T2 were applied to 3 distinct plots. I therefore have 6 different plots. T1 applies to plots 2,4, and 5 while T2 applies to plots 1,3, and 6. Within the excel sheet I have one column named Plot with plot numbers listed 2,1,4,3,5,6 listed in the rows below. In the next column I have the data points linked to their respective plot number.


If I generate a column graph with 2 series:


1) 3 data points under a legend entries (series) named T1. The horizontal (category) axis labels have the corresponding plot numbers.


2) same thing but for T2


Once I do that there are only 3 horizontal values. Usually the last ones I selected in the horizontal (category) axis labels prompt. Now I have a clear distinction between T1 and T2 but not the appropriate plot number.


If I selected all of the plot numbers in the horizontal (category) axis labels prompt nothing changes unless I expand the legend entries (series) to all data points. However, then columns all have the same colour and the distinction is lost.


The best I could come up with is to have 3 rows. First being for plots, second for T1, and third for T3. I leave a blank where there is no data point for the specific plot (i.e T2 has no value in plot number 4). There I can generate a plot that has all 6 points and a distinct colour per treatment. However, there is an awkward gap since excel does not ignore the missing values.


Can any give me any advice regarding my issue. I've attached an excel file with the examples I mentioned above. 


Thank you