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

A seven-generation family tree goes back to sixty-four great-great-great-great-grandparents. Can you really squeeze it onto a single printed sheet in Excel?

Yes you can! After a lot of experimentation, I’ve come up with the best format that displays and prints 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.

What A 7-Generation Pedigree Tree Looks Like

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

You may be thinking at this point that this goes back further than you need. In that case, check out these tutorials:

• how to create a six-generation pedigree tree in Excel
• how to create a five-generation pedigree tree in Excel

But what if it doesn’t go back far enough? Check this out:

• how to create an eight-generation pedigree tree in Excel

Video Walkthrough

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

Step 1: Create A Blank Worksheet That Prints In Landscape

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

So, you want to see the boundaries as we’re building out the worksheet. That means you won’t step outside them.

1. Click on the File menu in the top ribbon.
2. Choose the Print menu item.
3. Toggle the print mode to “landscape orientation”.
4. 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 I.

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.

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

• set column A to 3
• set columns B, C, D, E, F, and H to 17
• set column G to 2
• set column I to 9

Step 3: Set The Row Heights

We will work with rows 1 to 64.

To fit seven generations onto the page, we need 64 rows – one for each 5^th great-grandparent.

The default row height in Excel only fits 33 rows. Obviously, we need to reduce the row height. The challenge is to be sure that the text is still legible.

I find that a row height of 8 works.

1. Select the entire sheet using ctrl-A.
2. Right-click anywhere on the sheet.
3. Choose “Row Height” from the menu.
4. Enter 8 as the height.

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

To start with, we’ll create the formatted area for the first person.  

Merge two sets of cells

1. Select cells A32 and B32.
2. Right-click and choose “Format cells”.
3. Switch to the Alignment tab.
4. Check the “Merge cells” box.

Repeat this merge for cells A33 and B33.

Add an outside border

Place a border around cells B16 and B17 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.

Set the font type and size of the name merged cells

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 don’t look great at small sizes. I find that Segoe UI displays and prints well at that size.

1. Select the merged cells A/B 32.
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 font type and size of the date merged cells

In order to display and print correctly (without words overlapping each other), the dates need to be slightly smaller.

1. Select cell A/B 33.
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 7 (type the number into the box).

Set the text alignment

I prefer names to be left-justified and dates to be centered.

You must change the vertical alignment of both cells from the default. This ensures that they display and print well.

You may not have worked with the vertical alignment before. These are the options in the top row of the Alignment area in the Home menu.

I’ll show pictures below of the alignment configuration.

Let’s start with the name cells.

1. Select the merged cells A/B 32
2. Set the vertical alignment to Middle Align.
3. Set the horizontal alignment to Left.

Here is the picture of these alignment choices:

Now, we format the date cells.

1. Select the merged cells A/B 33
2. Set the vertical alignment to Top Align.
3. Set the horizontal alignment to Centered.

Format the coloring for the name

I like to use a different color in the name field for male and female ancestors.

My preference is a light-blue for males. Here it is in the color pick tool:

I also use light-orange for females. Here it is in the color pick tool:

To complete this step, set the fill color of the name field (B16) to either male or female (depends on who the tree is for!).

To change the color of the name field:

1. Select cell A32 and B32.
2. Expand the color pick tool in the Home menu.
3. Choose your color.

Format the coloring for the dates

I prefer a light-gray color for the date cells for all ancestors. This acts as a visual contrast.

1. Select cells A33 and B33.
2. Expand the color pick tool in the Home menu.
3. Choose “Gray, accent 3”.

Here it is in the color menu:

Step 5: Create The First Paternal Ancestor

This of course is the father.

Here is a summary of the instructions for the borders, font, and alignment. If you need detailed steps, refer to the previous section.

1. Select cells B16 and B17.
2. Place an outside border around the two cells.
3. Set the font type and size of B16 to Segoe UI size 8.
4. Set the font type and size of B7 to Segoe UI size 7.
5. Set the alignment of B16 to “Middle Align” and “Left Justified”.
6. Set the alignment of B17 to “Top Align” and “Centered”.

Set the background color

1. Set the background color of B16 to light blue.
2. Set the background color of B7 to light gray.

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 “Postlethwaiteblaney” will display as “Postlethwaitebla”.

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

Enter a space into cells C16 and C17.

Step 6: Create The Paternal Ancestors For The 3rd-6^th Generations

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

This means that we can use copy-and-paste to copy the first paternal ancestor.

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.

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

3^rd Generation

• C8
• C40

4^th Generation

• D4
• D20
• D36
• D52

5^th Generation

• E2
• E10
• E18
• E26
• E34
• E42
• E50
• E58

 6^th Generation

• F1
• F5
• F9
• F13
• F17
• F21
• F25
• F29
• F33
• F37
• F41
• F45
• F49
• F53
• F57
• F61

We’ll deal with the seventh generation later.

Step 7: Create The First Maternal Ancestor

This is the mother.

The steps are the same as for the father, except for the background color.

1. Select cells B48 and B49.
2. Place an outside border around the two cells.
3. Set the font type and size of B16 to Segoe UI size 8.
4. Set the font type and size of B7 to Segoe UI size 7.
5. Set the alignment of B48 to “Middle Align” and “Left Justified”.
6. Set the alignment of B49 to “Top Align” and “Centered”.

Set the background color

1. Set the background color of B48 to light orange.
2. Set the background color of B49 to light gray.

Format the adjacent cells to stop overspilling

Enter a space into cells C48 and C49.

Step 8: Create The Maternal Ancestors For The 3rd-6^th Generations

Select and copy the four cells B48, C48, B49, and C49.

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

3^rd Generation

• C24
• C56

4^th Generation

• D12
• D28
• D44
• D60

5^th Generation

We have to do things a little differently for the fifth generation. We can’t just copy the four cells because some will overwrite paternal ancestor cells we’ve already set.

Instead, we’ll copy the maternal name and date cell only.

Select cell B48 and B49 only and copy them into these cells:

• E6
• E14
• E22
• E30
• E38
• E46
• E54
• E62

 6^th Generation

We can go back to copying four cells for this generation.

Select and copy the four cells B48, C48, B49, and C49.

Paste into these cells:

• F3
• F7
• F11
• F15
• F19
• F23
• F27
• F31
• F35
• F39
• F43
• F47
• F51
• F55
• F59
• F63

We’ll deal with the seventh generation next.

Step 9: Create The 7^th Generation

This furthest generation has a different format to the others.

 In order to fit the 64 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.

1. Set the color of cell H1 to light blue.
2. Copy the color to every odd-numbered cell i.e. H3, H5, H7, H9 etc. until H63.
3. Set the color of cell H2 to light orange.
4. Copy the color to every even-numbered cell i.e. H4, H6, H8, H12 etc. until H64.

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

1. Set the color of cell I1 to light gray.
2. Copy the color to all cells from I2 to I64.

And that’s it! We’ve got all our ancestor fields.

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

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 10: Create The Connector Lines From The Home Person

Because of the need for space, the connector lines from the Home Person to the parents are placed in a different configuration than most generations.

The configuration looks like this (I’ve added the lengths of the lines).

Horizontal line above the home person

1. Insert a black horizontal line into cell A16.
2. Right-click the line and choose “Format Shape”.
3. Switch to the “Size and Properties” tab.
4. Edit the width to 0.25 cm (enter this into the input box).
5. Use the keyboard arrows to align the line along the bottom gridline of cell A16.
6. Use the keyboard arrows to ensure that the right edge touches the left border of the Father.

Vertical line above the home person

1. Insert a black vertical line into cell A16.
2. Right-click the line and choose “Format Shape”.
3. Switch to the “Size and Properties” tab.
4. Edit the height to 4 cm.
5. Use the keyboard arrows to connect the top of the line with the left tip of the horizontal line.

Horizontal line below the home person

1. Copy the horizontal line you placed above the home person.
2. Paste and drag the line down to about A48.
3. Use the keyboard arrows to align the line along the bottom gridline of cell A48.
4. Use the keyboard arrows to ensure that the right edge touches the left border of the Mother.

Vertical line below the home person

1. Copy the vertical line you placed above the home person.
2. Paste and drag the line down to about A34.
3. Use the keyboard arrows to connect the bottom of the line with the left tip of the vertical line you aligned with the Mother.

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

Things get easier from here on because the connector lines are the same configuration within each generation.

So, you just need to create the first set. Then I’ll give you a great tip for copying them down to the other ancestors of this generation.

Also, you don’t need to insert lines from scratch from here on. I hate doing that because getting straight horizontal or vertical lines is fiddly.

Instead, I always copy a line I’ve already created and edit it.

So, let’s go. We’ll start with the connector lines from the father to his parents. They look like this:

Horizontal line above the father

1. Copy one of the short horizontal lines you already created.
2. Paste it into about cell B8.
3. Edit the width to 1.3 cm.
4. Align it along the bottom gridline of cell B8.
5. Position the right tip to touch the left edge of the ancestor box.

Vertical line above the father

1. Copy one of the vertical lines you already created.
2. Paste it into about cell B8.
3. Edit the height to 1.85 cm.
4. Align it along the bottom gridline of cell B8.
5. Position the right tip to touch the left edge of the ancestor box.

Horizontal line below the father

1. Copy the horizontal line you placed above the father.
2. Paste and align it along the bottom gridline of cell B24.
3. Nudge it to touch the left edge of the ancestor box.

Vertical line below the home person

1. Copy the vertical line you placed above the father.
2. Paste and align it to intersect with the horizontal line and the bottom of the father’s 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 B8 to B15.
2. Paste into cell B40.
3. Select and copy the cell range from B18 to B25.
4. Paste into cell B50.

That’s it for this generation.

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

You now know how to copy, paste, and position a horizontal and vertical line.

So, I’m going to focus on the heights and widths.

Let’s start with getting the connector lines into place for the first great-grandparent to his parents. Then we can copy them down.

1. Copy a horizontal line to cell C4.
2. Size it to 1.3 cm and nudge it into position along the bottom of the gridline of C4.
3. Copy a vertical line to cell C4.
4. Size it to 0.8 cm and nudge it into position.
5. Copy and place the new horizontal line into position in cell C12.
6. Copy and place the new vertical line into position in cell C10.

Copy the connector lines to the other ancestors in this generation

Select and copy the cell range of C4 to D7.

Paste into these cells:

• C20
• C36
• C52

Select and copy the cell range of C10 to C13.

Paste into these cells:

• C26
• C42
• C58

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

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

The vertical lines for this generation are shorter. They are 0.3 cm.

The horizontal lines are 1.3 cm.

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

Copy the connector lines to the other ancestors in this generation

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

Paste into these cells:

• D10
• D18
• D26

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

Paste into these cells:

• D14
• D22
• D30

Step 14: Create The Connector Lines From The 5th To 6th Generation

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

The vertical lines for this generation are about 0.15 cm.

The horizontal lines should align along the middle of cells E1 and E4. Their length is 1.3 cm.

Copy the connector lines to the other ancestors in this generation

Select cell E1.

Paste into these cells:

• E5
• E9
• E13
• E17
• E21
• E25
• E29
• E33
• E37
• E41
• E45
• E49
• E53
• E57
• E61

Select the range of cells of E4.

Paste into these cells:

• E8
• E12
• E16
• E20
• E24
• E28
• E32
• E36
• E40
• E44
• E48
• E52
• E56
• E60
• E64

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

This can be either very simple or a bit tricky.

Easier option

If you want to take the simple route, use a single horizontal line that connects the child and parent like in this picture:

Copy and resize a horizontal line to 0.5 cm for this option.

Position it along the bottom gridline of cell G1.

Then copy cell G1 and paste it into all the cells in this column down to G64.

Trickier option

I prefer a fork that looks like this picture:

The challenge is that the four lines are very small and a bit finicky to move into position.

All four lines are sized at 0.25 cm.

Once you have them positioned in the fork structure in cell G1, copy and paste the cell all the way down to G64.

Get Our “Done For You” Bundle

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

1. Classic template with name and date fields
2. Template with names, dates, and photo areas
3. Template with name fields and photo areas
4. Simplest template with name fields

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