How To Create A Five-Generation Family Tree In Google Sheets

This is a step-by-step guide to creating a family tree in Google Sheets.

This tree goes back five generations to great-great-grandparents. It fits on a single printed page.

You can leave out the last level for a four-generation tree.

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

Google Sheets Family Tree

Here is what the tree looks like in Google Sheets:

I’ve left enough space in the template for a photo to the left of every person.

The individual entries look like this:

If you want one more generation that still fits on a single page, check out our guide on creating a six-generation Google Sheets family tree.

If you want one less, then here is our four generation Google Sheets template.

Looking for a much bigger tree?

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

In between those two sizes, we also cover:

Video Walkthrough

If you prefer a video walkthrough, click on the play button:

Step 1: Create A Blank Online Spreadsheet

I’m assuming that you have a Google account. If not, sign up – it’s free.

Create a blank spreadsheet at this link.

By default, a new sheet prints as an A4 sheet in landscape mode. That’s exactly what we want.

Step 2: Set The Column Widths

It took a few tries to work out the best column widths to get the tree to print on a single page.

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

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

To set the width of multiple columns together, hold down the Ctrl key while clicking the column letters.

Now you know how to do it, set these widths (in pixels):

set column A to 53
set columns B, E, H, K, and N to 144
set columns C, F, I, and L to 45
set columns D, G, J, and M to 8

Step 3: Create The Box For The Home Person

We’ll create a box to hold the name and dates for the first person.

We only need to do this once. Then we can copy the formatted cells for the other thirty people.

Add an outside border

Place a border around cells B16 and B17 with these steps:

Select cells B16 and B17.
Expand the Borders menu in the top ribbon.
Choose the “Outer Borders” option.

Set the font type and size

Arial is the default font in Google Sheets.

I find that this font is too wide to ensure that most longer names fit into the box.

My preference is to use a narrower font. These are some good choices:

Oswald (this one is in the default list)
Barlow Condensed (click on the “more fonts” option to see this one)
Archivo Narrow

Test several to see which one you find most legible at a size of 10. “Archivo Narrow” is my favorite here.

Select cell B16.
Go to the Home tab in the top menu ribbon.
Change the font type to “Archivo Narrow” (or your preference).
Ensure the font size is 10.

Format the date cell

The cell beneath the name holds the dates e.g. “1923 – 1980”.

I like to have a visual difference between the name and date font.

So, I set the font for cell B17 to “Barlow Condensed”. It’s a little narrower and lighter than Archivo Narrow.

I also like the date cell to be centered. So, set the horizontal alignment to Center.

Format the adjacent cells to stop overspilling

We want to ensure that a long name doesn’t spill into the next cell. The display should truncate the name to the width that we specified.

In other words, the name “Archibald Thoroughoodlivingstone” will display as “Archibald Thoroughoodliving”.

The guaranteed way is to add a space into the cell beside it.

In other words, click into cells C16 and C17 and enter a space.

Step 4: Create The First Paternal Ancestor

The first paternal ancestor is the father.

Thankfully, we don’t have to repeat the steps above. We simply use copy-and-paste with one extra step.

When you copy the cells, don’t forget to include the ones with the space.

Select and copy the four cells B16, C16, B17, and C17.
Paste into cell E8.

Unless you entered a sample name into the first name area, all you’ll see now are the bordered cells.

The next step is to set the background color(s) for a male ancestor. I like to use a light-blue.

To change the color of the name field:

Select cell E8.
Expand the fill color tool in the ribbon.
Choose “light blue 3”.

I like to use a light gray shade for the date field below the name.

Select cell E9.
Expand the color pick tool in the Home menu.
Choose “light gray 3”.

Step 5: Create The Other Male Ancestors

Now that we’ve got the colors set up, we can copy the newly formatted four cells to create the rest of the male ancestors.

Select and copy the four cells E8, F8, E9, and F9.

Paste into the cells below, working through them one by one:

Step 6: Create The First Maternal Ancestor

We are now going to create the area for the mother.

In summary, we’ll copy the four cells for the father to the mother area and change the background color.

Copy the four cells E8, F8, E9, and F9.
Paste into cell E24.

I like to set the background color for female names to “light orange 3”.

I keep the date field the same color as for the male box.

Step 7: Create The Other Female Ancestors

Now that we’ve got the colors set up, we can copy the newly formatted four cells to create the rest of the female ancestors.

Select and copy the four cells E24, F24, E25, and F25.

Paste into the cells below, working through them one by one:

Step 8: Apply The Color Formatting To The Home Person

We didn’t apply any color formatting to the home person in cells B16 and B17.

That’s because I don’t know if you’re working with a male or female person.

Follow the steps I’ve outlined to set the name and date colors appropriately.

Once that’s done, the next steps are to add the connector lines.

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 9: Create The Connector Lines For The Home Person

Create a vertical line using borders:

Select cells D9 to D24.
Place a left border along these cells.

Create three horizontal lines using borders:

Select cell D9 only and place a top border on this cell.
Select cell D24 and place a bottom border on this cell.
Select cell C16 and place a bottom border on this cell.

Step 10: Create The Connector Lines From The 2^nd To 3^rd Generation

Create the first set of connector lines:

Place a left border along cells G5 to G12.
Place a top border on cell G5.
Place a bottom border on cell G12.
Place a bottom border on cell F8.

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:

Select and copy the range of cells from F5 and G5 down to F12 and G12.
Paste into cell F21.

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

Step 12: Create The Connector Lines From The 3^rd To 4^th Generation

Place a left border along cells J3 to J6.
Place a top border on cell H3.
Place a bottom border on cell J6.
Place a bottom border on cell I4.

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

Copy the range of eight cells from I3 and J3 to I6 and J6.
Paste into cell I11.
Paste into cell I19.
Paste into cell I27.

Step 13: Create The Connector Lines From The 4^th To 5^th Generation

Place a left border along cells M2 to M3.
Place a top border on cell M2.
Place a bottom border on cell M3.
Place a bottom border on cell L2.

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

Copy cells L2, M2, L3, and M3.
Paste into cell L6.
Paste into cell L10
Paste into cell L14
Paste into cell L18
Paste into cell L22
Paste into cell L26
Paste into cell L30

And that’s it! You now have the pedigree five-generation family tree structure in Excel.

You can stop and work with what you’ve got i.e. add the correct names and print out the sheet.

But if you want some photos in there, let’s keep going.

Step 14: Preparing Images For The Tree

In our example tree at the top of this article, we have cropped images with faces to the left of the name and date boxes.

Because the spreadsheet images must be small, you probably have some preparation and editing to do to get similar images from your family photos.

My advice is to prepare images in either of these size units:

38 X 38 pixels
1 cm X 1 cm

Step 15: Add Images To The Tree

I’ll just come right and out say this: Google Sheets does not do a good job of letting us align images on the page. Microsoft Excel is better.

But if you really want photos on your tree, here are the steps.

Put your cursor into a cell to the left of the appropriate ancestor box.
Go to the Insert tab in the ribbon.
Expand the Image drop-down and choose “Insert an image over the cells”.
Grab the image.
Find the correct image.

The image will appear on the sheet but probably not in the correct place.

Use the arrow keys to nudge it to where it should be.

Do you find that the same size of image aligns perfectly beside one ancestor but not another? Yes, it’s annoying.

Printing Your Tree

When you print your tree, be sure to turn off the grid-lines.

Done-For-You Bundle

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

We have two versions in the spreadsheet package:

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