EXCEL DNA Segment Grapher

I cobbled this spreadsheet together in Excel to let me examine Chromosome Browser matches from FTDNA graphically for an entire Chromosome. It is an .xlsx file (I don't think the "stock market graph" I'm misusing to do this works in the older formats of Excel). You'll note that the "chromosome browser" here is turned 90 degrees on its side. I couldn't figure out a way to make Excel rotate it. Maybe someone else does. But this works. Many thanks to Sue Griffith who really enhanced it with automated titles, axis labels, and start and end point data labels!

If you want to use it for your purposes here's how to do it:

1. Use DNAgedcom.com to download your consolidated Chromosome Browser file (all the matching segments to any of your matches). You can do this manually, but it requires loading matches into the Chromosome Browser 5 at a time and downloading small .csv files and then combining them when you're done. DNAgedcom does that for you automatically.

2. Open the DNAgedcom Chromosome Browser file in Excel and sort on the cM column. Delete all the rows that have segments less than 5 cM. (You can use all the segments, but it makes it hard to see the difference between IBD and IBS segments. You can also cut off at 7 cM or 10 cM - up to you!)

3. Sort on Chromosome and Start position columns.

4. Copy the rows of data for the Chromosome you are interested in to the clipboard.

5. Open this file: SURNAMEgivenChr00.xlsx

and clear the contents of the cells that have the data in them. Then paste the data you copied into the same location.

6. Click in the blank area of the graph so that the bounding boxes appear around the data columns. Drag the corner of the box either up or down so that all of the rows of data are included.

7. Depending on how many rows of data you have, you may need to adjust the size of the graph box so that you can read all the names at the bottom of it. If you do that, you may also need to set the print area so the table and graph are all within the printable area.

If you hover your cursor over the top or bottom of a segment it will tell you which person you are looking at. When I get a test back I find it useful to print out the graph for each chromosome's segment matches, and then circle "stacks" of matching segments and use that as a guide to triangulate in FTDNA to make sure that all the segments match each other. Often one or two of the segments may not match the others when I triangulate (meaning they may be a match on my other parent's chromosome). Note that, as with other chromosome browsers, the scale is to base pairs, not cMs, so some segments will appear larger or smaller than they really are in cMs.

It's also fun to play with it by changing the sort of the input data. For example, I built one spreadsheet with all the matching segments of a chromosome, no matter how small, but then sorted them by cM instead of start point. It's interesting to see the change in the pattern of segments between what must be smaller IBS segments, and the longer IBD ones, and the long runs of stacked matches among the IBS segments. You can also plot more than one chromosome on a single graph. Also, I suppose you could use this to document the segments for which you've found a common ancestor.

Hope this is useful to you. Let me know if you have any questions about it.

If you want something more interactive and color-coded, you should use Kitty Munson Cooper’s One Chromosome Grapher here:

http://blog.kittycooper.com/tools/one-chromosome-mapper/

I prefer to use Kitty's mapper for quick switching between chromosomes when I am first browsing through a match file, and for the final printout, however my Excel grapher is nice for printing out working copies and marking them up since all the segment match data is there in tabular form and there is no limit on the number of matches it can depict.

Don Worth
worth@ucla.edu

12/3/2013