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

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

A six-generation tree goes back to thirty-two great-great-great-grandparents.

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

Even with the pedigree layout, it’s a bit of a squeeze. But after a lot of experimentation, I’ve come up with the optimum format to display and print clearly.

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

Table of Contents

What A 6-Generation Pedigree Tree Looks Like

Here is an example of how a six-generation pedigree family tree prints in landscape mode:

If you need to go back further, we have tutorials on:

But you may be thinking that six generations are too much for your research.

In that case, look at our tutorial for creating a five-generation pedigree tree in Excel. You may even want to go down a level to a four-generation family tree in Excel.

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.

Step 2: Set The Column Widths

We are going to work with columns A to J.

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 to 1
set columns B, C, D, F, H, and J to 17
set columns E, G and I to 3
set column K to 8

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

To start with, we’ll create the formatted area for the first person. The end result will be two cells that look like this:

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

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 follow these steps:

Select cell B17.
Set the font 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 “Fauntleroywickenham” will display as “Fauntleroywi”.

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

Click into cells C16 and C17 and enter a space.

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 and a light-orange for females.

To change the color of the name field:

Select cell B16.
Expand the color pick tool in the Home menu.
Choose “Blue, accent 1” or “Orange accent 2” (pictures below)

I like all the date fields to be set to a light gray. This will be the same across all the ancestors.

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

Format the text positioning

I prefer that the name field (B16) is left-justified while the dates are centered in cell B17.

You may have a different preference. I suggest you enter a sample name and dates and experiment with the look that you want.

As we’ll be using this first set of cells to copy elsewhere, it’s worth getting it right at this point.

Step 4: Create The First Paternal Ancestor (Father)

We will use copy-and-paste now to speed up the process.

The most important point here is to copy four cells, not two! We want to include the adjacent cells with the invisible space.

Select and copy the four cells B16, C16, B17, and C17.

Place your cursor into cell C8 and paste the cell range.

If your home person is colored light-orange, you simply have to change the color of the father to complete this step.

Set the color to light blue (“Blue, accent 1”). The instructions are in the previous step.

Step 5: Copy To Paternal Ancestors For The 2^nd-5^th Generations

The ancestor areas look the same in the columns for the 2^nd, 3^rd, 4^th, and 5^th generations. The exception is the 6th generation.

But we can use copy-and-paste to copy our first paternal ancestor to the next four generations.

Select and copy the four cells C8, D8, C9, and D9.

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

We’ll deal with the 6^th generation later.

Step 6: Create The First Maternal Ancestor (Mother)

Select and copy the four cells B16, C16, B17, and C17.

Place your cursor into cell C24 and paste the cell range.

If your home person is colored blue, then you need to change the background color of the mother’s name to light-orange.

In other words, change cell C24 to light-orange.

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 C24, D24, C25, and D25.

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

We’ll deal with the 6^th generation for both male and female ancestors now.

Step 8: Create The 6^th Generation

The great-great-grandparent generation has a different format to the others.

In order to fit the 32 people onto the page, I can only give a single row to each. That means that the date field is in the same row.

In other words, the first two great-great-grandparents look like this:

The first thing is to set the alternating colors of the name column.

Set the color of cell J1 to light blue.
Copy the color to every second cell i.e. J3, J5, J7, J9 etc. until J31.
Set the color of cell J2 to light orange.
Copy the color to every second cell i.e. J4, J6, J8, J10 etc. until J32.

The next thing is to set the color of each date field.

Set the color of cell K1 to light gray.
Copy the color to all cells from K2 to K32.

And that’s it! We’ve got all our name and date fields.

The next half of this tutorial will create the connector lines.

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

The first two set of lines will look like this:

This step creates the vertical line.

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.9 cm

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

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.9 cm.

Now position the line so the bottom intersects with the top of the name cell. Here are some tips:

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 five letters 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 the letter “m” in “Longfirstname”.

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.

Step 10: Create The 2nd 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 1.3 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 his two parents.

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

The main difference is that the two vertical lines are 1.6 cm.

Align the horizontal lines with the bottom gridline of cells C4 and C12.

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 copy individual lines from place to place.

You can copy entire cells instead. This brings the multiple lines that they enclose.

Select the range from C4 down to C7. That is four cells in total. By doing so, you’ve also selected the two lines within this range.

Place your cursor into cell C20 and paste the range.

Now select the range from C10 down to C13.

Place your cursor into cell C26 and paste the range.

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

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

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

Use the instructions in step 13 to create the vertical and horizontal lines on top of cell D4 and below cell D5.

Align the horizontal lines along the bottom of D2 and D6 respectively.

Step 16: Copy The Lines To The Three Other Grandparents

Select the range of four cells of D2, E2, D3, and E3.

Paste into these cells:

Select the range of four cells of D6, E6, D7, and E7.

Paste into these cells:

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

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

The vertical lines for this generation are about 0.3 cm.

The horizontal lines should align along the middle of cells F1 and F4. Their length is about 2 cm.

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

Select the range of cells of F1 and G1.

Paste into these cells:

Select the range of cells of F4 and G4.

Paste into these cells:

Step 19: Create The First Set Of Lines From The 5^th To 6^th Generation

This set of lines looks different to the others because of the challenge of space.

These are the instructions with sizes that are for the end result. But read the cheat mode after these instructions before you get going.

Insert a horizontal line that runs along the bottom of cell I1.
Set the width to 0.5 cm.
Insert a vertical line that intersects with the right edge of the horizontal line.
Set the width to 0.5 cm.
Insert a vertical line of 0.2 cm that intersects with the top of the vertical line.
Insert a vertical line of 0.2 cm that intersects with the bottom of the vertical line.

Cheat mode

I found it difficult to work with these very short lines of 0.2 cm and 0.5 cm. Even selecting the 0.2 cm line to position it was a challenge.

So, I worked in a separate area and created the four lines in the correct alignment but at twice those sizes. Then I used the height and width controls to shorten them.

Once I had the shorter lines all intersecting, I copied the lines into the correct place.

Step 20: Copy The Lines To The Other Great-Great-Grandparents

Copy the cells I1 and I2.

Paste into these cells:

Step 21: Finalize The Home Person

Finally, we may need to complete the Home Person.

I had you set the colors first to light blue and then to light orange.

If the Home Person (cell B16) is male, then set the color back to light blue.

Get Our “Done For You” Bundle

Does this all seem like too much fiddling around? I’ve prepared a bundle of four formatted six-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 6-Generation Pedigree Family Tree In Excel (Tutorial)

What A 6-Generation Pedigree Tree Looks Like

Video Walkthrough

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

Step 2: Set The Column Widths