How to make a simple chromosome browser chart

Below is a small section of the master spreadsheet I have been using to analyse my DNA match data. You will see I have added a column with a visual representation of the start and end points of the matching segments.  

I find the bars much easier to understand at a glance than the raw numbers.

It is surprisingly easy to create bars like these. You don’t need to do tricky things with charts or have the latest and greatest version of any particular software package. The key to creating a simple chromosome browser like the one above is the text function REPT.

REPT takes two inputs, a text string and the number of times the string is to be repeated.

= REPT(text, number_times)

If you were to enter =REPT(“a”,5) the cell would display “aaaaa”. If you repeat a block character like this █ 5 times you get a bar 5 characters long.

REPT(“█”, 5) = █████

If you want to do the same sort of thing, these instructions should get you started.

First, create a column for the formula. Highlight the new column and change the font to a fixed width font. I used Courier New because it is a standard font and I could go down to a font size of 8 without losing the fixed width property we need.

The formula you will enter has two parts – the empty space and the bar.

To make the empty space, simply repeat the space “ ” character by an amount proportional to the value in the start column.

I say “proportional to”, because if you use the start column as it is in this FTDNA file you could end up with a bar over 100 million characters long! I have found that dividing the start number by 3 million works nicely. You may need to experiment to suit your screen real estate and personal preferences.

= REPT(“ ”, Start/3,000,000)

Note: swap in the appropriate cell reference instead of “Start”

Add an ampersand “&” to the end of the formula to join on the next part.

= REPT(“ ”, Start/3,000,000)&

The final part of the formula is the bar. You can use whatever character you like for this. I have used a solid block █. You can find the same character on a PC by either copying and pasting using the character map or by entering Alt +2588. If that sounds too complicated any symbol you like the look of will work, so long as you are using a fixed width font.

The length of each bar is proportional to the ‘End’ column value minus the ‘Start’ column value. Divide the length you calculate by the same number as before.

= REPT(“ ”, Start/3,000,000)&REPT(“█”, (End – Start)/3,000,000)

Finally a fix because occasionally a shorter segment won’t show up.  I modified the second part of the formula to always show at least one block, like so:

= REPT(“ ”, Start/3,000,000)&REPT(“█”, MAX(1,(End – Start)/3,000,000))

There you have it – a simple chromosome browser. Enjoy!