We will be using hyperlinks and spreadsheet formulas. Also, this literally took me an entire day to perfect so don’t forget a nice cup of your favorite caffeinated beverage and bring many, many snacks. If this scares you, you can always save yourself many hours of work and buy THE ONE I MADE FOR 12 GENERATIONS on Etsy. It’s an instant download.
GOOD NEWS: You don’t need to be an expert to create your very own customized 12 GENERATION PEDIGREE SPREADSHEET.
Hello genealogy enthusiasts,
In case we haven’t met, I’m the elf from House Elves Anonymous. I make printable genealogy artwork, family tree planner inserts, and pedigree spreadsheets for the House Elves Anonymous Etsy Shop! Recently, I’ve suddenly had a lot of people requesting tools and artwork to help them document 12 generation family trees. I ended up making my own 12 generation pedigree spreadsheet because I couldn’t find exactly the right thing. It seems like there isn’t a lot out there about this, so I’ve decided to share my workbook creation technique here, in case it will be useful to any of the family historians out there.
If you follow along with these instructions, you can create your own family tree/genealogy spreadsheet workbook for 12, 13, 14, 15 generations, or more!
I’ll assume you have a basic knowledge of spreadsheets so this post doesn’t get too long, but if you have any questions, please feel free to ask in the comments below.
IF YOU’VE EVER TRIED TO FIT A 12 GENERATION PEDIGREE SPREADSHEET ON ONE TAB…
You already know what a horror that can be. There are so many cells of data! 4,096 cells and then some, if you want more than just names and dates. Before even entering the actual customized data, that file is gigantic. If it loads at all, it tends to load painfully slowly or freeze up completely as soon as you try to enter data. At some parts of the massive pedigree chart, certain ancestors have to be spread way too far apart and it just becomes needlessly difficult to navigate. So what’s the best way around this? Break that lineage tracker down into smaller, manageable pieces. I’ll walk you through the process, step by step. Without further ado…
HOW TO CREATE YOUR VERY OWN 12 GENERATION PEDIGREE SPREADSHEET / GENEALOGY TRACKER WORKBOOK WITHOUT SPENDING A PENNY
I like to use Google Docs to organize my genealogy research. It’s part of a software suite comparable to Excel, only it’s free and connected to your (also free) Gmail account. If you don’t already have a Gmail address, you can get one here.
TAKE A LOOK AT THE FINISHED VERSION BEFORE YOU BEGIN, SO YOU CAN SEE WHERE THESE INSTRUCTIONS ARE LEADING YOU. If it helps you to have a visual reference, you can look at the 12 generation pedigree chart project that I made over HERE. You have my permission to copy that design for personal use, as long as you don’t sell it or give it away to anyone else. That chart uses the CINZEL DECORATIVE font and the bottom color of the 4th row from the left where you select your font colors. Ready to begin? Onward!
DIY PEDIGREE SPREADSHEET INSTRUCTIONS
1. GO TO GOOGLE SPREADSHEETS
While logged into your Google/Gmail account in another window, go to https://docs.google.com/spreadsheets/. From there, click the big plus sign to start a new spreadsheet, and give it any title you want.
First, let’s design your ancestor box template. Start on the upper right-hand side of your blank spreadsheet. This is the step where you choose the format you want each person’s info to be added into. For example, it could be 3 lines for Name, life span, and place, with a thick border around that grouping. Try not to make it too big, but include the information you find essential to your research. I recommend making the total height of the box 10 cells tall because it makes the formulas we’ll use later much easier to edit. Once you get this boxed ancestor area the way you like, we’ll be making a lot of copies, so be sure to get it just right from the beginning. Believe me, you do not want to go back and make changes later!
Select all three of the boxes you started with. Copy them, and paste them immediately below the first block of 3. To the left of that grouping, paste one single ancestor box, and change the border so it takes up the total height of the box grouping to its right.
Continue copy/pasting this way until you feel that you have 6 generations. At this point, you can delete the unused columns on the left and bottom of your spreadsheet. You can also add in extra border cells/columns and header text if you like. At this point, you should have a pretty organized pedigree spreadsheet, with 6 columns for the most recent generations on your tree. You can go ahead and label this tab something like “First 6 generations before branching”. Also, to the right of your right-most generation, insert a row. We will use this row to create hyperlinks to where each ancestor’s family tree branch continues. But first, we need to create those extra tabs on our spreadsheet.
Select your entire page by clicking the box in the top left corner, under where it says fx. Copy the whole page, click the plus symbol at the bottom left side of the page. That will create “sheet 2”, which you can label “Branch 1”. And you can paste that first page onto the second tab. If you want MORE than 12 generations in total, you should add the # of extra generations on the “Branch 1” tab, or on the “First 6 generations before branching” tab, or split the extra generations across both tabs at this point. Now is the part where we get fancy. Go back to your first tab, the main tree.
In the column that you added to the right of the page, next to the top-right ancestor box, in an empty cell, write “Branch 1 “. Click that cell, and ‘Insert’ > ‘Link’. Select the ‘Branch 1’ tab from the “Sheets in this spreadsheet” dropdown menu, and click ‘APPLY.’
Go back to the ‘Branch 1’ tab. Click the first cell of the first ancestor (top left), and type or copy/paste this in:
=‘First 6 Generations before branching’!L11
Go ahead and do steps 6-8 for the other 63 ancestors under that first one on your main tree.
Now you can begin filling in your family tree workbook!
You can follow and connect with me on:
Happy family tree climbing!