Blog post

Monday, July 10, 2017

Visualising Ancestry DNA matches–Index

This series of posts describes how you can use a free Excel template (Windows only, I’m afraid) to draw a network graph of your Ancestry DNA matches.

Published posts

Part 1: Getting ready

  • NodeXL
  • DNAGedcom

Part 2: Loading files the first time

  • Additional input file (‘skip’ close relatives)
  • Load in-common-with
  • Load matches
  • Load additional input
  • Identify groups

Part 3: Navigation and presentation

  • Hiding columns
  • Zooming in
  • Changing layouts
  • Dot sizes and labels
  • Scale the features

Part 4: Updating

  • How new data is treated
  • Clearing duplicates
  • Updating – the process

Part 5: Busy graph diagnosis

Part 6: Busy graph treatments

Part 7: Adding shared administrator lines

Part 8: Adding known ancestors

Part 9: Combining kits

  • Loading more kits into the same graph
  • Using a formula to adjust dot sizes

Part 10: Colour coding

  • Show colour per vertex
  • Colour one node at a time
  • Apply colour codes in bulk
  • Colour coding examples – Branch, Side, Place, Heat map


  1. I'm making my way thru this with the help of your excellent posts. looking forward to the simplifying a busy graph.

  2. What a fantastic job you've done Shelley!

  3. Thank you very much for these posts Shelley. I'm looking forward to parts 4-6.

  4. I'm curious: What would be the minimum requirements for a computer doing this project in terms of working memory?

    1. Hi Anonymous, I don't know. There may be answers on the developer's page ( I certainly see a difference in performance between large and small in-common-with files on my PC.

  5. It took me a while to figure this out (I can be slow), but once I did, I found this to be an incredibly helpful tool. Thank you SO much for blogging on this tool!

  6. This is from NodeXL's web page: "A bad experience. Didn't work. My experience is that it doesn't install to Excel2016/64bit & Win10, despite a statement that the installation was successful, It's nowhere to be seen. This was a big disappointment, and checking the logs many users have reported similar problems to mine, without followup. All very frustrating, as the tool very good from what I've read. I've gone to the trouble to create an ID here just to enter yet another review in the hope that the developers get this back on the rails soon. With so many high profile organisations in the mix, my expectations were different. Maybe I just came to try this out at a bad time. Please let me know if things improve.
    by pjmcleod on Aug 4, 2016 at 11:20 PM" I am having the same problem

    1. Hi Julie, How frustrating!

      You won't see any difference when you open Excel normally, only when you create a new worksheet using the template. If you open Excel and click File - New then hopefully you will see it as an option in the list of templates.

      If not, do you know how to search your PC? The file you need to find is NodeXLGraph.xltx. On my system it's sitting in Documents/Custom Office Templates. If you find it, double click to create a new worksheet based on the template (don't open it using File Open from Excel or you will be editing the template itself).

      I hope it works for you. If it does, could you please comment here again so that other people will know what steps worked?

      If you can't find it, please message me using the "CONTACT ME" link at the top of my blog as there's one more thing I can think of but I'll need to email you.

    2. Hi Shelley, in Win10 NodeXL template can also be accessed from the Start Menu and scrolling through the alphabetical index or by typing NodeXL in the search box adjacent to the Start button.

  7. Does this only work with dna test? I've tested at FTDNA & 23andme

    1. Hi Sybil, you can do this with any 'in common with' and match list. My FTDNA chart was much more interlinked and complex than my Ancestry one because FTDNA data includes more distant 'in-common-with' relationships. If you try it, enter 'skip' next to the most distant matches to filter them off before you try to create a chart. I haven't yet tested it with 23andMe data.

  8. Hello. I have downloaded the files from Ancestry through NodeXL, and I have the matches file and the in-common-with file, and a tree file, but I don't have an additional input file. I don't see how to find it to download it. What should I do? Thank you in advance.

    1. Hi Richard, it's not a download file. You make it yourself (see part 2). Alternatively, you can enter 'skip' in the visibility column manually for the appropriate people.

  9. I tested the download site a minute ago - it works for me. I used the big purple button on
    Maybe try using a different browser?

  10. Hi there, I have got the graph to appear for one of the accounts I administer but not another. I have rechecked and reloaded it multiply times. Is it correct that I have an additional input file for each person. I notice the dynamic filters column has no data in the file that doesnt produce a graph. Any ideas about where im going wrong would be most grateful. Its a great app. Kind regards Jocelyn

    1. Hi Jocelyn, the main point of the additional input file is to set the visibility of the focus person to skip - so yes you do need to have one for each person or to set that value manually. Are the first two columns of the Edges sheet populated? If not, did you remember to fix the import options so that the sheet wouldn't be cleared each time you load new data?

  11. Re the template: I just went through a frustrating period trying to download the template on a Windows 10 computer and finally discovered with the help of the manager at a computer store that some machines will do it easily but apparently some will not. I had to trade a Lenovo for an HP to get it downloaded.

  12. I am loving this. Thank you so much for posting TEN detailed tutorials. I couldn't have set up the data without them!

    I'm working on identifying the birth parents of a woman's father who was adopted at birth in 1908. NodeXL separated her DNA matches into 31 groups. Two are large, lots are medium, some are tiny. I guess I was expecting to see a big group for maternal, a big group for paternal, and then others related to one but not the other.

    Um, newp!

    The subject's mother's family tree is well sourced and built back a couple of centuries, and I have been able to place many of her DNA matches on it. They are all in the largest (dark blue) group, let's call it DB. The other large group is light blue (LB).

    There's a cluster of people who don't appear to be related at all to the mother, and they are ALSO in the DB group however they are in a separate section of it.

    Should I infer that they are linked through the mother somehow?

    I'm just not sure how to leverage all this great data and these connections... I feel like if I looked at it the right way I could extract some real gold nuggets!

    1. Hello Alexandra, first of all my apologies for taking so long to respond! Since blogger changed their settings some time ago it's harder to keep track of comments.
      It's also hard to say what the situation might be without looking at the chart - and even then I could only say what the situation MIGHT be. If the cluster in the DB group are strongly connected to the rest it's likely that they are linked through the mother somehow. If only a few lines, then it's just as likely to be a co-incidental secondary connection between groups that may have nothing to do with the focus person's ancestry.
      I hope that in the time since you posted you have discovered some of those nuggets!

  13. Is there anyone "out there" who has successfully used a Mac alternative to NodeXL, like gephi or Cytoscape to visualise their AncestryDNA matches? I don't know anything about either of these utilities (or NodeXL), but I looked up NodeXL alternatives for a Mac and found these. Thoughts?