Monday, August 8, 2016

Your DNA Cousin Match Database: Getting Started with my Excel Template

A reader from a recent post requested a template for setting up a DNA matching database. Because it's quick and easy to set one up in Excel, I threw one together quickly. Please feel free to reuse for non-commercial purposes, but not to repost.






When you open this file, it's going to be a View Only file in my Google Drive. In order to use it yourself, you need to download it. The link to do so is on the top right corner, next to the Print option. You'll then be free to use it in Excel, or with whatever other spreadsheet software you want to use that is compatible with Microsoft Excel.

I've got the Filters already set up and enabled, which means you'll be able to sort and filter information when you analyze the data. In order to see those matches with the greatest similarity/longest segments, sort your Segment Length column from largest to smallest. If you want to see the other matches that might also connect to a given cousin on the same chromosome, sort the Starting or Ending Point from largest to smallest. Reading the ranges between these two numbers, determine where there may be overlap. Use the matching utilities from your testing company or analysis website of choice to determine whether a match exists. Record your findings in your Notes section. If you're able to determine who the common ancestor(s) are that you share with a match, record that information in the Most Recent Common Ancestors (MRCA) column.

If you decide you want to move different columns around, or add more columns, I recommend turning off the Filters (highlight headers, Data > Filter button toggled off), changing things around, then turning the filters back on.

If you use the table in Excel, I've frozen the column headers, so they continue to show up as you scroll down through the data. To enable or disable this option, highlight the headers, go to View > Freeze Panes > Unfreeze Columns, Freeze Top Row, or Freeze First Column, according to what your preferences are. I don't know if this option works in Google Sheets or not, or in any other spreadsheet software. So bear that in mind.

However, now that I'm seeing this open in Google Sheets, I think anyone who decides to use this may want to consider using it there. Not only will this allow you to access your spreadsheet from multiple devices at once, the layout of the file itself is much nice in Sheets than in Excel. Because of the way Excel scrolls the tabs across the bottom of the screen, you have to click through a series of tabs over and over again to get from Chromosome 1 to Chromosome 22. In Google Sheets, however, all of the tabs display across the bottom without any of them being hidden. This may not be an issue, depending on the size of your monitor or display resolution. But definitely check around to see what you like/makes your life easier!

Let me know how it works out for you in the comments, and be sure to like and subscribe to our Genetic Genealogy series on YouTube!

Popular Posts