Charting Replicate Data and Associated Dates

**ovirgadamo** · 09-02-2009, 09:03 PM

Hi
I am new to this forum but I have read numerous threads and they have all been very informative, so I hope someone will be kind enough to help me. Attached is a spreadsheet containing replicate measurements taken on various days. The intent is for the replicate data to be shown on the plot with the date on the x-axis and the measurements on the y-axis. The problem I am encountering is excel wants to show all dates between the dates the measurements were performed.
A solution I have found is to switch the format of the x-axis to text but then excel plots the replicates on a horizontal grid line to the left (see attached spreadsheet). The intent is to have all the replicates for a given date on the same horizontal grid line.
I hope this makes sense - it is very hard to explain this situation in words.
Thank you in advance for any insight you are willing to provide.

**ovirgadamo** · 09-02-2009, 09:04 PM

Oh one more detail - I am using excel 2007

**teylyn** · 09-02-2009, 11:14 PM

Hi,

you need to change the chart type to XY Chart.
Then replace the names with simple numbers (formated as General). Just use 1, 2 and 3 instead of the dates. Now your chart is plotted like you want, but you need to jazz up the x axis labels.

Next, download XY Chart Labeler and install the add-in. Now, create a dummy data series with all zeros and the dates you want. Add the dummy series to the chart and format the dummy series to show no data point and no line to make it invisible. Use the XY Chart Labeler to attach labels to the dummy data series (choose "below"). Select the dates of your choice as the labels. Format the x axis to show no tick mark labels.
Finally, remove the dummy series from the legend.

Attached file shows the result and the changes to your data source. I have left the dummy series visible, but it's easy to reformat to no line, no marker to hide it.

Charts that have been created with the XY Chart Labeller can be opened on computers that do not have the add-in installed.

hope that helps

**shg** · 09-02-2009, 11:40 PM

Originally Posted by ovirgadamo

Oh one more detail - I am using excel 2007

You might change your profile to agree.

**ovirgadamo** · 09-03-2009, 01:14 PM

Thanks Teylyn - that worked perfectly. Now because all the replicate data is so close, I need to figure out how to generate 4 stacked charts all with different scales. Do you know how this can be achieved?
Thanks
PS - Thanks Shg for the comment - I have updated my profile.

**teylyn** · 09-03-2009, 06:23 PM

Hi,

you could create 4 separate charts, one for each dimension, all with their own indivitual (automatically scaled) axis. Then make them all the same size and align them on the spreadsheet. Only the bottom chart would need the x Axis label. The dummy series would need to be calculated to be just a fraction less than your lowest value on the bottom chart.

Hint: when you drag charts around on a worksheet and hold down the ALT key, they will snap to a cell, which makes it easy to align several charts!

Play around with the attached and let me know if that gets you closer to what you need.

cheers