Create A Ten-Generation Pedigree Family Tree In Excel (Tutorial)

A ten-generation family tree goes back to 512 great-great-great-great-great-great-great-grandparents.

This tutorial shows you how to create this very large tree in Microsoft Excel.

Our version prints across twenty-four portrait pages arranged as three pages wide and eight pages deep.

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

If you’re too busy for the eighteen steps in this tutorial, jump down to the end to grab our “done for you” Excel template bundle.

What A 10-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 the paternal grandfather:

Are ten generations too much? Check out these tutorials:

Video Walkthrough

If you prefer a video walkthrough, here you go (click on the play button):

YouTube player

Step 1: Create A Worksheet And Set The Column Widths

Create a new worksheet.

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 4.
  • 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 534.

To fit ten generations on this spreadsheet, we need at least 512 rows – one for each 8th great-grandparent.

The default row height in Excel only fits 33 rows. If we keep that default, we’ll have a very long print-out. Instead, I reduce the row height while still ensuring that the text is 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 The First Great-Grandfather Name Field

We are going to fill out the section of the tree for one great-grandfather.

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

Follow these steps to create the field for the great-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.

Step 4: Set The Font Type, Size, Alignment, And Color

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 5: Copy For Male Ancestors From The 5th To 9th 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.

5th Generation

  • E17

6th Generation

  • F9
  • F41

7th Generation

  • G5
  • G21
  • G37
  • G53

8th Generation

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

9th Generation

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

We’ll deal with the tenth generation later.

Step 6: Create The First Great-Grandmother

This is the first female box on the page.

The steps are the same as for the great-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 7: Copy For Maternal Ancestors In The 6th To 9th Generations

Select and copy the merged cells E49 and E50.

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

6th Generation

  • F25
  • F57

 7th Generation

  • G13
  • G29
  • G45
  • G61

8th Generation

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

9th Generation

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

We’ll deal with the 10th generation next.

Step 8: Create The 10th Generation

This furthest generation has a different format from the others.

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

That means that the display looks a little squeezed for this generation. But it’s still clearly legible.

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 9: Create The Connector Lines From The Great-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 10: Create The Connector Lines From The 5th To 6th 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 11: Create The Connector Lines From The 6th To 9th 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:

  • 6th – 7th generation: 1.16 cm
  • 7th – 8th generation: 0.4 cm
  • 8th – 9th generation: 0.2 cm

Step 12: Create The Connector Lines From The 9th To 10th Generation

This can be either very simple or a little more finicky.

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

I prefer the fork format in the right of the picture above.

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

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

Whichever format you choose, create the first connection. Then copy the cells all the way down.

Step 13: Copy The First Great-Grandfather To The First Great-Grandmother Section

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 14: Create The First Grandfather Name Box And Connector Lines

Take these steps to create the grandfather box:

  1. Copy the merged cells D33 and D34.
  2. Paste into cell C65.

The name boxes aren’t perfectly symmetric in our layout. That’s because I don’t want the merged name boxes to be too close to a top or bottom page boundary.

That makes it easier to tape the pages together.

The asymmetry means that the vertical connector lines above and below the grandfather are different lengths.

Take these steps to create the connector lines from the grandfather to his parents.

  • Top vertical line: 12.28 cm
  • Bottom vertical line: 13.5 cm
  • Horizontal lines: 1.5 cm

Step 15: Copy To Create The Paternal Grandmother Sections

We now have the paternal grandfather and his ancestors.

We’re going to copy the lot to create the paternal grandmother section.

  1. Copy the range of cells from C2 to K2 to C132 to K132.
  2. Paste this range into cell C136.
  3. Change the color of merged cells C199 and C200 to light orange.

Step 16: Create The Father’s Name Box And Connector Lines

Create the father’s name box with these steps:

  1. Copy the merged cells C65 and C66.
  2. Paste into cell B132.

Take these steps to create the connector lines from the father to his parents.

  • Top vertical line: 26.18 cm
  • Bottom vertical line: 26.2 cm
  • Horizontal lines: 1.5 cm

Step 17: Copy To Create The Mother And Her Ancestors

We now have the father and his ancestors.

We’re going to copy the lot to create the maternal pedigree lines.

  1. Copy the range of cells from B2 to K2 to B266 to K266.
  2. Paste this range into cell B270.
  3. Change the color of merged cells B400 and B401 to light orange.

Step 18: Create The Home Person

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

  1. Merge the four cells A267, B267, A268, and B268.
  2. Set your preferred background color.

Add the connector lines to the parents:

  • Top vertical line: 53.1 cm
  • Bottom vertical line: 52.4 cm
  • Horizontal lines: 0.4 cm

Done-For-You Bundle

 If you want a short-cut, we have prepared four different ten-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. Here is how the top of this tree looks:

The other template in this format has placeholders for photos.

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

Margaret created a family tree on a genealogy website in 2012. She purchased her first DNA kit in 2017. She created this website to share insights and how-to guides on DNA, genealogy, and family research.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.