How to Organize your direct line in excel Spreadsheet
If there is one thing I have learned over the past 35 years of doing genealogy work, it is that ORGANIZATION IS KEY!!
about 20 years ago, when I REALLY started getting serious about doing Family History and research, I was all over the place. I had notebooks and pads of paper and everything stuffed into a folder. Disorganization reigned supreme.
I had started just plugging in names everywhere,, aunts, uncles, cousins…EVERYONE!! Thats not a bad thing….BUT the problem is, you start getting far back enough it is hard to keep everything straight. Then there are men with multiple marriages, widows, new children with different women., side lines etc… It was a lot to keep track of. And it was frustrating and chaotic.
Who belongs where? Was that Mary’s dad or Amanda’s Dad? Which mother belonged where? I even accidentally put the wrong person in the wrong line.
I read somewhere that the best way to start is little bits at a time. Start with direct line only. Get as far back as you can, and then start plugging in from there.
Several years ago I saw a post on how to organize your direct line on excel spreadsheet using a color coding system and numbering system. This was a game changer!!
All you need is a spread sheet program (like excel) and some basic math skills.
This is for you direct line, YOUR personal ancestry. This will not include a widows 2nd wife or someone that isnt a direct ancestor to you. This isn’t all their children or brothers and sisters. This is grandparents direct line only.
This is to keep your personal DIRECT grandparent line organized.
Where to start
In your spreadsheet workbook you start by labeling the columns across. Self, parents, grandparents, 2nd great grandparents. Then 3rd great grandparents etc.
You can go as far as you like, I started with5 and then added as necessary.
Start numbering with your grandparents
You are always number 1. Your Father is 2 and mother is 3. Fathers are always placed above the mothers on charts. . I added them to my dummy sheet as an example. I labeled them a nondescript color scheme, because it isn’t really of main concern. Where the chart REALLY gets started is column C,(Where your grandparents start.)
NOTE: These is a dummy sheet illustrative purposes only…these are not actual people on my charts.
4 is your paternal grandfather Color #1 (red)
5 is your paternal grandmother Color #2 (yellow)
6 is your maternal grandfather Color #3 (blue)
7 is your maternal grandmother color #4 (purple)
Keep your colors
As you can see #8 and 9 and your paternal grandfathers parents -father on top, mother underneath. Both in Red
10 and 11 are your paternal grandmothers parents -again father 1st, mother under. Both in Yellow
12 and 13 are Maternal Grandfathers parents- both in blue
14 and 15 are your maternal grandmothers parents.- both in purple
EVERY COLUMN WILL DOUBLE! Each number of ancestors AND each color will get doubled in every column.
So remember when setting up your spreadsheet, to double the reds, double the yellows, double the blues and double the greens in every column. 1,2,4,8,16,32,64,128,256 etc etc etc.
The beauty of this system is you know all the reds are your paternal grandfathers direct line, all the yellows are your paternal grandmothers direct line, all the blues and greens are your maternal grandfather and grandmothers lines.
Keeping Track by Number
You start getting an awful lot of colors once you start reaching about the 4th and 5th generation. So how do you keep track of which parents belong where?
In numbering it will always be easy to find the parents or child of a particular person
ALL MALES ARE EVEN NUMBERED (we don’t count ourselves as even or odd as #1, That only represents you as being first on the chart. If you are someone’s mother or father you would be either 2 or 3 on their chart.)
ALL FEMALES ARE ODD NUMBERED
You are always #1 (no matter your sex) , your father will be 2 and mother is 3. The math formula is double the number for the father and then add an extra 1 for the mother.
x= ancestor number on chart
Father : 2x
Mother: 2x+1
example: your father is #2 on your chart. Therefore his math equation to find his parents would be 2x (or 2×2) . Therefore his father is #4 on the chart. His mother would be 2x+1, or (2×2) +1, which is #5.
Basically DOUBLE for the father, and then Double and add one for the mother.
Have I completely lost you? Lets try one more:
Your 2nd great grandfather is #8, in order to know what his parents numbers are we plug in the math.
8×2=16. Your paternal 3 great grandfather is #16 on your chart.
(8×2) +1=17. Your paternal 3 great grandmother is #17 on the chart.
SO if we look at #24 , we know that HIS parents would be #48 and 49 on the list. And they would be in Blue.
#75 Her parents would be #150 and 151 (75×2 = 150, and 75×2+1= 151)
strike that…reverse it!
Want to figure out who someones child is? The math equation is reverse .
Males (or even numbers): Divide by 2 to find the child
Females (or odd numbers). Subtract 1, then divide by 2 to find the child.
EXAMPLE:
#39 on my dummy list is Lisa Shaw.
SInce she is an odd number you will subtract 1 first, which gives you 38. Now half of that is 19. So we know her child was #19 on your list.
#34 on my dummy list is Joshua Mack. Since he is an even number (male), he would be divided by 2, which means his child was #17.
REMEMBER:
*Males are always listed first in pedigree numbering. SO if you have a male, his spouse/partner/ mother of child will always be ONE MORE than his number
*Females are listed under their spouse or partner in pedigree numbering. So if you have a female, her spouse/partner/father of her child will always be ONE LESS than her number
EXAMPLES:
you know that #39, Lisa Shaw is a female, then her spouse or father of her child has to be #38.
you know that #34 , Joshua Mack is a male, then the spouse or mother or his child is #35.
Incomplete information
So what happens if you dont have complete information on a person? You still leave room for them on the chart, Even if you cant fill it in just yet.
Fill in as much information as you have.
In my dummy chart you will see that in #65 I only have a 1st name.
#66 You could have an UNKNOWN. Perhaps that was written on a birth certificate or probate will. You could also just leave it blank if there is no record at all.
In #69 I wrote a last name with ???, meaning it COULD be the last name, unsure, or perhaps try a variant spelling. (Maybe census taker had terrible writing, or immigration clerk spelled it phonetically).
Adding more sheets
Eventually one page will be too big to handle, so you can add a second page to your workbook by clicking the plus sign. Your numbering will continue on from where you left off, colors etc. It is up to you . You may decide you can handle it all on one sheet. But remember by the 20th generation, you are looking at over a million names!
Transferring over to a pedigree chart/Using a Database
When you have this information numbered and color coded it will be easy to transfer information over to a pedigree chart. You will know exactly where to put everyone, which child belongs to which parents and vice versa.
So even if you don’t have a record, you need to leave a spot for them in your excel spreadsheet.
If you have a personal database with vital information, make sure that all your names are numbered the same. That way if you are entering in the information on #35, you know that on your chart and in your database #35 is the same person. #35 will be the same record across the board and you will be able to find that information easily.
FOR EXAMPLE (remember these are dummy names and made up vital stats so no identities can be stolen or used in fraud purposes)
So as you can see on my dummy records, #19, 20 and 21 on my spreadsheet match #19, 20 and 21 in my vital information database.
On an external Hard Drive ( or Flash Drives) there are individual file folders with all the source documentation.
Be sure to label your hard drives and flash drives if they get full with those numbers as well so you can easily find the information and pull it up quickly.
I hope that this little trick can help get and keep you organized as you navigate your way through your direct line.
