To get the most out of DNA testing in family history you need to test multiple people and upload your test results to multiple websites. This creates a lot of data and Excel can be used to store and manage this data in a single location.
Why I use Excel for DNA
Gather
Excel allows you to aggregate data from multiple DNA kits in one place, in one file. You can then compare the matches between kits and get a broader range of data – for example, my sister might have a DNA match that I do not have, but I can still use that information if I store it in Excel.
Patterns
The spreadsheet format of Excel makes it easier to see patterns in the data. These patterns provide clues to how your matches may be related to you and to each other. Before DNA clustering tools became available, I used to use Excel to discern clusters.
Customise
Third party tools are great for DNA analysis, but you are bound by the design and limitations of that tool. With Excel you can customise and design tables to meet your own specific needs.
Adaptable
You only have to enter the data into Excel once, then you can manipulate it in different ways depending on the question that you want to answer on any particular day.
The downside of using Excel for DNA is that most of the data has to be entered manually, which can be time consuming.
How I use Excel for DNA
My Excel spreadsheet serves three purposes. I use it to:
- record information about DNA matches for all of the test kits that I manage in a single spreadsheet
- analyse how the matches may be related to me and to other matches, and
- plan my research to test hypotheses based on the DNA data.
Step 1 Worksheet 1 – the main worksheet
I began my worksheet by copying and pasting my test results from GEDmatch. If you are a Tier 1 member you can avoid the copy and paste by downloading your top matches as a CSV file. Alternatively, you could download your test results from your testing company as a CSV file, open it in Excel and then save it as an Excel file.
Step 2 Finalise your columns
If you read my other posts about using Excel you will know that columns are the units of analysis when using Excel for family history, so you should keep any columns that are relevant to your analysis and delete the rest. You may also need to split some columns into two – for example, I separate the surname of the DNA match into another column so that I can sort or filter using the surname.
Add new columns if needed and rename columns if necessary.
Here are the columns that I use:
Record:
- Kit (GEDmatch kit no. or A for Ancestry, F for FTDNA etc.)
- User name/Alias
- Surname
- Which kit (which of the kits I manage has this person as a DNA match)
- Autosomal total cM
- Longest segment
- Estimated generations
- Chromosome
- Start point
- End point
- cM on Chromosome
- SNPs
- X DNA total cM
- X DNA longest segment
Analysis and Plan:
- Relationship (to the tester)
- Analysis group
- Side (Maternal or Paternal)
- GP group (based on the Leeds Method analysis)
- Confirmed common ancestor (Y or N)
- MRCA (most recent common ancestor(s))
- Location
- Notes
- Actions required
- Contact email
Step 3 Enter data
Enter data in the columns about matches from other sites or other test kits. This will have to be done manually, so focus on matches greater than 40cM first, then add those greater than 20cM. Smaller matches can be added later if they are relevant to your analysis.
In most cases, each DNA match will have a single row. However, close matches will have multiple rows as they match you on more than one chromosome.
If you do add data from other test kits, a DNA may have multiple rows because they appear in different kits. You can sort the data by match name to identify these.
Ancestry does not provide chromosome data, so Ancestry kits will have some empty columns.
Step 4 Add filter buttons to each column
Click in Cell A1, click on the Data Tab, then click on the Filter button. This adds a little drop-down arrow to each column. You can use the drop-down arrows to filter the data using any column.
Step 5 Fill in the Analysis columns
Analysis group – Create groups and assign matches to the groups based on your analysis of relationships between matches. This is just like the coloured dots in Ancestry but in Excel you are not limited to 24 groups. The groups you use are entirely up to you.
Side (Maternal or Paternal) – If you have tested someone on your mother’s side or your father’s side, use this to designate a match as Maternal or Paternal.
GP group (based on the Leeds Method analysis) – I create a version of my family tree and assign letters and numbers to them, based on my four grandparents. See Worksheet 3 below. For an explanation of the Leeds Method, see below.
Confirmed common ancestor (Y or N) – Have you identified the ancestor that you and that DNA match have in common?
MRCA (most recent common ancestor(s)) – Name the ancestor or ancestral couple that you and that DNA match have in common
Step 6 Worksheet 2 – Group index
Prepare an index to your Analysis groups. I use seven columns as shown below. Add chromosome data, if it is available.
Step 7 Worksheet 3 – Leeds Method Index
Prepare an index to your four grandparents and their direct ancestors, which is then used in the Leeds Method Analysis. I include a numbering system to help me label subgroups of each grandparent line.
Step 8 Worksheet 4 – Leeds Method Analysis
Record your Leeds Method analysis. You will need six columns as shown.
Using Excel for this analysis means that you can use the sort and filter functions to help you analyse the results.
Step 9 Transfer
After conducting your Leeds Method Analysis, transfer the results to your main worksheet, using the keys from the Leeds Method Index.
Step 10 Use your table
Other analyses that you can conduct on the main worksheet include:
- who are my closest matches – sort by the column labelled Autosomal total cM, largest to smallest; or sort by the Estimated Generations column, smallest to largest
- who matches me on a particular chromosome – filter by the column Chromosome, then sort by Start Point smallest to largest and look for overlapping segments
- which kits are for people who may be related – sort by Contact Email to identify kits that are being managed by the same person (NB that does not necessarily mean that they are related, but it is possible)
- Sort or filter by Location to get clues that may help determine how the match is related to you
Step 11 Hyperlinks
You can also include hyperlinks in the Notes column to websites such as the match page on the testing site, family trees or DNA painter; or to other files stored on your computer. On the Insert Table, click the Hyperlink Icon, add a link in the Address field or browse for a file on your computer.
Further information
For more articles and information about using Excel in family history, head to the Excel page. You can also download your free copies of my Excel guides on my Free Stuff page.
For more of my articles about DNA, go to the DNA in family history page. Or jump straight to this article: Is DNA essential evidence for family historians?
Instructions from Dana Leeds on her method: Leeds Method of DNA analysis
Post updated 5 June 2024
