KenoshaRabbits
Well-known member
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.
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.
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.
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.
-- 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.
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.
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.
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.
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.
-- 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.
