How to Make a Spreadsheet

To begin you need a software program. Any program will work and the two most popular are Microsoft Excel and Open Office. I keep all my spreadsheets in one folder and I make a spreadsheet for every surname that I work on. Spreadsheets can contain multiple pages and I always have two “Master” page that use the design below and an “Email” page (instructions at the bottom of this page)

Open Office is a free program that works like Excel and has the option of saving as a whole list of file types including the Microsoft file types.
You can learn more about it and download it for free here...

The items you will want to include are:
  • Tester's name
  • Tester's Id
  • Tester's email address
  • Tester's gedcom or tree address
  • Chromosome
  • Segment information
  • Segment length
  • SNPshis information will depend on the sources that you have available. Gedmatch supplies the maximum amount of info at this time and Family Finder rates second. If you know how to find most of this on 23&Me, please...teach me.














 M111111(*user name) 1 5,605,419 7,108,912 3 540  B111111 (*User name) 1 61,117,597 63,340,244 4 815

Email address 1 20,756,403 22,410,236 2 616 Email address 1 70,714,938 73,262,084 1 773

Gedcom Id 1 22,605,731 24,318,715 2 501 Gedcom Id 1 93,971,444 94,840,685 1 761

Names researching 1 166,960,836 168,445,688 2 639 Names researching 1 97,704,844 99,324,396 2 665


1 218,222,713 220,289,474 2 631
1 106,576,454 108,086,911 2 517


2 71,229,709 73,073,315 3 511
1 221,693,647 224,580,957 2 879


2 112,366,265 113,729,938 1 521
2 29,235,542 30,347,676 2 564


2 176,365,468 178,264,524 2 686
2 35,059,456 36,655,503 2 668


2 202,542,373 205,051,704 2 660
2 43,282,562 45,014,802 2 625


3 38,542,071 39,860,941 1 512
2 218,403,347 219,938,372 3 543


3 78,677,509 81,539,342 1 595
2 235,704,608 238,174,498 4 820


3 88,813,802 98,957,320 1 964
3 12,000,633 13,085,476 1 548


3 110,471,967 113,336,887 2 770
3 124,286,063 126,184,698 2 686


4 65,419,204 67,921,247 1 667
3 152,126,063 153,830,172 2 557


4 70,440,426 72,874,940 2 794
3 158,286,259 160,498,181 1 545


4 103,334,045 105,611,471 1 539
4 8,721,240 10,540,686 2 607


4 131,432,039 134,304,735 2 604
4 23,876,247 25,628,156 4 713


5 51,485,394 53,624,341 2 826
4 78,241,928 80,481,545 2 617


5 152,766,713 154,825,908 1 723
4 106,923,615 109,628,057 3 639


6 32,298,006 33,662,125 1 1,732
4 147,674,442 149,712,643 2 526


6 56,530,175 64,194,845 1 649
5 28,345,850 30,995,561 2 740


7 90,537,009 93,177,711 2 661
5 90,457,972 94,221,220 2 709


7 93,773,013 95,454,245 1 928
5 98,552,687 101,138,197 1 530


7 97,458,627 100,050,190 2 732
5 128,563,297 132,243,706 1 909


7 100,375,679 103,143,511 2 669
5 144,529,286 146,534,556 2 562


7 108,165,064 113,523,084 4 1,332
6 15,096,746 16,803,509 3 674


8 15,008,484 16,692,519 2 979
6 34,273,699 36,712,176 2 753


8 17,462,963 18,130,497 1 534
6 77,790,969 80,460,700 2 751


8 26,592,360 28,120,034 2 813
7 57,791,605 66,562,032 2 623


8 41,807,985 49,445,899 1 600
7 81,620,533 82,979,598 2 507


8 49,752,259 52,831,002 2 682
8 15,048,322 16,060,383 1 717


9 92,641,776 94,623,293 2 614
8 49,752,259 52,730,334 2 651


9 95,535,736 97,997,798 3 701
8 82,379,143 84,911,494 2 622


9 102,467,562 104,162,171 2 699
8 107,465,164 109,089,111 1 541


10 55,156,023 56,697,226 1 663
8 119,663,571 121,339,017 2 566


10 66,154,144 67,558,235 2 507
9 39,099,101 71,442,089 3 500


10 102,880,411 105,321,751 1 585
9 92,074,547 93,456,890 2 551


10 116,167,599 117,969,308 2 538
10 36,074,244 41,829,800 2 675


10 122,798,043 123,828,016 2 505
10 56,704,046 59,012,627 1 632


11 26,338,978 28,976,726 2 758
10 116,498,137 118,345,520 3 543


11 34,540,958 35,516,874 2 501
11 89,807,187 92,230,096 1 673


11 87,787,221 93,214,447 3 1,509
12 48,618,337 50,709,301 2 554


12 32,926,708 39,315,471 1 1,322
12 55,535,867 57,427,666 1 559


12 84,600,116 87,969,670 1 660
12 57,509,946 59,836,287 1 526


12 94,256,727 96,210,555 2 739
12 61,526,939 63,866,140 2 657


12 100,530,377 102,293,452 2 833
12 105,174,481 107,036,884 2 518


13 80,059,655 82,122,720 2 553
12 107,225,687 111,757,873 4 1,098


14 48,870,922 50,825,294 2 661
13 18,212,519 20,044,232 2 563


14 62,652,487 64,681,721 2 642
13 46,564,773 48,782,056 1 689


15 33,947,253 45,921,089 14 3,773
13 63,101,745 65,250,675 1 534


15 97,485,209 98,689,102 2 512
15 21,465,730 23,295,536 2 534


17 17,198,571 19,822,676 2 597
16 1,478,465 2,177,550 2 713


17 23,634,896 26,787,499 1 667
16 55,646,631 57,102,569 2 584


17 40,660,914 43,910,744 2 759
17 58,095,649 60,901,071 2 722


17 58,977,373 60,995,973 2 539
18 60,706,714 62,923,071 3 616


21 19,326,788 20,776,591 3 538
22 22,514,850 24,261,372 4 547


21 28,362,971 31,411,428 3 973
22 31,102,712 32,137,636 2 537


21 42,540,934 43,594,239 2 575
X 152,717,974 154,886,292 2 834


X 47,241,932 51,486,924 5 614







X 53,175,498 67,938,541 4 982







X 102,918,549 109,178,124 2 754







X 152,717,974 154,673,882 2 776





Gedmatch


Tester's name, Id, email address and Gedcom Id are all available on the User Information Utility if you have at least one piece of information.
The One to One tool provides the chromosome, segment, segment length and SNP's. Refer to the page on how to change your settings to get the best results for your individual project.
The X One to One tool provides the chromosome, segment, segment length and SNP's for the X chromosome only.
Entering the data is relatively simple. Using your Id and the person that you are interested in seeing if you are DNA cousins Id go to the One to One tool. Enter your search criteria or settings. If you want to find more recent cousins do not change anything which leaves all boxes but your Id's blank. Gedmatch has already set the tool with default settings. This might be good for searching for adoption etc. If you are a family historian, you may want to lower the settings. As the generations pass, it appears that we get smaller bits of DNA from each previous generation. There are exceptions to every rule and when you share more than one set of common grandparents, you may have longer or more segment information show up. Almost all of my personal research is from 1800 back so I use low settings of 500 for Minimum SNPs (this is a unique bit of DNA material that provides for a specific trait) and 1cm for segment length. Highlight the result table and copy/paste it into your spreadsheet. Allow room for the name and the color tool bar, so start in the third column. I then return to my results and copy/paste the Id and name exactly as it is for consistency. I may then add the personal name after a few emails and we have worked together.

Do the same for the X chromosome. Check them all but you will have better results with a female but user names do not necessarily reveal sex. If when you go to the user Id, copy all user names as you may have a mother or sister available and that would give you a chance to find the X matches but you can always run the User utility a second time if this is a good match and you want to explore it further.
I am a very visual person so I came up with a color coded system to mark the matches. I have thought about a database but I am finding that I can now spot segments that seem to turn up a lot for me. This can be a clue for further research. I chose 22 colors to make a toolbar and you can use any colors you like but use the same toolbar for all spreadsheets. It will be very useful when you start seeing that the same person fits into five or six families.
I then chose a color for segment length and three colors for SNPs. I mark all segments over 4cm in length. Sharing longer or multiples can indicate either a closer relationship or multiple lines. The number of SNPs reveal the amount of exact DNA that you carry that is the same as this individual, such as red hair or brown eyes. You can actually share a lot of the same DNA packed into a small segment and there is a growing conversation about which is more important. I do not get too involved in this type of thinking because this is still a new field and changes quickly but do believe that having this info available if there is ever a breakthrough on this debate, I will be ready with it in my spreadsheet.
I then go through the chromosomes and mark the matches using the “fill tool”.
In the sample above person A is a cousin from the 1760's with our grandfathers being brothers. Person B is someone I found in a Gedcom search that belongs to her grandfather's wife who is a Sizemore. I match this person but have no idea how and since it is an X match we share a grandmother. Person C is a person found in a Gedcom search that is a Bunch which is the grandmother of our shared Grandfathers, time period. 1700.

What I did not expect was to match the Sizemore but I do. I will do two things. Create a Sizemore spreadsheet and collect six to ten random Sizemores using the Gedcom search tool and see if I match more of them. If I do then I can check out there gedcoms or contact them. The second option is to contact them first. I always do the spreadsheets first as I have better luck when I know more about a family before making contact.
The second thing that I did not expect was for the Sizemore and the Bunch to match. Here would be a good time to just contact this person to see if they know of having Sizemore or Bunch in their grand-parental lines. Perhaps one of my ancestors married a Bunch and his sister married a Sizemore. We would all be sharing the same DNA .

In the end, it is always necessary to confirm that each person is in fact a direct line descendent of the surname you are working with or that they think it might be the name of one of their “unknowns” that was living in the same area at the same time.

Family Finder


I begin in the Matches by using the “Ancestral Surnames” and do a search. I usually spend some time looking at any available trees to see if I learn anything there. I then choose one to start with (my reasons can vary), and do the “Run Common Match tool” Last icon on the toolbar below the name. I search for “in common with” and write down those names.

Next, I go to the chromosome browser. Reset the Cm down to +1 and compare five of the names.

Select “view data in a table” and you get a pop up. I then copy/paste each of these in a spreadsheet.

I return to the matches pull up my original person and rerun the “Run Common Match” tool. This time click on the name and copy paste the email address into your spreadsheet. There is also a list of surnames available that you might like to copy for further reference but if you have the name and email, you can always find them again if it works out to be a good match.

Segment length and SNPs is not available but it is not critical in finding your relatives. I do keep a list of these email address or if I have time, go to Gedmatch and using the email address, I can check to see if this person has registered with Gedmatch. If so, you may find more matches and some original 23&Me testers that could help you build a family group or compare them to sample of grandparents hat are you think may be the shared line.

Email Page

Using Family Finder, I copy paste all emails in column one. I then can add a column for contact made, note or the surname list if they posted one at FF.


On Gedmatch, my Email pages contains my Gedcom searches. See Gedcom Seaches for tips and tricks.

No comments:

Post a Comment