Contents |
Excel Spreadsheet Series
The spreadsheet series is located here.
Introduction
The success of a heifer-rearing program can be evaluated by monitoring the height and weight of calves and heifers and comparing the results to breed averages for specific age groups. Although most dairy producers, consultants, feed industry representatives, and veterinarians can recognize under- or overconditioned animals, it is difficult to visually determine whether a heifer’s height or weight is normal for her age. The only real way to tell how heifers are growing is to weigh and measure them several times a year and compare them to standards. With this information, managers can identify potential problems in the rearing program. Whole groups of animals that are undersized, underweight, or overweight indicate improper feeding or management.
The Growth Monitor spreadsheet series provides a set of tools to evaluate heifer growth. Options within the series provide flexibility for monitoring multiple or individual animals, plotting heifer body weight or withers height against current growth standards, and calculating average daily weight gains. These options are available in separate files for each breed. In addition, hip heights may be entered and evaluated for Holstein heifers.
This manual describes operating and interpretation procedures for the core files in the series for each breed:
- groupgrowthmonitorBREED.xls
- groupadgmonitor.xls
- individualgrowthmonitorBREED.xls
- repeatedBREED.xls
Spreadsheets are available on the Dairy Cattle Nutrition website. To download files, right click on the title of the spreadsheet and choose “Save file as”. For the Group Growth Monitor, Individual Growth Monitor, and Repeated files, choose the files for the breed you are interested in to compare your heifers to standards for that breed. The Group ADG Monitor file can be used with any breed.
Please check this link first if you are interested in organic or specialty dairy production
Group Growth Monitor
- Available for Holstein, Jersey, Brown Swiss, Guernsey, Ayrshire, and Milking Shorthorn.
- Versions for Holstein and Jersey calves are also available. Instructions for use are exactly the same; the only difference in the file is the scales on graphs.
- Provides a snapshot comparing multiple heifers to recommended breed standards on a single measurement date.
Instructions
- Measure and record heifer body weight, withers height, and/or hip height (Holsteins).
- Open a new Group Growth Monitor file.
– A dialog box will open automatically with a message about macros. Choose to “enable macros”. If you choose “disable macros”, the spreadsheets will not work.
– If this dialog box does not open, you can change the default setting to disable macros. This allows you to choose whether or not to enable macros on a case-by-case basis (when you open the file). Here’s how: Open Excel and click on the “Tools” menu. Choose “Macro” and then “Security”. A window will open; click on the “Security Level” tab. You will see the three levels of security in Excel and their definitions. We recommend setting your security to “MEDIUM”. This option will allow you to choose between “enable” and “disable” every time you open a file containing macros. Choose “enable” to activate the macros. Note: if you choose “disable” the spreadsheets will not work properly.
- Click on the “Input” tab.
- Enter the measurement date (in cell C1) and the farm name (in cell K2).
- Enter identification information and growth measurements for each animal.
Notes on entering data:
– Type over any example data present in the file. If you do not, the example will be included with your heifers and will affect your results.
– Enter data in cells containing blue text. Do not enter anything in cells with black text; these are formulas and changing them may cause them to stop working.
– You must enter an ID name or number and a group name or number for each heifer if you want to separate results by group. - As you enter data, heifers that fall below recommended weights will be marked with a red “X” in the “Underweight Heifers” column.
– Check marked records for measurement accuracy, data entry error, or actual growth problems.
– Note: To view all heifers checked “underweight,” hold down “control” and type an “l” (lowercase L). This will sort all checked heifers to the top of the list. - Prepare graphs and summary table.
– Due to the spreadsheet’s ability to handle a large number of heifers, most users will need to update the graphs and summary table.
– This is a simple matter of using pre-programmed commands called macros.
*Update the summary table by holding down “control” and typing “t”.
*To display graphs display correctly, hold down “control” and type “h”. This command hides all the blank rows in the “Input” sheet. - View the summary table and graphs by clicking on the appropriate tabs at the bottom of the window.
– Note: If the line for “Your Heifers” shoots off the scale or has extreme curves in it, see Step 6. - If you need to enter more data into the spreadsheet, reveal hidden rows by holding down “control” and typing “u”.
- The spreadsheet is currently set up to handle a maximum of 1200 heifers. Users can expand this number, but will have to make a few changes to maintain functionality.
– Complete the following steps:
a) Copy formulas down the column in all columns containing formulas.
b) Change the range of cells included in the summary table (it is a pivot table).
– Click on a cell inside the table.
– Right click and choose “Wizard”.
– Click on the back button to access the data range.
– Change the last cell reference to include as many cells as needed.
c) Change source data for all graphs to include the expanded data range.
– Click inside the plot area of a graph.
– Right click and choose “Source data”.
– Go to the “Series” tab and scroll through the series to find the one that needs to be changed.
– Change the last cell reference to include as many cells as needed.
— In the weight graph, change the “Weight” series.
— In the withers height graph, change the “Withers Height” series.
— In the hip height graph, change the “Hip Height” series.
– Caution: changing these things may cause the macros to stop working. Do not attempt to make these changes if you are not reasonably skilled at manipulating Excel data. If you need assistance, contact Coleen Jones. - Data for recommended weights and heights can be viewed on the Summary Table worksheet or by clicking on the “Recommendations” tab.
– Standards are based on the results of measuring a large number of heifers of various breeds throughout the United States.
– The Holstein population was part of a national USDA study conducted during 1991 and 1992. Most of the data was collected from herds in which the average age at first calving was slightly greater than 24 months. As a result, the heifers probably were smaller for any given age than those found on today’s well-managed dairy farms that calve heifers at 22 to 24 months of age.
– Holstein growth standards are based on the median, 75th percentile, and 95th percentile of the national population.
– For all other breeds, the recommended growth ranges shown are the population mean plus one standard deviation.
– Hip height recommendations for Holstein heifers are also presented. These data are a compilation of USDA and Pennsylvania data sets and are available for Holstein heifers only.
— The range represents the population average plus one standard deviation above the average.
— These recommendations are based on a much smaller number of observations and, therefore, should not be interpreted as absolute standards. - We suggest saving each measurement date as a separate file for future reference.
– It also may be useful to use the measurement date in the file name, for example measurements taken May 15, 2002, might be saved in a file called “051502 Group Growth.xls.” This would be especially helpful if the Group ADG Monitor file is used to evaluate gains over time. - Any worksheet may be printed by choosing “print” from the file menu.
Group ADG Monitor
- Single file applies to all breeds.
- Calculates ADG between two measurement dates for multiple animals.
Instructions
- Open a new Group ADG Monitor file.
– A dialog box will open automatically with a message about macros. Choose to “enable macros”. If you choose “disable macros”, the spreadsheets will not work. - Click on the “Input” tab.
- Enter the farm name (in cell C1) and two measurement dates (in cells C5 and F5).
- Enter identification name or number, birth date, and body weight on the two dates for each animal.
Notes on entering data:
– If growth measurements previously have been entered into a Group Growth Monitor file and saved by measurement date, data can easily be found and copied into the ADG Monitor file.
— Sort data for both dates by ID number or name.
— Copy columns from the Growth Monitor file into the ADG Monitor file.
— Be sure to match information using ID number or name.
— Remember to paste in several steps to avoid copying over formulas.
— Type over any example data present in the file. If you do not, the example will be included with your heifers and will affect your results.
— Enter data in cells containing blue text. Do not enter anything in cells with black text; these are formulas and changing them may cause them to stop working.
— You must enter an ID name or number and a group name or number for each heifer if you want to separate results by group. - Update the summary table by holding down “control” and typing “t”.
- View the table by clicking on the “Summary Table” tab.
– Notes:
— Animals that were measured on only one of the two dates are automatically excluded from calculations of group averages.
— Animals that changed groups between measurements will be included in the average for their group on the second measurement date (that is, group averages are calculated for groups as of the second date). - Any worksheet in the file can be printed by choosing “print” from the file menu.
Individual Growth Monitor
- Available for Holstein, Jersey, Brown Swiss, Guernsey, Ayrshire, and Milking Shorthorn.
- Compares growth of an individual animal to recommended breed standards over her lifetime.
Instructions
- Open a new Individual Growth Monitor file.
– A dialog box will open automatically with a message about macros. Choose to “enable macros”. If you choose “disable macros”, the spreadsheets will not work. - Click on the “Input” tab.
- Enter the animal’s identification information.
- Add growth measurements over time as they are taken.
Notes on entering data:
– Type over any example data present in the file. If you do not, the example will be included with your heifers and will affect your results.
– Enter data in cells containing blue text. Do not enter anything in cells with black text; these are formulas and changing them may cause them to stop working. - Prepare data for graphing by hiding blank rows; hold down “control” and type “h”.
- View graphs by clicking on the appropriate tabs at the bottom of the window.
– Note: If the line for “Your Heifers” shoots off the scale or has extreme curves in it, see Step 6 under the Group Growth Monitor instructions. - Before entering new observations, all hidden rows must be revealed; hold down “control” and type “u”.
- Any worksheet in the file can be printed by choosing “print” from the file menu.
Repeated
- Currently available for Holstein and Jersey only.
- Provides information about growth of multiple animals at a specific age over a period of time. This information differs from information gleaned from comparing the graphed results of two measurement dates. If the two graphs are compared, information about growth at specific ages over time can be seen. However, the advantage of the two-graph method is that management and feeding changes also can be detected and evaluated.
Instructions
- Measure and record heifer body weight, withers height, and/or hip height.
- Open a new Repeated file.
– A dialog box will open automatically with a message about macros. Choose to “enable macros”. If you choose “disable macros”, the spreadsheets will not work.
– If this dialog box does not open, you can change the default setting to disable macros. This allows you to choose whether or not to enable macros on a case-by-case basis (when you open the file). Here’s how: Open Excel and click on the “Tools” menu. Choose “Macro” and then “Security”. A window will open; click on the “Security Level” tab. You will see the three levels of security in Excel and their definitions. We recommend setting your security to “MEDIUM”. This option will allow you to choose between “enable” and “disable” every time you open a file containing macros. Choose “enable” to activate the macros. Note: if you choose “disable” the spreadsheets will not work properly. - Click on the “Input” tab.
- Enter the measurement date and the farm name (in cell C1).
- Enter identification information and growth measurements for each animal.
Notes on entering data:
– Type over any example data present in the file. If you do not, the example will be included with your heifers and will affect your results.
– Enter data in cells containing blue text. Do not enter anything in cells with black text; these are formulas and changing them may cause them to stop working.
– You must enter an ID name or number and a group name or number for each heifer if you want to separate results by group. -
Remove blank cells – they create problems for the graphs.
– Due to the spreadsheet’s ability to handle a large number of heifers, most users will need to update the graphs and table.
– This is a simple matter of using pre-programmed filters.
— Go to the Summary worksheet.
— Each column has a filter that allows you to choose which cells to view. Click on the arrow button in the first row of any column and a list of filter options will appear. To remove blank cells, scroll to the bottom of the list and choose “NonBlanks.”
— Note: If one column has more blank cells than another, you may have to apply filters to more than one column to view graphs correctly.
— To remove a filter, click on the arrow button and choose “All.” - Update the Table worksheet
– Go to the Table worksheet and click anywhere inside the first table.
– Go to the “Data” menu and choose “Refresh Data” (the red exclamation point icon). This icon also may appear on the Pivot Table toolbar. - View graphs by clicking on the appropriate tabs at the bottom of the window.
– Note: If the line for “Your Heifers” shoots off the scale or has extreme curves in it, see Step 6. - The spreadsheet is currently set up to handle a maximum of 100 heifers measured 6 times. Users can expand this number, but will have to make a few changes to maintain functionality.
Complete the following steps:
– Unprotect the worksheet(s) that need to be changed.
— Go to that worksheet, then click on the “Tools” menu and choose “Protection”. Then choose “Unprotect Sheet.”
– On the Input sheet, copy formulas down the column in all columns that calculate age.
— Add measurement dates if needed; be sure to check formulas.
– On the Summary sheet, add formulas that will transfer the new information from the input sheet. Use existing formulas as a guide.
– Change the range of cells included in the summary table (it is a pivot table).
— Click on a cell inside the table.
— Right click and choose “Wizard”.
— Click on the back button to access the data range.
— Change the last cell reference to include as many cells as needed.
– Change source data for all graphs to include the expanded data range.
— Click inside the plot area of a graph.
— Right click and choose “Source data”.
— Go to the “Series” tab and scroll through the series to find the one that needs to be changed.
— Change the last cell reference to include as many cells as needed.
— In the weight graph, change the “Weight” series.
— In the withers height graph, change the “Withers Height” series.
— In the hip height graph, change the “Hip Height” series.
Caution: changing these things may cause the macros to stop working. Do not attempt to make these changes if you are not reasonably skilled at manipulating Excel data. If you need assistance, contact Coleen Jones. - Data for recommended weights and heights can be viewed on the Table worksheet or by clicking on the “Recommendations” tab.
– Standards are based on the results of measuring a large number of heifers of various breeds throughout the United States.
– The Holstein population was part of a national USDA study conducted during 1991 and 1992. Most of the data was collected from herds in which the average age at first calving was slightly greater than 24 months. As a result, the heifers probably were smaller for any given age than those found on today’s well-managed dairy farms that calve heifers at 22 to 24 months of age.
– Holstein growth standards are based on the median, 75th percentile, and 95th percentile of the national population.
– For all other breeds, the recommended growth ranges shown are the population mean plus one standard deviation.
– Hip height recommendations for Holstein heifers are also presented. These data are a compilation of USDA and Pennsylvania data sets and are available for Holstein heifers only.
— The range represents the population average plus one standard deviation above the average.
— These recommendations are based on a much smaller number of observations and, therefore, should not be interpreted as absolute standards. - Any worksheet may be printed by choosing “print” from the file menu.
Reading and Interpreting Results
All graphs show both recommended weight and height and the actual weight and height of heifers entered in the spreadsheet. Holstein graphs display three recommended lines. The bottom line (green) represents the median of the population. The middle line (blue) represents the 75th percentile, and the top line (purple) represents the 95th percentile. An achievable goal is to keep a majority of heifers near the 75th percentile and the entire herd ranging between the median and the 95th percentile. Graphs for other breeds display only two lines. The bottom line (green) represents the mean, while the top line (blue) represents one standard deviation above the mean. An achievable goal is keeping a majority of heifers near the top of this range and the entire herd above the mean (bottom line).
Research has shown that heifers large enough to calve at 22 to 24 months old with acceptable levels of milk production must be near or above average in size. Keep in mind that producing extremely large heifers may not result in increased lifetime milk production. Growing extra large heifers also increases feed expenses, because daily energy requirements increase with body size. It also is important to realize that calving age is highly correlated to the profitability of the animal, and that body weight after calving (1,250 pounds for Holsteins) is highly correlated to first lactation milk production. The objective of a heifer-raising program is a relatively constant rate of growth. Rate of gain from 2 months of age to puberty should target 1.75 pounds per day. Growing heifers above or below this target will result in reduced milk production in the first lactation (and perhaps throughout her lifetime).
Holstein heifers should reach 750 to 800 pounds and 48 to 50 inches by breeding age (13 to 15 months). After calving, heifers should weigh 1,137 to 1,296 pounds and measure 52 to 55 inches at the withers. Recommended breeding heights and weights for all heifers are shown in Table 1.
| Breed | Body Weight (pounds) | Withers Height (inches) | Hip Height (inches) |
| Jersey | 525-575 | 43-45 | 45-47 |
| Ayrshire | 700-750 | 46-48 | 48-50 |
| Guernsey | 700-750 | 46-49 | 48-51 |
| Milking Shorthorn | 750-800 | 46-48 | 48-50 |
| Holstein | 750-800 | 48-50 | 50-52 |
| Brown Swiss | 750-800 | 48-51 | 50-53 |
Growth charts can help evaluate heifer management and can flag problem situations, such as entire groups of animals that are undersized, underweight, or overweight. Low- or poor-quality forages (hay and silage) generally are responsible for inferior growth performance in young heifers, but lack of a balanced grain supplement and overcrowded housing can add to the problem. Underfed calves do not receive adequate energy and protein to meet their growth requirements. Inadequate housing and ventilation can contribute to poor growth by causing subclinical respiratory problems, and can negate an adequate feeding program. The level of management and sanitation also can affect heifer growth.
Adequate weight but restricted skeletal growth may occur in a particular age group. Relying on poor-quality hay and corn silage for all or most of the heifers’ diet usually causes this problem. Both of these forages typically have low protein, mineral, and vitamin levels. Feeding cereal grains with very little protein, mineral, and vitamin supplementation along with these forages may exacerbate the problem.
Feeding 2 to 4 pounds of grain per day and balancing the diet for protein, energy, minerals, and vitamins can solve a feeding problem, often within several months. Rations must be more than adequate, however, if heifers are to compensate with increased growth rates.
Overweight animals with normal skeletal growth for a their age group are another common scenario. Although heifers as young as 6 months of age may appear fat, this problem is more common in breeding-age heifers. Marginal to adequate dietary protein combined with excessive energy intake most often causes fat heifers. This can result from feeding free-choice corn silage and moderate levels of supplemental grain. Problems generally arise on farms where good- to excellent-quality forages are produced and fed to heifers in unrestricted amounts. Restricting heifers’ intake of these high-quality forages usually corrects the problem.
Summary
Healthy, productive herd replacements are the result of good management that starts before calves are conceived and continues until they enter the milking herd. Proper attention to the entire process ensures that genetically superior animals with maximum potential for milk production enter the herd.
Visit Penn State’s College of Agricultural Sciences on the Web: http://www.cas.psu.edu/
This publication is available in alternative media on request.
The Pennsylvania State University is committed to the policy that all persons shall have equal access to programs, facilities, admission, and employment without regard to personal characteristics not related to ability, performance, or qualifications as determined by University policy or by state or federal authorities. It is the policy of the University to maintain an academic and work environment free of discrimination, including harassment. The Pennsylvania State University prohibits discrimination and harassment against any person because of age, ancestry, color, disability or handicap, national origin, race, religious creed, sex, sexual orientation, or veteran status. Discrimination or harassment against faculty, staff, or students will not be tolerated at The Pennsylvania State University. Direct all inquiries regarding the nondiscrimination policy to the Affirmative Action Director, The Pennsylvania State University, 201 Willard Building, University Park, PA 16802-2801, Tel 814-865-4700/V, 814-863-1150/TTY.
Where trade names appear, no discrimination is intended, and no endorsement by Penn State Cooperative Extension is implied.
Issued in furtherance of Cooperative Extension Work, Acts of Congress May 8 and June 30, 1914, in cooperation with the U. S. Department of Agriculture and the Pennsylvania Legislature. T. R. Alter, Director of Cooperative Extension, The Pennsylvania State University.
Authors
Coleen Jones
Jud Heinrichs
The Pennsylvania State University
Department of Dairy and Animal Science