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: Importing text fileOpen 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’.

I set up mine to have headings across the top of Gene Name, SNP id, Risk Allele, person’s name (yours!), and Notes.  Here is an example with some made up data:  example Excel gene 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.

=VLOOKUP(TRIM($B2),Sheet1!$A$16:$D$610564,4,FALSE)

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: Excel copyAdd 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.

Categories: Genetics

8 Comments

Karla · October 20, 2016 at 11:43 pm

Thank you for this information!

Michelle · March 1, 2017 at 8:36 pm

Thank you for this guide, but I am still so confused. I asked my partner who is an IT professional to help me, but he is confused too.

How is it that Excel can compute and translate all this?

Where do we enter the formulas and how do we know what each formula is?
(like the example above of VLOOKUP(TRIM($B2),Sheet1!$A$16:$D$610564,4,FALSE)

I saw in the comments “highly suggest putting them into an Excel file or installing the SNPtips extension for Firefox” is THAT what is decoding the raw data?

I want to do this myself for all the reasons you stated, but if I simply can’t figure out how to do it on my own is there a legit website you could suggest to ‘decode’ my raw data from 23andme?

Thanks so much!!

Cathy · May 12, 2017 at 10:08 am

Michelle, have a look at a reference explaining excel’s VLOOKUP function, such as this: https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1
Hope this helps

TrixieN · February 25, 2018 at 3:44 pm

Has anyone used Open Office to do this?

    Debbie Moon · February 27, 2018 at 9:10 pm

    Hi – I think Open Office should work just fine since you can have up to a million or so rows of data in their spreadsheet software. Google Sheets, on the other hand, can’t handle the number of rows needed (~600,000).
    Debbie

Amy · March 12, 2018 at 4:08 am

Ok, I love you for this! But I was wondering if you have any ideas or tips to autofill the notes section based on the results that autofill under the name? I am in school for genetics and want to do a sheet like this for many people but with information, I have learned for each gene based on wildtype, heterozygous or homozygous. I just wanted to see if there was a way so I wouldn’t have to manually type them in each time? I would appreciate any input.
Thank you!

    Debbie Moon · March 14, 2018 at 11:40 pm

    Hi Amy – Glad you are able to use the ideas on importing the raw data file into Excel. I don’t have any quick ways to autofill the data on the genes though. I’ve ended up doing a lot of cutting and pasting to create my own Excel file that contains pretty much everything on the Genetic Lifehacks blog.
    Debbie

Checking Your Carrier Status for Genetic Diseases | Genetic Lifehacks · December 30, 2016 at 4:03 pm

[…] genome, and this is just a list of SNPs that I’ve compiled along the way.  I highly suggest putting them into an Excel file or installing the SNPtips extension for Firefox as an easy way to see your data.  Quite a few of […]

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Posts

Genetics

Review of 23andMe’s GrandTree Feature

Ever wonder whether you get your red hair from your grandmother or grandfather? Is it grandma’s fault that you are likely to go bald? The GrandTree feature on 23andMe allows you to link together three generations Read more…

Genetics

PMS, Genetics, and Solutions (Patrons Only)

A lot of women know the moodiness and brain fog that comes with premenstrual syndrome (PMS).  It can range from simply feeling irritable and icky to being something that really interferes with our lives. What role Read more…

Genetics

Ancestry.com vs 23andMe: Comparing the raw data files

I recently picked up an AncestryDNA kit out of curiosity to find out how well the data matched up to the 23andMe test that I did a few years ago.  Quick answer: It matched up Read more…