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.
2. DESIGN YOUR ANCESTOR DATA AREA – FOR ONE PERSON ONLY
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!
3. COPY/PASTE TO ADD BOXES
Now that you have your well-designed ancestor box in the top right corner of your spreadsheet page, select the whole box, copy it, and paste it below your first box, with no space between them. To the left of the first box, paste again, but this time, change the border of the newest box so it takes up the total height of the first two boxes. So you should have one box on top of the other on the right, then immediately to the left of those two boxes, a tall box the same height as those two put together.
4. ONCE MORE FOR ELVIS
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.
5. DELETE UNUSED CELLS
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.
6. CREATING THE EXTRA 64 TABS/PAGES WHERE THE FAMILY LINES CONTINUE
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.
7. CREATE A LINK FROM THE MAIN TREE TO THE FIRST BRANCH EXTENSIONIn 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.’
8. HOW TO AUTOFILL THE FIRST ANCESTOR ON BRANCH 1 FROM YOUR MAIN FAMILY TREE
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
Replace L11 with the letter and number of the cell from your “First 6 generations before branching” tab where the top right ancestor’s matching cell is. Do this for each cell belonging to the first ancestor on Branch 1.
9. CONGRATULATIONS! NOW YOU’VE GOT THE FIRST 6th GENERATION ANCESTOR ALL LINKED AND BRANCHED!
Go ahead and do steps 6-8 for the other 63 ancestors under that first one on your main tree.
10. THAT’S IT! WELL DONE!
Now you can begin filling in your family tree workbook!
If you have any questions, please feel free to ask.
You can follow and connect with me on:
Instagram
Pinterest
Happy family tree climbing!
p.s. One more free family history resource for you. Have you heard of the new genealogy volunteer network, WalkMyPast.com? It’s a free genealogy community created by Abbie Allen, where genealogists and family tree enthusiasts help each other with research, from all around the world. Why not take a moment to join the community now? It’s completely free.
Remember to Pin this tutorial so you’ll be able to come back to it later!
