Create A Large Ten-Generation Family Tree In Google Sheets

This tutorial shows you how to create a very large family tree in Google Sheets.

A 10-generation tree goes back to 512 great-great-great-great-great-great-great-grandparents.

This version prints across three A4 portrait pages wide and eight pages deep.

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

What A 10-Generation Pedigree Tree Looks Like

The picture below is a zoomed out look at just an upper section of the tree.

This structure is repeated many times down the tree.

We’ll use this repetition to our advantage. I’ll show you how to maximize cut-and-paste to cut down your work.

Looking for a smaller tree?

If you’d prefer one that’s half the size, we have a guide to creating a five-generation family tree in Google Sheets.

Step 1: Set Column Widths And Row Heights

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 “Resize column” from the drop-down menu.
3. Enter the sizes below (this is in pixels).

Set these column sizes:

• Set column A to size 30.
• Set columns B, E, H, K, N, Q, T, W, Y (nine columns) to size 159.
• Set columns C, F, I, L, O, R, U to 23.
• Set column D, G, J, M, P, S, V to size 12.

Set these row heights:

The spreadsheet needs at least 512 rows which makes this a long tree.

I want to use the least amounts of printed pages, so I reduce the height of the rows from the default.

1. Select rows 1 to 600.
2. Right-click and choose “Resize row” from the drop-down menu.
3. Enter size 15.

Step 2: Start With The First Great-Grandfather Name Field

We start with the section of the tree for the first 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 cells

1. Select cells H32 and H33.
2. Expand the Format menu in the top ribbon and choose “Merge cells”.
3. Choose “Merge all”.

Add an outside border

1. Select the merged cells.
2. Expand the Borders menu in the top ribbon.
3. Choose the “Outer Borders” option.

Set the font type, size, alignment, and color

I like to use a narrow font type that first more letters into the cell. This ensures that longer names display in full. My preference is “Archivo Narrow”.

1. Select the merged cells.
2. Expand the font list and choose “More fonts”.
3. Change the font type to “Archivo Narrow”.
4. Leave the font size at 10.

Set the text alignment

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

Cells are left-justified by default so all you need to set is the vertical alignment.

1. Select the merged cells.
2. Expand the vertical alignment dropdown.
3. Set to the middle option.

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.

1. Select cell H32 and H33.
2. Expand the fill color tool in the ribbon.
3. Choose “light blue 3”.

Step 3: Copy For Male Ancestors From The 5^th To 9^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 H32 and H33.

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

5^th Generation

• K16

6^th Generation

• H8
• H40

7^th Generation

• Q4
• Q20
• Q36
• Q52

8^th Generation

• T2
• T10
• T18
• T26
• T34
• T42
• T50
• T58

9^th Generation

• W1
• W5
• W9
• W13
• W17
• W21
• W25
• W29
• W33
• W37
• W41
• W45
• W49
• W53
• W57
• W61

We’ll deal with the tenth generation later.

Step 4: 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 H32 and H33.

Paste into cell K48.

Change the background color to light orange.

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

Select and copy the merged cells K48 and K49.

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

6^th Generation

• N24
• N56

 7^th Generation

• Q12
• Q28
• Q44
• Q60

8^th Generation

• T6
• T14
• T22
• T30
• T38
• T46
• T54
• T62

9^th Generation

• W3
• W7
• W11
• W15
• W19
• W23
• W27
• W31
• W35
• W39
• W43
• W47
• W51
• W55
• W59
• W63

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

Step 6: Create The 10^th 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 Y1 to light blue.
2. Put an outside border around Y1.
3. Set the color of cell Y2 to light orange.
4. Put an outside border around Y2.

Now copy these two cells down as far as Y64 is filled with an orange cell.

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

A Note On Connector Lines In Google Sheets

You can insert lines in Google Sheets and position them on the page. That’s how I prepare trees in Microsoft Excel.

But I find that lines in Google Sheets are very difficult to size precisely. The feature seems to be half-baked.

So, I use a trick to simulate lines. Instead of drawing the shape, I use cell border formatting instead.

You’ll see what I mean as you follow the next set of instructions.

Step 7: Create The Connector Lines From The Great-Grandfather To His Parents

Create a vertical line using borders:

1. Select cells J18 to J49.
2. Place a left border along these cells.

Create three horizontal lines using borders:

• Select cell J17 only and place a top border on this cell.
• Select cell J48 and place a bottom border on this cell.
• Select cell I32 and place a bottom border on this cell.

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

Create the first set of connector lines:

1. Place a left border along cells M9 to M24.
2. Place a top border on cell M9.
3. Place a bottom border on cell M24.
4. Place a bottom border on cell L16.

Now we have a set of connector lines around one set of ancestors, we can copy the cells down to the other ancestors in this generation.

In other words, follow these steps:

1. Select and copy the range of cells from L9 and M9 down to L24 and M24.
2. Paste into cell L41.

It’s that easy! We’ll be using that copy-and-paste trick a lot to complete out this layout.

Step 9: Create The Connector Lines From The 6^th To 7^th Generation

1. Place a left border along cells P5 to P12.
2. Place a top border on cell P5.
3. Place a bottom border on cell P12.
4. Place a bottom border on cell O8.

Now copy these bordered cells to the other ancestors in this generation.

1. Copy the range of eight cells from O5 and P5 to O12 and P12.
2. Paste into cell O21.
3. Paste into cell O37.
4. Paste into cell O53.

Step 10: Create The Connector Lines From The 7^th To 8^th Generation

1. Place a left border along cells S3 to S6.
2. Place a top border on cell S3.
3. Place a bottom border on cell S6.
4. Place a bottom border on cell R4.

Now copy these bordered cells to the other ancestors in this generation.

1. Copy the range of eight cells from R3 and S3 to R6 and S6.
2. Paste into cell R11.
3. Paste into cell R19.
4. Paste into cell R27.
5. Paste into cell R35.
6. Paste into cell R43.
7. Paste into cell R51.
8. Paste into cell R59.

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

1. Place a left and top border on cell V2.
2. Place a left and bottom border on cell V3.
3. Place a bottom border on cell U2.

Now copy these bordered cells to the other ancestors in this generation.

1. Copy the range of four cells U2 and V2 to U3 and V3.
2. Paste into cell U6.
3. Paste into cell U10.
4. Paste into cell U14.
5. Paste into cell U18.
6. Paste into cell U22.
7. Paste into cell U26.
8. Paste into cell U30.
9. Paste into cell U34.
10. Paste into cell U38.
11. Paste into cell U42.
12. Paste into cell U46.
13. Paste into cell U50.
14. Paste into cell U54.
15. Paste into cell U58.
16. Paste into cell U62.

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

This connection is just a single horizontal line.

1. Put a bottom border on cell X1.
2. Copy the two-cell range of X1 and X2.
3. Paste the cells all the way down to X63.

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 H1 to Z1 to H64 to Z64.
2. Paste this range into cell H66.
3. Change the fill color of the merged cells H97 and H98 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 H32 and H33.
2. Paste into cell E64.

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.

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

1. Place a left border along cells G33 to G97.
2. Place a top border on cell G33.
3. Place a bottom border on cell G97.
4. Place a bottom border on cell F64.

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 E1 to Z1 to E129 to Z129.
2. Paste this range into cell E131.
3. Change the color of merged cells E194 and E195 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 E65 and E66.
2. Paste into cell B132.

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

1. Place a left border along cells D65 to D194.
2. Place a top border on cell D65.
3. Place a bottom border on cell D194.
4. Place a bottom border on cell C129.

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 B1 to Z1 to B259 to Z259.
2. Paste this range into cell B261.
3. Change the color of merged cells B389 and B39 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 A259, B259, A260, and B260.
2. Set your preferred background color.
3. Add a border around the cells.
4. Set the vertical alignment to middle.

Add the connector lines to the parents:

1. Place a left border along cells B131 to B258.
2. Place a left border along cells B261 to B388.

And that’s it! Well done for getting this far!

Step 19: Print Settings

When you print your ten-generation spreadsheet, use these settings:

• Paper Size: A4
• Page Orientation: Portrait
• Show gridlines: unchecked

Here’s a picture of this set-up:

Done-For-You Bundle

If you want a short-cut, we have pre-made templates in Google Sheets. Everything is laid out perfectly, you just need to fill it in!

We have two versions in the spreadsheet package:

1. The classic template with names and dates.
2. The simpler template that shows names only.