Reading through some Facebook questions the other day, it hit me that most people are not keeping track of their genetic information for themselves. There are so many recommendation for ‘reports’ that you can order for $19 or $50 or much more. A lot of these reports just color your SNPs red or green, which isn’t necessarily meaningful. Some polymorphisms will make an impact on your health and some have no impact. So I’ve decided to explain how I personally keep track of information on my own genes (and for family and friends).
Short answer: I simply created an Excel spreadsheet, imported all of my raw data file, and use a separate sheet to look up my genetic info.
The spreadsheet isn’t beautiful, but it contains way more information than any of the paid reports that I’ve seen. Plus it has all of my notes that are pertinent just to me! It is important to me that I know the source of the information and to read through studies myself rather than just relying on what someone else might say. (There is a lot of speculation, misinformation, and wacky stuff floating around on the internet – especially on Facebook!)
A final advantage of creating your own spreadsheet to keep track of your genetic information is that it is private. I always worry a little bit about the security of sharing my 23andme data on another website.
Step-By-Step for Excel Gene Report using 23andMe Raw Data
Step 1: Download your raw data file from 23andMe. Log into the 23andMe website and go to the Tools section. Click on Raw Data, and the click on Download. That will take you to a page with instructions on how to download the file. After downloading it, you will need to un-zip the compressed file, and then I really recommend saving it in a place where you won’t lose it (i.e. don’t leave it in your downloads folder!).
Step 2: Open up Excel and start a blank workbook. Import your raw data file by going to File, Import, and choosing to import a Text File. (Or your version may have a Data tab and an icon for importing Text.) Find your .txt file that you just downloaded from 23andMe. It is a “Delimited file” that is tab delimited. It may take a minute to import all the data since your raw data file is over 600,000 rows.
Step 3: Open a new sheet by clicking on Sheet 2 at the bottom of your Excel screen. This new sheet is where you will make your genetic ‘report’.
Step 4: Here is where the real magic comes in! For each SNP id and risk allele, your alleles will be looked up and automatically included.
Go ahead and put in some data: put in MTHFR C677T under your Gene Name column and rs1801133 under your SNPid column. The risk allele for MTHFR C677T is A.
Then in the column for your gene data you will use the following formula to look up your information from your raw data sheet.
What this formula does is looks at what is in B2 (the SNP id) and compares it to everything on your raw data sheet (Sheet1) in all of the rows. Then when it finds a match, it returns your genotype for that SNP id.
Step 5: Add in some more Genes and SNP ids (read the rest of my blog posts to find more), and then copy the formula down under your gene data column by clicking on the little box in the lower right corner of the cell and dragging it down the columns that you want to fill. (You can copy and paste the normal way as well if you don’t like the ‘fill handle’ shortcut.)
Double check that you have copied correctly by checking that the VLOOKUP formula is always referring to the row that you are on (e.g. $B3).
Also, the “Risk Allele” always needs to be in the same orientation that 23andMe uses (referred to as forward or plus).
Step 6: Finally, add in some color to make it easy to read. Use whatever color coding system makes sense to you — this is YOUR spreadsheet. I did end up setting up Conditional Formatting to automatically color code my spreadsheets, but that is a more complicated tutorial for another day.