Using Spreadsheets

In recent discussions with a few of my genetic genealogy students, I discovered that many need some help with understanding how to use spreadsheets. So I went looking and found a series of excellent youtube videos that even taught me a few things. Here is the first one in the series.

He uses OpenOffice Calc which is free and happens to be the spreadsheet that I use.

The basic idea of a spreadsheet is to make a list of things that you want to keep track of, with the information about each of them listed next to them in columns. As you use it, you may decide to insert more columns, the things you are tracking for each, or more rows, the items you are interested in. You can also delete any of these and best of all, sort them.

For DNA tracking purposes, the only other important function to understand is formatting cells so that the numbers don’t surprise you by turning into dates or fractions when you do not want them to. Click here for a recent article claiming that 20% of scientific papers on genes contain gene name conversion errors because of this type of reformatting!

Personally I reformat the start and stop points to have commas so I can read the numbers more easily and make the centimorgans column (genetic distance) default to two decimal places so that they line up well. I also change the font to Arial.

Suppose you want to keep your match list in a spreadsheet. There are many articles on this blog that explain how to do that. Use the tag DNA spreadsheets to find them by clicking here –
Continue reading

Using an ahnentafel and the DNArboretum tool

There is a new tool, a chrome add-on called DNArboretum, that will generate an ahnentafel ancestor list from a Family Tree DNA tree or from the old format 23andme tree (not myHeritage).

An ahnentafel is a very clever and condense way to show all your ancestors. When trying to match up with a DNA relative it is particularly useful since you can quickly scan their ahnentafel for places and names in common. Obviously it would be better to automate that comparison but with misspellings and Norwegian names that has not worked well for me. However it might work for you, so click here for my blog post about how to use automation to compare GEDcoms.


This is part of my ahnentafel as generated by DNArboretum from my tree at Family Tree DNA when logged into another account there. I clicked on my great grandmother Maren Wold and it bolded all her ancestors and descendants. Note that my parents are missing because they are marked private.

Sue Griffith of Genealogy Junkie has blogged in detail about how to install and use this tool at

Continue reading

Organizing DNA results with your Genealogy:

While I have many spreadsheets that I use to analyze DNA results, what I also want is a field in my genealogy program where I can put simple DNA information like haplogroups, where the person tested, and the GEDmatch id number.

To my delight, the free online one world tree at has all those features. Plus you can see whom you might have gotten your X DNA from, as well as your Y and mtDNA ancestral lines. Another feature is that a person’s profile page shows the tests of relatives that are related by DNA. Here is my mother’s page:

Continue reading

Mapping an Ancestral Couple: a Backwards Use of my Segment Mapper

Sometimes I wonder if the interest in genetic genealogy runs in our DNA. I have found many more new cousins with autosomal testing that are descended from my WOLD line than in any other family. Yes they all had lots of children until recently, but so did the Munsons and the Skjolds.

So I decided to make a picture of the HIR (half identical region) DNA segments that I know come from my great-great-grandparents Jørgen and Anna Wold of Drammen, Norway. To do this I made a CSV file with a list of all the segments that are just from those ancestors. I put the first names of the group of matches in the column that would be the MRCA in the usual style segment map. I have to give credit to my distant DNA cousin (on the AJ side) Israel Pickholtz (he blogs too) for this wonderful idea of making a reverse segment map. Below is my picture of Wold DNA created with my DNA segment mapper tool. Click the image to go to the actual html page which will show the centimorgan values and names when you put your mouse on a colored block.

WoldDNAsmllThe use of two lines is arbitrary; I could have used three or four. The DNA segments shown are not separated into lines for Anna and those for Jorgen. Where that was possible, I had intended to do it with colors, but did not get to it, next version. Knut, OK, Nancy and Aaron are on Anna’s side while Susannah, her mother, and Lester are on Jorgen’s side only.
Continue reading

The DNA of eye color: it’s complicated

In high school I learned that blue eyes were a simple recessive gene so two brown-eyed parents could have a blue-eyed child but not vice versa. Recently I discovered that this was wrong. Many genes are involved in eye color and with the subtle variations in eye coloring.

GEDmatch eye color prediction for me eye color prediction for me

So I ran the fun eye color calculator at and discovered that it shows many of the SNPs involved in my eye color with the details of what they each do.

I had not realized that blue eyes are caused by the lack of (brown) melanin, so really are no color. Blue happens the same way the color of the sky does: light is bounced around in a way that appears blue according to the wikipedia article on eye color. Maybe that is why my eyes appear greenish when I wear certain green shirts and turquoise when I wear those color shirts.

If the two blue-eyed parents have their lack of eye color melanin caused by different genes then they could have a brown-eyed child. I happen to know a brown-eyed child of green and blue-eyed parents (and she looks just like her Dad so not what you are thinking). This case got me curious about how that could happen. So I googled around and found this wonderful article that explains it:

Here is my attempt to simplify the ghist of that article:

Continue reading