Create A Nine-Generation Pedigree Family Tree In Excel (Tutorial) – Data Mining DNA (2024)

A nine-generation family tree goes back to 256 great-great-great-great-great-great-grandparents.

This tutorial shows you how to create this large tree in Microsoft Excel. Our version prints across twelve portrait pages arranged as three pages wide and four pages deep.

The printed pages can be easily taped together to put up on a wall as a single display.

If you’re too busy for the fifteen steps in this tutorial,jump down to the endtograbour“done for you” Excel template bundle.

What A 9-Generation Pedigree Tree Looks Like

The picture below shows what the top of the tree looks like. This has the full set of ancestors for your father.

The tree is laid out in pedigree format. In other words, it’s horizontal.

Do you need one more set of ancestors? Or one less? Check out these tutorials:

• create a ten-generation family tree in Excel
• create an eight-generation family tree in Excel

Step 1: Create A Blank Worksheet And Set The Column Widths

Create a new worksheet.

We are going to work with columns A to K.

To change the width of any column, follow these steps:

1. Select the entire column by clicking on the letter at the top.
2. Right-click and choose “Column Width” from the drop-down menu.
3. Enter a size.

Set these sizes:

• Set column A to size 1.
• Set columns B to I (eight columns) to size 21.
• Set column J to size 3.
• Set column K to size 21.

Step 2: Set The Row Heights

We will work with rows 1 to 266.

To fit nine generations onto the page, we need at least 256 rows – one for each 7^th great-grandparent.

The default row height in Excel only fits 33 rows. Obviously, we need to reduce the row height. The challenge is to be sure that the text is still legible.

I find that a row height of 11.5 is the best size.

1. Select the entire sheet using ctrl-A.
2. Right-click anywhere on the sheet.
3. Choose “Row Height” from the menu.
4. Enter 11.5 as the height.

Step 3: Start With One Grandfather Name Field

We are going to fill out the section of the tree for the paternal grandfather.

Then we can save a huge amount of time by copying this section three times.

Follow these steps to create the field for the grandfather.

Merge two sets of cells

1. Select cells D33 and D34.
2. Right-click and choose “Format cells”.
3. Switch to the Alignment tab.
4. Check the “Merge cells” box.

Add an outside border

Place a border around the merged cells with these steps:

1. Right-click and choose “Format Cells” from the drop-down menu.
2. Choose the Border tab.
3. Choose the “Outline” preset border.

Set the font type and size of the merged cells

In order to fit names into this small row height, we need to set the font size to 8.

But the default font of Calibri or Arial doesn’t look great at small sizes. I find that Segoe UI displays and prints well at that size.

1. Select the merged cells.
2. Go to the Home tab in the top menu ribbon.
3. Change the font type to “Segoe UI”.
4. Set the font size to 8.

Set the text alignment

I prefer names to be left-justified and vertically centered in the merged cells.

1. Select the merged cells.
2. Set the vertical alignment to Middle Align.
3. Set the horizontal alignment to Left.

Here is a picture of these alignment choices:

Format the coloring

I like to use a different color in the name field for male and female ancestors. My preference is a light blue for males.

To change the color of the name field:

1. Select the merged cells.
2. Set the background color to light blue.

Step 4: Copy For Male Ancestors From The 4^rd To 8^th Generations

We are going to use copy-and-paste as much as possible in this process!

I’ve already worked out where each ancestor should go. You’re welcome.

Select and copy the two merged cells D33 and D34.

Select each cell below in turn and paste the selected range into the cell. This copies the two cells.

4^th Generation

• E17

5^th Generation

• F9
• F41

6^th Generation

• G5
• G21
• G37
• G53

7^th Generation

• H3
• H11
• H19
• H27
• H35
• H43
• H51
• H59

8^th Generation

• I2
• I6
• I10
• I14
• I18
• I22
• I26
• I30
• I34
• I38
• I42
• I46
• I50
• I54
• I58
• I62

We’ll deal with the ninth generation later.

Step 5: Create The Paternal Grandmother

This is the first female box on the page.

The steps are the same as for the grandfather, except for the position and background color.

Select and copy the two merged cells D33 and D34.

Paste into cell E49.

Change the background color to light orange.

Step 6: Copy For Maternal Ancestors In The 5^th To 8th Generations

Select and copy the merged cells E49 and E50.

Paste the range into the cells listed below. This copies the two merged cells.

5^th Generation

• F25
• F57

6^th Generation

• G13
• G29
• G45
• G33
• G61

7^th Generation

• H7
• H15
• H23
• H31
• H39
• H47
• H55
• H63

8^th Generation

• I4
• I8
• I12
• I16
• I20
• I24
• I28
• I32
• I36
• I40
• I44
• I48
• I52
• I56
• I60
• I64

We’ll deal with the 9^th generation next.

Step 7: Create The 9^th Generation

This furthest generation has a different format from the others.

In order to fit the 256 people onto four portrait pages deep, I can only give a single row to each.

We’ll format the first male and female boxes. Then we can copy them down the column.

1. Set the color of cell K2 to light blue.
2. Put an outside border around K2.
3. Set the color of cell K3 to light orange.
4. Put an outside border around K3.

Now copy these two cells down as far as K65 is filled with a pink cell.

Now we’ll create the connector lines for this quarter of the tree.

Adding, Positioning, And Sizing Lines In Excel

If you haven’t worked with lines in Excel, here is how to add one to the sheet:

1. Go to the “Insert” tab in the top ribbon.
2. Expand the “Illustrations” drop-down.
3. Expand the “Shapes” option.
4. Choose the line without arrows.
5. Place your cursor into a cell roughly in the area of where you want it.
6. Drag the cursor horizontally or vertically to create a line.

Changing the color to black

1. Right-click the line and choose “Format Shape”.
2. Change the color from blue to black.

Changing the length of the line

I recommend that you don’t lengthen or shorten lines by dragging the edges. It’s very finicky to get right.

Instead, I’ll give you the exact dimensions to enter into the height and width controls.

1. Right-click the line and choose “Format Shape”.
2. Switch to the “Size and Properties” tab.
3. Use the height property to edit the length of vertical lines.
4. Use the width property to edit the length of horizontal lines.

You can use the arrows to increase or decrease the size.

If you need to enter a size like “2.5 cm”, then you can input it directly into the box.

Use arrows for precise positioning

Once you’ve added the line anywhere on the sheet, you can drag it into the general area where it should be.

You can then use the arrow keys on your keyboard to nudge the lines into the precise position.

Step 8: Create The Connector Lines From The Grandfather To His Parents

The configuration looks like this:

The vertical lines above and below the name box are sized at 5.9 cm.

Two short horizontal lines connect the ends to the edge of the parent boxes. The horizontal lines are sized 1.5 cm.

Step 9: Create The Connector Lines From The4^th To 5^th Generations

There are two sets of connector lines involved here but you just need to position the first set.

I’ll give you a great tip for copying them down to the other ancestors of this generation.

Create and position the vertical and horizontal line above the top blue great-grandfather box:

• Vertical lines: 2.71 cm
• Horizontal lines: 1.5 cm

Repeat to position a vertical and horizontal line below the blue grandfather box.

Fast-Copy For the Other Ancestors In This Generation

You’ll be pleased to hear that you don’t have to copy individual lines to finish out this generation’s connector lines.

Instead of copying lines, we simply copy the range of cells that surround the lines. That’s much easier.

1. Select and copy the cell range from E9 to E16.
2. Paste into cell E41.
3. Select and copy the cell range from E19 to E26.
4. Paste into cell E51.

That’s it for this generation.

Step 10: Create The Connector Lines From The 5th To 8th Generations

You now know how to position and copy lines.

So, I’m going to focus on the heights and widths.

All the horizontal lines are 1.5 cm long.

The vertical lines differ by generation:

• 5^th – 6th generation: 2.04 cm
• 6^th – 7th generation: 1.7 cm
• 7^th – 8th generation: 1.7 cm

Step 11: Create The Connector Lines From The 8^th To 9^th Generation

This can be either very simple or a bit tricky.

If you want to take the simple route, use a single horizontal line that connects the child and parent.

I prefer to create a fork.

The challenge is that the four lines are very small and a bit finicky to move into position.

The left horizontal line (the handle of the fork) is 0.4 cm.

The two horizontal lines (the tines of the fork) are 0.3 cm.

The vertical line is 0.4 cm.

Once you have them positioned in the fork structure in cells J2 and J3, copy and paste the cells all the way down.

Step 12: Copy The First Grandfather Section To The First Grandmother Section

You’ll notice that we will leave some rows blank for this layout.

This is to ensure that the name boxes don’t span across page boundaries. This makes it easier when taping pages together.

Take these steps to create the maternal section below what we’ve already got.

1. Copy the range of cells from D2 to K2 to D65 to K65.
2. Paste this range into cell D69.
3. Change the fill color of the merged cells D100 and D101 to light orange.

Step 13: Copy The Two Paternal Grandparents To Maternal Grandparents

We now have the top two grandparents. We’re going to copy the lot to create the second set of grandparents.

1. Copy the range of cells from D2 to K2 to D132 to K132.
2. Paste this range into cell D136.

Now we have all the ancestors from the 3^rd to 9^th generations.

Step 14: Create The Parent Boxes

Create the father’s name box with these steps:

1. Copy the merged cells B32 and B3.
2. Paste into cell C65.

Create the mother’s name box with these steps:

1. Copy the merged cells D100 and D101.
2. Paste into cell C199.

Create the connector lines from them to their parents:

• Vertical lines: 13.1 cm
• Horizontal lines: 1.5 cm

Step 15: Create The Home Person

The last step is to add yourself (or whomever the home person is).

1. Copy any of the merged cells.
2. Paste the copied cells into cell B132.
3. Set your preferred background color.

You may want to use the blue or orange colors depending on whether this is a male or female. Or you may want to go with a completely different color.

Add the connector lines to the parents:

• Top vertical line: 27.9 cm
• Bottom vertical line: 27.9 cm
• Horizontal lines: 1.5 cm

Done-For-You Bundle

I’ve had a lot of practice, and it takes me a few hours to complete the spreadsheet. If you want a short-cut, we have prepared four different nine-generation templates.

Two templates have the classic pedigree layout. One looks like the version in this tutorial, while the other has placeholders for photos beside the names.

The other two templates have a split format. The father’s pedigree is on the left, and the mother’s is on the right. One version has photo placeholders, and the other looks like this:

You can grab them all for the price of one good cup of coffee. Download from our Gumroad store here.