Guest Post: Comparing Admixtures Using Visuals

By: Erik Schaefer

Remember the excitement of receiving your first DNA results and wanting to share them with everyone? I was that person once. But after that excitement fades, many of us realize there are differences between our “paper trails” and our admixture estimates.

Here is a cool visual way to capture those differences in a combo donut chart:

This post will teach you how to make a donut chart for yourself. You will need Microsoft Excel and a basic understanding of how to use it.

The instructions have you go through the process once to learn how to do it, then gives you pointers for how to refine your chart to suit your tastes.

The Process

The steps below for creating an Admixture Combo Donut chart are based on the Windows version of Excel Office 365. Other versions of Excel and other operating systems may look somewhat different and may require you to improvise.  Now, let’s create the chart.

1. Open Excel and make two lists, one for your paper trail regional estimates based on your family research, and one for your DNA-based ones. Mine paper-trail estimate is very simple, as you can see. If yours is more complex, you may need to make best guesses. Remember, this is for fun. It doesn’t have to be perfect. For your DNA estimates, simply type in the categories and percentages exactly as they appear at your testing company (AncestryDNA, 23andMe, MyHeritage, etc.)
2. Place your cursor in one table and go to the “Insert” menu at the top to create a pie chart.
   
3. Right click on your chart and choose “Select Data”. You should get a window similar to this one.

4. In this window, click on “Add” to add the data series for the other table you made. You should get a popup with fields to identify the new data range.
5. You can leave Series Name blank or type in the source of the estimates, e.g., AncestryDNA. Then click, in the “Series Values” field. Erase what’s there, then select the percentages (and just the percentages) from your other table. Press OK.
6. You should still have the “Select Data Source” window open. Click on your new data series once. In the Horizontal (Category) Axis Labels field on the right side, the new axis labels will just be numbers, like this:
   Click “Edit” on the right hand side to make the “Axis Labels” window appear. Highlight the column in your second table with the admixture names. The numbers will be replaced with names like “Scandinavian”, “Swedish”, etc. Press OK.
   

7. At this point, you will see your original pie chart with some extra legend entries at the bottom for the new axis labels (admixture groups).
8. Click on your chart, go to the Design Menu at the top, and choose “Change Chart Type”. You may get a pop up window like this:
   This is where the magic starts to happen. Decide which data you want in the middle of your chart and what you want in the outer ring. Set the “middle” data to be a pie chart type. Then set the “ring” data as a doughnut chart type and check the box for Secondary Axis. Press OK. We are almost done!

9. To add data labels, click on any section of the inner pie chart to highlight it. (Sometimes it’s fussy and needs to be double clicked. “Undo” the changes and try again if this doesn’t work right.) Once the section is highlighted, right click and hover over “Data Labels” in the pop up menu. Another small window will open where you can choose “Data Labels” or “Data Call Outs”. You want to add “Data Call Outs”. This will label each section with name and percentage.

Formatting

Now, you can format the chart to your tastes. Right click on a section of the chart, right click again, go to “Format”, and choose a fill color for that section. You can also add a chart title, delete/modify the legend, etc.

Spice It Up

Because my ethnicity is so simple, I decided to spice it up a bit and use country flags. I got mixed results.

The Good:

The Ugly:

Here are the steps:

1. Google a website with world flags and download the relevant ones to your computer. I did them all from a single website so they were the same size and resolution.
2. Right click a section of the chart and right click again. Go to “Format Data Series” (as you would to change the color). You should see this as your “Fill” options on the right side:

Rather than doing a solid fill and choosing a color, click on “Picture or Texture fill”. Choose the “File” option and point it to the flag file you downloaded to your PC. Repeat this for each section of your chart.

Final Touches

Perhaps the most difficult part of this process is arranging up your admixtures in a way that makes sense. Excel creates pie and doughnut charts based upon the descending order of your list. The first entry will form the section at “12 o’clock”, with the others going clockwise around the chart. Your paper admixture will have different start/stopping points than your DNA admixtures, but we want them to line up visually as well as possible. (Remember that it won’t be perfect.)

Here are my tips:

1. Determine your two largest paper admixtures first. Put the largest one first on your list and the second last on your list. This makes the “12 o’clock” position on your chart a clear dividing line.
2. Combine a little artistic license with your knowledge of DNA and history. For example, maybe you have UK and Scandinavian ancestors. We know those groups sometimes blend/overlap, so group them together in your pie chart because your admixtures will likely overlap. For example, if your paper ethnicity is German, Scandinavian, and Chinese, don’t put Chinese in the middle of the German and Scandinavian.
3. Rearrange your second table (outer ring) to align with your paper admixture as best as you can. In my example above, the data series for the inner circle starts with Denmark while the data for the outer circle starts with nearby Norway and Sweden. On the flip side, I put my German DNA at the bottom of my second table because I wanted it to be that final segment that aligns the outer ring to the “12 o’clock” position.
4. There will be some DNA categories that are more broad (see my example where I have “NW European”. In my case, that’s likely some of my assumed German and Danish heritage, so that crossed both ethnicities. This is where grouping your inner circle can help fill in these gaps.

Play around with it. Changing the order the data appears in your tables will affect how the segments are positioned clockwise around the charts.

NOTE: When rearranging items in your data table, ensure you are moving both the percentage and the category name together. Also, moving data often messes up the “Data Series” references, so you may have to redo Steps 3 through 6. This can be avoided with really detailed planning up front, but I find it’s easier to make the chart and visually see how things line up before deciding what to change.

Conclusion

I hope you enjoyed this visual trick. Excel will let you add multiple Doughnut charts so in theory you could show multiple DNA testing companies with your paper trail, but I prefer to keep it simple. Some other comparisons you might consider:

1. Use one relative as the center Data Series, and a different family member (e.g., parent or child) as the outer ring.
2. Use one DNA company in the center, and another in the outer ring.

Play around with it, and share your results. I would love to get more ideas based on ideas you submit.