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

This tutorial walks step-by-step through creating a five-generation pedigree family tree in Microsoft Excel.

A five-generation tree goes back to sixteen great-great-grandparents.

The challenge is how to get five generations to fit on a single printed page. The solution is to use the pedigree layout.

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

Table of Contents

What A 5-Generation Pedigree Family Tree Looks Like

Here is an example of how a five-generation pedigree family tree with photos can print in landscape mode:

Of course, you don’t need to include the photos.

You can keep referring back to this image as I walk you through the steps to create the layout.

If you’re thinking you’d like more or less generations, we have separate tutorials on:

Video Walkthrough

If you would like a video walkthrough alongside this tutorial, here you go:

Step 1: Create A Blank Worksheet With A Landscape Print Area

The tree must print on a single A4 sheet in landscape mode.

So, we want to see the boundaries as we’re adding elements to the worksheet.

The first thing to do is to show those boundaries while you’re working. That means you won’t step outside them.

Click on the File menu and choose the Print menu item. You’ll see that the default print mode is Portrait, which isn’t what we want.

Toggle the setting to Landscape mode and click the back arrow (top left).

You will now see dashed lines on your blank spreadsheet that mark the boundaries. If you’re working on a small screen, you may need to scroll right or down a bit to see the lines.

Step 2: Set The Column Widths For Five Generations

It took me a lot of experimentation to work out the best column widths to get a symmetric five generation pedigree tree on a single page.

We are going to work with columns A to K.

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.

Now you know how to do it, set these widths:

set columns A, G, and I to 7
set columns B, D, F, H, and K to 19
set columns C, E, and J to 1

Step 3: Create The Name/Date Area For The Home Person

I like to put photos of ancestors to the left of their names, so this layout leaves space for that.

Right now, we’re going to focus on creating a box 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:

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

Set the font type and size

Calibri is the default font in the current versions of Excel. Prior versions may use Arial as the default.

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. Microsoft provides several narrow fonts.

“Bahnschrift Condensed” is my favorite for the names. To set the font for the name:

Select cell B16.
Go to the Home tab in the top menu ribbon.
Change the font type to “Bahnschrift Condensed”.
Ensure the font size is 11.

I use the cell below the name for 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 “Bahnschrift Light Condensed”.

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 surname “Postlethwaitewigginbotham” will display as “Postlethwaitewig”.

You’ll find several “how-to” instructions online on how to do this. I find that several of these methods don’t always work.

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.

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

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 D8.
Expand the color pick tool in the Home menu.
Choose “Blue, accent 5”.

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

Select cell D9.
Expand the color pick tool in the Home menu.
Choose “Gray, accent 3”.

I also like the dates to be centered within the cell.

As we’re going to be copying this first ancestor to many others, it’s worth entering in some sample data to get the look that you want.

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 D8, E8, D9, and E9.

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

For the following insert areas, don’t worry that the two cells with a space are beyond the right-most print boundary. That’s okay.

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.

Select the four cells D8, E8, D9, and E9.
Insert into cell D24.

I like to set the background color for female names to “Orange, accent 2”.

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 D24, E24, D25, and E25.

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

Step 8: Check That The 5th Generation Fits In The Page

The great-grandparent generation is of course the column to the furthest right.

At this point, you should double-check that you haven’t spilled over landscape print boundary.

I find that the print area gridlines aren’t always correct when viewing a worksheet. However, the print preview matches the output.

Use the Print menu to see what you’ve got. Focus on the K column and be sure that the boxed border is entirely on the page.

If you see it spilling over, then your local machine is different from mine. Don’t worry!

All you have to do is to narrow the filler columns A, G, and I. Try reducing these three columns to 6.5 and check again.

Work down in 0.5 increments until everything fits on a single landscape page.

Now that we’ve got the ancestor boxes, the next steps are to add the connector lines.

About Creating Connector Lines In Excel

Most of the challenge is to overcome the finicky nature of drawing lines in Excel.

We’ve got some handy tips for you in the rest of this tutorial.

We’ll work from left to right, starting with the first-to-second generation.

Step 9: Create The First Vertical Line Above The Home Person

Create the first vertical line above the left-most box with these steps:

Go to the “Insert” tab in the top ribbon.
Expand the “Illustrations” drop-down.
Expand the “Shapes” option.
Choose the line without arrows.

Place your cursor into cell B8. Drag the cursor down a little to create the line.

Don’t worry about the placement or height. You just want a short straight vertical line in the general area.

Now you have the line, let’s format it correctly.

The default line is probably a light blue color. I prefer to change this to black.

Right-click the line and choose “Format Shape”.
Edit the “Color” pick to black.

Set the height to 3.5 cm

The next step is to set the height (length) of the vertical line.

I recommend that you don’t use the cursor to drag lines to an unknown height. Instead, use the precision of the height control with these steps:

Right-click the line and choose “Format Shape”.
Switch to the “Size and Properties” tab.
Edit the Height to 3.5 cm.

Tip: enter a temporary string of letters to fill the name field

You want to position the vertical line so that it intersects with the top of the name field (B16) about halfway along the cell.

My tip is to insert a temporary string of letters to fill the entire cell.

You can enter “Longfirstname Longlastname” for example. The box truncates the last letter if you are using the same font and size as mine.

Depending on the font type and size you’ve chosen, there will be about 23 characters displayed. You just need to position the line under the middle letter.

With our example, the middle is at about the letter “m” in “Longfirstname”.

Tip: use the positioning circle

To position the line, drag it to intersect with the top of cell B16.

When you drag and drop the line, you will see a small circle at the top and bottom of the line. You can use the circle to position the end of the line to intersect with the name box.

If you’re used to fancier graphic software, you may expect the line to snap into place. It doesn’t.

You may find that you’ve pushed it up too far so that the line runs into the name box. Here’s our next tip.

Tip: use arrow keys for precise positioning

If you’re as clumsy as me, then positioning the line can get frustrating.

Here’s my best tip: don’t use the mouse. Instead, use the arrows on your keyboard.

When the line is selected, a click on an arrow nudges the line slightly in the right direction.

Watch the circle as you nudge the arrow up or down. Click away to see whether you’ve hit the exact spot.

Excel, lines, and younger folk

This may all be too finicky for small people with school projects.

I suggest you place the arrows for them and leave them with the fun of adding images to the tree (final steps).

Step 10: Create The Second Vertical Line Under The Home Person

To add a vertical line of the same color and height, simply copy the one you’ve already created.

Repeat the positioning steps to center it under cell B17.

Step 11: Create The First Horizontal Line To The Father

Repeat the instructions in Step 9 to insert a line, but this time place it so that it is horizontal.

Again, don’t worry about the length or position at this point. But change the color to be black.

Use the gridline for the bottom of B8 to position the horizontal line. It should intersect with the top of the vertical line.

Once you have the left edge anchored, you can adjust the length using the width control.

Right-click the horizontal line and choose “Format Shape”.
Switch to the “Size and Properties” tab.
Use the “up” arrow in the Width control to nudge the length to the right.

If you’re using similar positions as me, the width will be about 2.2 cm.

Step 12: Create The 2^nd Horizontal Line To Mother

Copy the horizontal line that you’ve just sized and positioned.

Move it to lie along the gridline at the bottom of cell B24.

When you have the left edge intersecting with the bottom of the vertical line, the right edge should intersect with the mother’s bordered area.

Step 13: Create The First Set Of Lines From The 2^nd To 3^rd Generation

You should now add the lines from the father to the two paternal grandparents.

Create these lines using the same advice as I give above.

The main difference is that the vertical line is 1.5 cm.

Align the horizontal lines with the bottom gridline of cells D4 and D12.

You’ll be happy to hear that I now have a time-saving tip for you.

Step 14: Copy These Lines For The Mother To Maternal Grandparents

The tip I’ve got for you is that you don’t need to get finicky and select the lines you’ve created. Instead, you can simply copy entire cells.

Select the range from D4 and E4 down to D7 and E7. That is eight cells in total. By doing so, you’ve also selected the lines within this range.

Place your cursor into cell D20 and paste the range. Hey presto!

Now select the range from D10 and E10 down to D13 and E14.

Place your cursor into cell D26 and paste the range.

That’s it for the 2^nd to 3^rd generation!

Step 15: Create The First Set Of Lines From The 3^rd To 4^th Generations

Use the previous instructions to create the vertical and horizontal lines on top of cell F4 and below cell F5.

The vertical lines for this generation are shorter. They are about 0.5 cm.

Align the horizontal lines along the bottom of F2 and F6 respectively.

Step 16: Copy The Lines To The Three Other Grandparents

Select the range of four cells of F2, G2, F3, and G3.

Paste into these cells:

Select the range of four cells of F6, G6, F7, and G7.

Paste into these cells:

Step 17: Create The First Set Of Lines From The 4^th To 5th Generations

Use the previous instructions to create the vertical and horizontal lines on top of cell H2 and below cell H3.

The vertical lines for this generation are about 0.25 cm.

The horizontal lines should align along the middle of cells H1 and H4. Their length is about 3.6 cm.

Step 18: Copy The Lines To The Seven Other Great-Grandparents

Select the range of cells of H1, I1, and J1.

Paste into these cells:

Select the range of cells of H4, I4, and J4.

Paste into these cells:

And that’s it! Whew!

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 19: Preparing Images For Your Excel 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

If you don’t have a favorite editing app for cropping and sizing photos, I recommend the free version of Canva.

You just need to register an email at Canva.com.

Step 20: Add Images To The Tree

I’ve left plenty of space for images.

Repeat these steps for each image:

Put your cursor into the cell to the left of the appropriate ancestor box.
Go to the Insert tab in the ribbon.
Expand the Illustrations drop-down and choose “Pictures”.
Choose “from This Device”.
Find the correct image.

Excel plonks the image onto the spreadsheet. You can now adjust the position.

I recommend that you use the arrow keys to align the image beside the ancestor box.

Use the arrow keys to nudge the image left and right.

Adjusting For Minor Image Differences With The Printed Version

As you work with positioning your pictures in Excel, you’ll probably notice that there can be tiny discrepancies between how the layout looks in the spreadsheet versus how it looks when printed.

I find that images that seem aligned perfectly with the name box are slightly to the left or right in the printed material.

That may not bother you. But if you’re like me, it will annoy you.

Thankfully, the Print Preview is accurate to what is printed. You’ll see the discrepancies there, without having to use up your ink.

The trick is to toggle back and forth to the Print View while making tiny adjustments to the spreadsheet.

Get Our Done-For-You Bundle

Does this all seem like too much fiddling around? I’ve prepared a bundle of four formatted five-generation templates:

Classic template with name and date fields
Template with names, dates, and photo areas
Template with name fields and photo areas
Simplest template with name fields

All this for the price of a cup of quality coffee. Download from our Gumroad store here:

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

What A 5-Generation Pedigree Family Tree Looks Like

Video Walkthrough

Step 1: Create A Blank Worksheet With A Landscape Print Area

Step 2: Set The Column Widths For Five Generations