Create A 4-Generation Vertical Family Tree In Excel (Tutorial)

This tutorial takes you step-by-step through creating a four-generation vertical family tree in Microsoft Excel.

A four-generation tree goes back to eight great-grandparents.

The challenge is how to get the details of eight people to fit across a single printed page. Sure, you can use a tiny font. But a family tree should be easy to read on screen and on paper.

This tutorial builds a tree using a readable font size with space for optional photos and location flags.

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

Table of Contents

What A 4-Generation Vertical Tree Looks Like

Here is an example of how a four-generation family tree with photos can print in landscape mode like this:

Of course, you don’t need to include the photos. And some people like to use flags or other icons for geographical information.

Using Boxes (Shapes) Instead Of Cells

You may be expecting a format where you enter first and last names into cells.

Despite a lot of experimentation, I can’t fit a four-generation tree into a single Excel page this way.

I can do it with one less generation. You can check out my tutorial for creating a simple three-generation family tree in Excel.

But I’ve found that using shapes lets me get the format I need. If you’re not familiar with this feature, the screenshots in this tutorial show every step.

Looking for an extra generation? We have separate tutorials on:

Video Walkthrough

If you prefer a video walkthrough, this goes through all the steps in this article:

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: Make The First Column Very Narrow

We are going to change the width of the first column to be very narrow.

The purpose of column A is to act as a placeholder to stop the leftmost shape from spilling outside the print boundary.

Change the width of the first column to 0.1 with these steps:

Select column A
Right-click and choose “Column Width” from the drop-down menu.
Set the size to .1

Step 3: Create The Name Area For The First Great-Grandfather

I like to put photos of ancestors above their names, so I’m going to leave lots of space above the name area.

You may not want photos, but I suggest you follow my instructions to the end. Then you can tinker with the layout.

The names appear within a rectangular shape that is sized to have two lines. The first name goes into the top line and the second name in the bottom line.

It will eventually look like this:

Go to the Insert tab in the top menu ribbon.
Expand the Illustrations drop-down.
Expand the Shapes drop-down.
Choose the rectangle with rounded corners.
Click into cell B5 for the initial placement.

Here’s an illustration from Excel 365:

You probably have a fat blue box right now. I’ll give you the dimensions that work for me for this generation.

Right-click the box and choose “Size and Properties” from the menu.
Enter a height of 1.38 cm.
Enter a width of 2.73 cm.

Remove or set the background color

If you want male/female color coding, change the fill color to your choice for male ancestors. I like to use a light-blue.

To change the color:

Right-click the box and choose “Format Shape” from the menu.
Expand the Fill section.
Use the Color pick tool.

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.

Both of these fonts are too wide for our purposes.

If we use a wide font, then we’d have to set the size to 6 or 7 to fit first and last names into our box. That’s so small that it would be hard to read.

Instead, I use a narrow font.

“Bahnschrift SemiBold Condensed” is my favorite for being the most legible when printed.

By using this font, you can set the size to 11.

Go to the Home tab in the top menu ribbon.
Click the shape we just inserted.
Change the font type to “Bahnschrift SemiBold Condensed.
Change the font size to 11.

Here’s an illustration of these steps:

Optional formatting

That’s all the formatting that I do.

I prefer the names to be left-aligned, but you may want them centered. You can make those changes at this point.

Step 4: Copy The Box For The First Great-Grandmother

As a reminder, this is how the great-grandparent line will eventually look like:

There is a tiny gap between the couples and a wider gap between each pair.

To complete the first pairing, copy and paste the first name box.

Use the arrow keys to position the copy to sit right beside the original box.

I find the arrow keys more precise for positioning. Use the grid lines to make the vertical positioning line up.

Finally, change the color to a light orange.

Step 5: Create Three More Great-Grandparent Box Pairs

Now that we have one great-grandparent pair, we’ll copy the two shapes three times.

Use the illustration in Step 4 as a guide to positioning each pair across the page.

Step 6: Check That Everything Fits On A Single Page

The great-grandparent generation is of course the widest line. It’s the trickiest to fit on a single page.

At this point, you should double-check that you haven’t spilled over to the right of the 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 Preview to see what you’ve got. Focus on the right-most grandparent box and be sure that it is entirely on the page.

If you see half an ancestor box (!), go back to the page and start nudging the boxes to the left to make the gaps narrower.

Step 7: Create the Four Grandparent Boxes

Everything gets easier from here.

Copy the first great-grandfather box and paste it at about cell C14.

Use the left arrow to nudge it into a central position below the first great-grandparent pair.

You may want to widen the box to fit more visible letters. We had to have relatively short boxes for the previous generation but we can be more generous here. I add an extra half centimeter.

Copy the second great-grandmother box and paste it at about cell F14. Use the right arrow to nudge it into place below the second great-grandparent pair.

Use this illustration as a visual guide:

Once you’ve got the first grandparent pair in position, just copy the two boxes and nudge them into place on the right half of the page.

Step 8: Create The Two Parent Boxes

At this point, you’ve got the general idea.

Copy the first male grandparent and paste it at about D23. Nudge it right to a central position between the two grandparent boxes.

Copy the first male grandparent and paste it at about L23. Nudge it left to a central position between the two grandparent boxes.

Step 9: Create The “Home” Person

This is the lowest entry in the tree.

Copy any male or female box and paste it at about cell H29.

We now have the names spaced out in the correct layout.

The next steps are to add the traditional connecting lines in a family tree.

About Creating Connecting Lines In Excel

The goal is to achieve this look:

Part 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.

I recommend that you draw full lines before adding photographs. Any images added after the lines will plonk on top of them.

Let’s say you take the opposite approach and add the images first, and then add lines to the edge of the image.

If you later want to remove the images, your lines will be too short and will need further editing.

Step 10: Create The First Short Vertical Line Under Great-Grandpa #1

Create the first short vertical line underneath the top 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 below the box for Great–Grandpa 1, somewhere near the middle of column B. Drag the cursor down to create the line.

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

Now you have the line, we’ll 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 1 cm

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

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

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

Tip: enter a temporary very long last name into the box

The final step here is to position the line halfway along the bottom of the last name cell.

My tip is to insert a temporary string of letters as the last name for Grandpa #1 to fill the entire line.

You can enter “Verylonglastname” for example. The box truncates the last two letters if you are using the same font and size as mine.

Depending on the font you’ve chosen, there will be 13-16 characters displayed. You just need to position the line under the middle letter.

With our example, the line should be under the “g”. This isn’t exactly half with scientific precision, but it’s close enough for the human eye.

Tip: use the positioning circle

To position the line, drag it to intersect with the bottom of the last name cell.

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.

Tip: ensure the line is straight

When you drag lines in Excel, it’s easy to be left with a slight angle i.e. it’s not exactly vertical or horizontal.

You might think the “Rotation” property in the Size control lets you control this. But it doesn’t. There may be some trick to using the shape properties to get a zero-degree angle, but I haven’t found it.

Instead, click the edge of the line and use the cross-hairs to judge when the angle is zero.

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 decorative images to the tree (final steps).

Or just grab our “done for you” templates.

Step 11: Create The Second Vertical Line Under Great-Grandma #1

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 the second box on the sheet.

Step 12: Create The First Horizontal Line Between Great-Grandparents

Repeat the instructions in Step 10 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.

Drag the horizontal line so that the left edge intersects with the bottom of the vertical line under Grandpa #1.

You may not be able to position it exactly through drag-and-drop (I can’t). So, drop to as close as you can and use the arrow keys to nudge it into place.

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

My tip is to use the height control instead of drag-and-drop. It’s far easier to fine-tune.

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

Step 13: Create The Long Vertical Line From Great-Grandparents To Grandfather

This vertical line is positioned halfway across the horizontal line.

It stretches down to tip the top of the paternal grandfather box.

Again, my advice is to edit the father’s first name to fill the cell. Then use the middle letter as your guide.

I won’t repeat the exact steps for positioning. But I advise that you follow this sequence for the easiest experience.

Start with a short black vertical line that is centered at the top of the father’s box.
Use the up arrow to nudge the short line up to meet the long horizontal line.
Open the shapes property and use the height control to lengthen the arrow back down to meet the father’s name box.

Step 14: Copy The Intersecting Lines To The Other Great-Grandparent Pairs

You’ll be glad to hear that you can simply copy what you’ve got.

Hold down the control key.
Click each of the four lines that make up the connector.
Click control-c to copy the lines
Click control-v to paste the lines anywhere

Now position the copied set of lines to sit beneath the next set of great-grandparents.

Rinse and repeat two more times!

Step 15: Create The Intersecting Lines From Grandparents To Parents

We’ll start by copying one set of connector lines from the previous generation to beneath the first set of grandparents.

You now have the correct number of lines, but they aren’t aligned correctly.

Use the right arrow to move the right-most vertical line to beneath the 2^nd great-grandparent pair.
Use the width property to widen the horizontal line to intersect with it.

Now, copy the adjusted connector lines for the second set of grandparents.

Step 16: Create The Intersecting Lines From Parents to Home Person

Copy one set of connector lines from the previous generation to beneath the first set of parents.

Widen the horizontal lines and reposition the vertical lines appropriately.

And that’s it! Whew!

You now have the vertical 4-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.

Preparing Images For Your Excel Tree

In our example tree at the top of this article, we have cropped images with faces.

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:

100 X 100 pixels
3 cm X 3 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 17: Add Images To The Tree

I’ve left plenty of space for an image above each name box.

You will likely have to make small adjustments to the size of your image once you’ve pasted it. Repeat these steps for each image:

Put your cursor a few cells above the appropriate name 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 to sit on top of the name box.

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

You can also widen or narrow the images within Excel by using the handles on the picture object containing the image.

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.

When the bottom of the image seems perfectly adjacent to the top of the name box, the printed version has a tiny gap.

That may not bother you. But if you’re like me, you’ll want the print-out to look perfect.

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.

I found that with some images, I had to nudge them down so they seem to be overlapping the name box. But they print perfectly (without a gap, but no overlap).

With other images, I had to shift them slightly to the right or left so that they were misaligned in the spreadsheet view.

But they were perfectly aligned in the Print Preview and printed version.

It’s a bit of trial and error.

Get Our Done-For-You Bundle

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

the classic family tree with name boxes
a template formatted for added photos
a template formatted for location flags
a template formatted for photos and flags

In addition, I’ve included perfectly-sized images for 50 U.S. state flags and 48 country flags.

All this for the price of a cup of coffee. Grab it at our Gumroad store here: