Creating A Pedigree Instantly Using Excel

Rabbit Talk  Forum

Help Support Rabbit Talk Forum:

KenoshaRabbits

Well-known member
Joined
Apr 27, 2016
Messages
161
Reaction score
2
Location
Kenosha, WI
Hi there, I thought I would share how to make a pedigree using excel. With this you could type in the name of a rabbit, or its ear tattoo, or any sort of identifier and it will fill in a pedigree for as many generations as you want. I will have a blank Excel at the end of the post.

To do this we use two EXCEL Formulae, MATCH and INDEX. These are two specialized formulae. A lot of people probably are familiar with simple formula like " =A1+A2" which will add the numbers in A1 and A2. There are also formula like =SUM(A1:A2) which will also add the numbers in A1-A2.

The formula MATCH is written =MATCH(A, B, C) which means look for term A in column B with a match value of C; and this will return a value corresponding to the row term A is in. For an exact match C=0

The formula INDEX is written =INDEX(X, Y) which means look at row Y in column X; and this will return whatever is written in that cell.

We can combine the two like so =INDEX(X, MATCH(A, B, 0)) which will look up term A in column B and tell us what is in column X of that same row. So I could type in a name of a rabbit and it could output the name of sire of that rabbit. I could paste a similar formula in a different box but replace term A with the cell that has the sire of the rabbit and it would tell me the sire of the sire; or grand sire. do that again and you can have the great grand sire.

-----------------------
So how to accomplish this. First write in the information you want to record. You could record whatever information you want; perhaps you want to record a registration number or a genetic code.
Sample Table.png
Next you want to make the information into a table. To do this; highlight the information and near the top of your Excel screen should be an option to format as a table. Make sure the "My table has headers" box is checked after you click on the style you want you table to be in.
Format as Table.png
This should create a table. The next step is to name the table, although this is optional as the table will have some sort of default name like Table 1. First click on the table, anywhere on the table will do. If you look at the top of your screen, there should be an option near the right side labeled "Design" under the words "TABLE TOOLS"; click this. This will allow you to change the look of your table. On the left side of the screen there is a box which allows you to name the table. Type in whatever you want to call it. This will be the name you enter in the INDEX/MATCH formulae above. I called mine master.
Name Table.png

When you have your table finalized you can add information at the bottom and it will attach to the table, such as adding information about a new rabbit. You can also add a column to the right and it will attach. You can also insert a column in the middle of the table.

Now that the table is named we can use write the formula to find specific information. So =INDEX(X, MATCH(A, B, 0)) X and B written in the form TABLENAME[ColumnHeaderName] and A refers to the cell with the information to look up. So my table is named Master and has columns Name, Sire, Dam.
So if I type the name of the rabbit in A1 and want the name of the sire in B1 I would type in the cell B1 =INDEX(Master[Sire], MATCH(A1, Master[Name], 0)) this will look for whatever is in cell A1 in the Name column of table Master and tell me what is in the Sire column of table Master.
Now if I want to find the sire of the sire in cell C1 I would write =INDEX(Master[Sire], MATCH(B1, Master[Name], 0)) and this will look for whatever was returned in cell B1 in the Name column of table Master and tell me what is in the Sire column of table Master.
If I wanted the breed of the grandsire listed below in cell C2 I would have =INDEX(Master[Breed], MATCH(C1, Master[Name], 0))

You can have a separate sheet in the Excel book for pedigrees. Simply have a Cell where you enter some sort of unique identifier for the rabbit and then base the INDEX/MATCH off that cell.

Here is a copy if this is too confusing for anyone. There is a tab where you can type in the rabbit's name and get a pedigree, and a tab where you can type in the rabbit's ear tattoo and get a pedigree.
View attachment Sample Pedigree.xlsx
* Feel free to use the template for your own personal use or modify it with the particular information you keep track of.

***TIPS***
1) The formula will only return information that is in your table. So in the sample sheet I have information about Oscar such as weight color sire and dam. But I don't have any of that information on the sire and dam. So if I entered Oscar's name on the pedigree, it would fill in his tattoo, date of birth, etc. It would fill in the name of the sire and the dam, but it wouldn't fill in the tattoo of the sire or the sire's sire and so on. It would just show up as #N/A

2) Make sure your identifiers are unique. This could be an issue if you reuse names, tattoos or any other identifier. So if you have two rabbits named Yankee or tattoo RAB01 there will be problems. It appears that the formula will return the instance nearest the top, which might not be the rabbit you need.

3) Make sure information matches up. Misspellings and differences do matter. If your rabbit Lima's sire is X-Ray and you have a row with X-Ray's information; make sure you enter the name X-Ray (with a hyphen) in the name column. If you type XRay the formula won't pick this up. However uppercase/lowercase differences do not matter. X-Ray in the sire column will link to x-rAy or X-raY in the name column.

4) Sorting a column or filtering the column will not effect the ability to create a pedigree. So if you wanted to filter the list to only show rabbits that are currently in your rabbitry, you would only see the rabbits that are on hand. The rows are hidden but they are still in the table. So the pedigree chart will still pull their information. If you sort by gender or weight or ear number this won't mess things up. If you insert columns or rows this won't mess things up either. So if you want to have a column for genetics or # of kits or whatever you can insert them wherever you want.

If you have any questions feel free to ask.

-- Tue May 24, 2016 3:00 pm --

***Printing***
If you make a pedigree in Excel don't worry about trying to format cell sizes so that it fits on one page. Click on "FILE" then on "PRINT". In the settings there is an option to scale the sheet. It's default should be "No Scaling". You can change it to "Fit sheet on one page". This should scale everything so that it fits on one page.
Fit on Shee.png

-- Tue May 31, 2016 12:34 pm --
*******
Feel free to use the template for your own personal use or modify it with the particular information you keep track of.
 

KenoshaRabbits

Well-known member
Joined
Apr 27, 2016
Messages
161
Reaction score
2
Location
Kenosha, WI
alforddm":1adb4olh said:
Wow that is impressive. Thanks for writing all that up.
No problem. I figured people would like to learn how to do this in Excel. It's quicker than having to look up the information on 15 separate rabbits and entering all the data.
 

KenoshaRabbits

Well-known member
Joined
Apr 27, 2016
Messages
161
Reaction score
2
Location
Kenosha, WI
I made a little note on the above post. Feel free to use the attached template for your own personal use or modify it with the particular information you keep track of.

I figured most people would assume I was giving them permission to use it; but just in case I'll make it explicit. Download it, enter your own info, add new columns, create tabs for individual bunnies, etc.
 

jimmywalt

Well-known member
Joined
Jun 4, 2014
Messages
520
Reaction score
0
Location
Michigan
You ABSOLUTELY ROCK!!!!!!!

Thank you, thank you thank you!!!!!!!!

:thankyou: :thankyou: :thankyou: :thankyou: :thankyou: :thankyou:
 

Bre

Member
Joined
Aug 26, 2018
Messages
7
Reaction score
1
Thanks a lot! I was looking for something like this!

Do you know if there is a way to add the inbreeding coefficient in Excel? I know how to calculate them, but my excel abilities are too rusty to be able to do it right now. <br /><br /> -- Sun Sep 09, 2018 7:57 am -- <br /><br /> Here is the formula for the inbreeding coefficient according to Wright

I cannot add the formula nicely, but here it should be understandable
FX = Σ[(1/2)to the power of n1+n2+1 times(1+FA)]

FX Inbreeding coefficient of the animal in question
FA Inbreeding coefficient of the common ancestor
n1 Number of generation from the sire to the common ancestor
n2 number of generation from the dam to the common ancestor

On paper, or on an online pedigree, it is quite easy to count the number of generations to the common ancestor, but with excel I do not know how to do it. Can anyone help me?
 

Barbacchielli

New member
Joined
Jun 9, 2022
Messages
2
Reaction score
0
Thanks a lot! I was looking for something like this!

Do you know if there is a way to add the inbreeding coefficient in Excel? I know how to calculate them, but my excel abilities are too rusty to be able to do it right now. <br /><br /> -- Sun Sep 09, 2018 7:57 am -- <br /><br /> Here is the formula for the inbreeding coefficient according to Wright

I cannot add the formula nicely, but here it should be understandable
FX = Σ[(1/2)to the power of n1+n2+1 times(1+FA)]

FX Inbreeding coefficient of the animal in question
FA Inbreeding coefficient of the common ancestor
n1 Number of generation from the sire to the common ancestor
n2 number of generation from the dam to the common ancestor

On paper, or on an online pedigree, it is quite easy to count the number of generations to the common ancestor, but with excel I do not know how to do it. Can anyone help me?
Hi, did u find a solution for adding this formula to excell?
Thanks
 

Barbacchielli

New member
Joined
Jun 9, 2022
Messages
2
Reaction score
0
Hi there, I thought I would share how to make a pedigree using excel. With this you could type in the name of a rabbit, or its ear tattoo, or any sort of identifier and it will fill in a pedigree for as many generations as you want. I will have a blank Excel at the end of the post.

To do this we use two EXCEL Formulae, MATCH and INDEX. These are two specialized formulae. A lot of people probably are familiar with simple formula like " =A1+A2" which will add the numbers in A1 and A2. There are also formula like =SUM(A1:A2) which will also add the numbers in A1-A2.

The formula MATCH is written =MATCH(A, B, C) which means look for term A in column B with a match value of C; and this will return a value corresponding to the row term A is in. For an exact match C=0

The formula INDEX is written =INDEX(X, Y) which means look at row Y in column X; and this will return whatever is written in that cell.

We can combine the two like so =INDEX(X, MATCH(A, B, 0)) which will look up term A in column B and tell us what is in column X of that same row. So I could type in a name of a rabbit and it could output the name of sire of that rabbit. I could paste a similar formula in a different box but replace term A with the cell that has the sire of the rabbit and it would tell me the sire of the sire; or grand sire. do that again and you can have the great grand sire.

-----------------------
So how to accomplish this. First write in the information you want to record. You could record whatever information you want; perhaps you want to record a registration number or a genetic code.
View attachment 19908
Next you want to make the information into a table. To do this; highlight the information and near the top of your Excel screen should be an option to format as a table. Make sure the "My table has headers" box is checked after you click on the style you want you table to be in.
View attachment 19909
This should create a table. The next step is to name the table, although this is optional as the table will have some sort of default name like Table 1. First click on the table, anywhere on the table will do. If you look at the top of your screen, there should be an option near the right side labeled "Design" under the words "TABLE TOOLS"; click this. This will allow you to change the look of your table. On the left side of the screen there is a box which allows you to name the table. Type in whatever you want to call it. This will be the name you enter in the INDEX/MATCH formulae above. I called mine master.
View attachment 19910

When you have your table finalized you can add information at the bottom and it will attach to the table, such as adding information about a new rabbit. You can also add a column to the right and it will attach. You can also insert a column in the middle of the table.

Now that the table is named we can use write the formula to find specific information. So =INDEX(X, MATCH(A, B, 0)) X and B written in the form TABLENAME[ColumnHeaderName] and A refers to the cell with the information to look up. So my table is named Master and has columns Name, Sire, Dam.
So if I type the name of the rabbit in A1 and want the name of the sire in B1 I would type in the cell B1 =INDEX(Master[Sire], MATCH(A1, Master[Name], 0)) this will look for whatever is in cell A1 in the Name column of table Master and tell me what is in the Sire column of table Master.
Now if I want to find the sire of the sire in cell C1 I would write =INDEX(Master[Sire], MATCH(B1, Master[Name], 0)) and this will look for whatever was returned in cell B1 in the Name column of table Master and tell me what is in the Sire column of table Master.
If I wanted the breed of the grandsire listed below in cell C2 I would have =INDEX(Master[Breed], MATCH(C1, Master[Name], 0))

You can have a separate sheet in the Excel book for pedigrees. Simply have a Cell where you enter some sort of unique identifier for the rabbit and then base the INDEX/MATCH off that cell.

Here is a copy if this is too confusing for anyone. There is a tab where you can type in the rabbit's name and get a pedigree, and a tab where you can type in the rabbit's ear tattoo and get a pedigree.
View attachment 19913
* Feel free to use the template for your own personal use or modify it with the particular information you keep track of.

***TIPS***
1) The formula will only return information that is in your table. So in the sample sheet I have information about Oscar such as weight color sire and dam. But I don't have any of that information on the sire and dam. So if I entered Oscar's name on the pedigree, it would fill in his tattoo, date of birth, etc. It would fill in the name of the sire and the dam, but it wouldn't fill in the tattoo of the sire or the sire's sire and so on. It would just show up as #N/A

2) Make sure your identifiers are unique. This could be an issue if you reuse names, tattoos or any other identifier. So if you have two rabbits named Yankee or tattoo RAB01 there will be problems. It appears that the formula will return the instance nearest the top, which might not be the rabbit you need.

3) Make sure information matches up. Misspellings and differences do matter. If your rabbit Lima's sire is X-Ray and you have a row with X-Ray's information; make sure you enter the name X-Ray (with a hyphen) in the name column. If you type XRay the formula won't pick this up. However uppercase/lowercase differences do not matter. X-Ray in the sire column will link to x-rAy or X-raY in the name column.

4) Sorting a column or filtering the column will not effect the ability to create a pedigree. So if you wanted to filter the list to only show rabbits that are currently in your rabbitry, you would only see the rabbits that are on hand. The rows are hidden but they are still in the table. So the pedigree chart will still pull their information. If you sort by gender or weight or ear number this won't mess things up. If you insert columns or rows this won't mess things up either. So if you want to have a column for genetics or # of kits or whatever you can insert them wherever you want.

If you have any questions feel free to ask.

-- Tue May 24, 2016 3:00 pm --

***Printing***
If you make a pedigree in Excel don't worry about trying to format cell sizes so that it fits on one page. Click on "FILE" then on "PRINT". In the settings there is an option to scale the sheet. It's default should be "No Scaling". You can change it to "Fit sheet on one page". This should scale everything so that it fits on one page.
View attachment 19914

-- Tue May 31, 2016 12:34 pm --
*******
Feel free to use the template for your own personal use or modify it with the particular information you keep track of.
Hi, thanks for sharing. Do u know how to ad an inbreeding calculation formula?
 
Top