Creating your own genetics “reports” for free

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.

Comments 8

  • Thank you for this information!

  • […] 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 […]

  • 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!!

  • 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

  • Has anyone used Open Office to do this?

    • 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

  • 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!

    • 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

Leave a Reply

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