Excel Data Tutorials

Excel data tutorials and learning activities from the Power of Data course

Discovering the Power of Data

These Excel tutorials, and associated Learning Activities, are for anyone who wants to learn how to work with data and spreadsheet software. While these tutorials have been developed in a farm animal context, the information provided is applicable and accessible to everyone, no matter what your individual circumstances or desired professional/personal uses.

Learning Objectives

By engaging with these tutorials, you will learn:

How to organise data into a spreadsheet, including the different components of a well-managed spreadsheet
How to use Excel formulas to create simple summaries of data, including averages, sums, and how to easily and quickly find metrics such as minimums, maximums, ranges, and counts
How to manipulate data to produce rates or other more informative calculations
How to use pivot tables to quickly get a more in-depth look at your data to help identify patterns, trends, and relationships
How to create line graphs and bar graphs to understand relationships between variables, and monitor changes over time

Data 101: An introduction to record keeping with spreadsheets

This first data tutorial will help you familiarise yourself with the free, online version of Microsoft Excel, and will teach you the basics of using a spreadsheet to manage data.

After watching the above video, you can choose to expand the Learning Activity below, and see what sorts of data are recorded on-farm. You can add your own ideas, or you may prefer to simply think of some data you can record that would be useful to your own situation:

Learning Activity (click to expand)

Think of variables you would like to record on your own farm

Then click the green plus sign in the bottom right corner to add your ideas:

Data 102: How to perform simple calculations and data summaries

Once data are recorded in an online spreadsheet, it becomes fast and simple to perform basic but important summaries of the data, such as finding averages, minimums, and maximums. The following tutorial will show you how to do so:

Now that you have watched the demonstration, expand the Learning Activity below, and try this for yourself.

Access the free online version of Excel here, then click the "Sign up for free" button and follow instructions. If you are using Office365 and an app-based version of Excel on either a PC or Mac, the layout may be slightly different, but the processes will all be the same.

We provide you with a demo dataset you can first download, and then open with Microsoft Excel online. Use the dataset to answer the provided questions. Solutions are provided below, as is a step-by-step downloadable solutions guide.

Learning Activity (click to expand)

Using the given sample database below, answer the following questions:

Data Tutorial 102: Learning Activity Demo Data (Excel) (12.32 KB / XLSX)

Click the above link to download it to your computer. Instructions on how to then upload the file to your online OneDrive/Excel.

Q1: How many calves are in the record?

Q2: For birth weight, calculate:

a) Minimum birth weight
b) Maximum birth weight
c) Average birth weight

Q3: For weaning weight, calculate:

a) Minimum weaning weight
b) Maximum weaning weight
c) Average weaning weight

Q4: Calculate the weight gain for each calf over the pre-weaning period, and then find the average weight gain for all calves.

Solutions (click to expand)

Q1: How many calves are in the record?

100 calves

Q2: For birth weight, calculate:

Minimum birth weight: 37kg
Maximum birth weight: 52kg
Average birth weight: 44.057kg

Q3: For weaning weight, calculate:

Minimum weaning weight: 200kg
Maximum weaning weight: 340kg
Average weaning weight: 271.94kg

Q4: Calculate the weight gain for each calf over the pre-weaning period, and then find the average weight gain for all calves.

Average weight gain = 227.883kg

Download the step-by-step answer key to check your answers, help if you get stuck, or keep on-hand for future reference:

Data Tutorial 102: Learning Activity Answer Key (2.04 MB / PDF)

Using Excel formulas to calculate rates: Part 1

The following video will demonstrate how to calculate growth rates (i.e., changes in weight over a period of time) for individually weighed animals. The same approach can be used to calculate other rates - such as financial expenditures over time, energy usage, etc..

As with all the data tutorials, the process will be shown using the free online version of Excel. You can also use an app-based version of Excel on PC or Mac.

We suggest you make sure to have watched the data tutorials in Module 1 before beginning these ones, as they will show you the fundamentals of using spreadsheet software.

Learning Activity (click to expand)

Q1: Using the sample dataset from the previous module, calculate the daily liveweight gain for each animal.

Note: If you have not completed the Module 1 Data Tutorial learning activities, we recommend you do these first. However, if you prefer to skip them, you can download the dataset here:

Learning Activity: Calculating growth rates for cattle, pigs, and small ruminants Spreadsheet (12.32 KB / XLSX)

Click the above link to download it to your computer. Instructions on how to then upload the file to your online OneDrive/Excel.

Q2: Using the daily liveweight gain calculated in Question 1, find the:

a) slowest growth rate
b) fastest growth rate

Solutions (click to expand)

Q1. Using the sample dataset from the previous module, calculate the daily liveweight gain for each animal.

Please see the answer key below for the solution.

Learning Activity Answer Key: Calculating growth rates for cattle, pigs, and small ruminants (686.57 KB / PDF)

Q2. Using the daily liveweight gain calculated in Question 1, find the:

a) slowest growth rate 0.76kg/day
b) fastest growth rate 1.51kg/day

Using Excel formulas to calculate rates: Part 2

This video takes another look at using formulas to calculate rates. This video refers to a lecture on weighing by Dr Laura Dixon, but it is not necessary to watch this lecture in order to engage with this tutorial. By watching this video, you will learn how spreadsheet software makes calculations fast and accurate.

Learning Activity (click to expand)

Using the given sample database below, answer the following questions:

Learning Activity Spreadsheet: Use of weight data for broiler chickens and laying hens (11.71 KB / XLSX)

Click the above link to download it to your computer. Instructions on how to then upload the file to your online OneDrive/Excel.

Q1. Using the BULK weighing sheet of sample dataset, calculate the average weight for:

a) birds in Shed A
b) birds in Shed B

Q2. Using the INDIVIDUAL weighing sheet of the sample dataset, calculate:

a) the standard deviation of birds in Shed B
b) the average weight of birds in Shed B
c) the flock uniformity (coefficient of variance) of birds in Shed B

Solutions (click to expand)

Q1: Using the BULK weighing sheet of sample dataset, calculate the average weight for:

a) birds in Shed A 0.57065kg (or 0.57kg or 0.571kg)
b) birds in Shed B 0.57475kg (or 0.57kg or 0.575kg)

Q2: Using the INDIVIDUAL weighing sheet of the sample dataset, calculate:

a) the standard deviation of birds in Shed B 0.145kg
b) the average weight of birds in Shed B 2.37kg
c) the flock uniformity (coefficient of variance) of birds in Shed B 6.1%

For a more detailed explanation see the answer key below:

Learning Activity Answer Key: Use of weight data for broiler chickens and laying hens (659.7 KB / PDF)

Using Excel formulas to calculate rates: Part 3

The following tutorial provide a step-by-step explanation of how to monitor mortality data for a flock or herd. This will include calculating daily mortality rates, cumulative mortality, and total mortality. Again, the principles in the video are widely applicable, showing various Excel tips and shortcuts that can be used for many purposes.

As with all the data tutorials, the process will be shown using the free online version of Excel.

We suggest you make sure to have watched the data tutorials in Module 1 and 2 before this one, as they provide the foundations we are now building upon.

After watching this tutorial, you will have the skills to use Excel formulae to calculate useful metrics of your own data using free spreadsheet software.

If you are interested in using the exact formulas shown in this tutorial, you can download a template here, which comes with the formulas pre-populated:

Poultry Mortality Template (Excel) (12.19 KB / XLSX)

Learning Activity (click to expand)

In Module 1 you thought of some variables you would like to record. In Modules 2 and 3 you have learned how to use formulas to calculate new variables, such as growth or mortality rates.

Now, think of some other variables you can use formulas to calculate, that would be useful to keep track of.

You can share these in the Padlet below. If you like, include the formula or function you think you would use to calculate the variables:

Creating line graphs to monitor changes over time

While data contained in well-organised spreadsheets is useful, it can be easier to interpret data that have been visualised in graphs or charts. The following tutorial will show you how to create simple line graphs that will enable you to monitor changes in a variable over time. The video discusses mortality and growth rates, but the process is the same for visualising other changes over time, such as energy use, budgetary spending, etc..

Learning Activity (click to expand)

Using the given sample database below, answer the following questions:

Module 3 Learning Activity - Database (11.77 KB / XLSX)

Click the above link to download it to your computer. Instructions on how to then upload the file to your online OneDrive/Excel.

Q1: Using the provided database, make a line graph of Daily mortality rate (%) as a function of bird age in days. On what day was bird mortality the highest?

Solutions (click to expand)

graph about daily mortality in poultry from a dataset

Q1: The daily mortality rate was highest on day 19, when birds were 19 days old.

Download the step-by-step answer key to check your answers, help if you get stuck, or keep on-hand for future reference:

Creating Simple Visualisations: Learning Activity Answer Key (454.03 KB / PDF)

Creating pivot tables to understand relationships in your data

There are many situations in which it is useful to understand the impact of a predictor variable on a response variable. For example, many factors can influence the performance of animals individually, and collectively. Data can help us understand not only what is happening, but also, why. This tutorial will explain how to identify relationships between variables.

As with all the data tutorials, the process will be shown using the free online version of Excel.

We suggest you watch the data tutorials in the previous modules before starting this one, as they provide the foundations we continue to build upon.

Learning Activity (click to expand)

Create pivot tables to answer the following questions:

The provided database below contains data for 200 dairy cows. They are either bedded on straw or concrete, and either fed silage only, or silage plus grain. Each cow has been mobility scored from 0 (good mobility) to 3 (severely impaired mobility) and weighed, in kilograms.

Pivot Tables: Learning Activity Demo Data (14.96 KB / XLSX)

Click the above link to download it to your computer. Instructions on how to then upload the file to your online OneDrive/Excel.

Q1: Assess the impact of bedding type on mobility score: How many cows bedded on straw have each mobility score? On concrete? Which bedding type would you choose, based on these data?

Q2: What is the average weight for cows fed silage only? For cows fed silage and concentrates?

Solutions (Click to expand)

Q1: How many cows bedded on straw have each mobility score? On concrete? Based on these data, how does bedding type impact mobility score?

	Mobility Score
	0	1	2	3
Concrete	29 cows	28 cows	24 cows	19 cows
Straw	64 cows	33 cows	3 cows	0 cows

Therefore, these data showed that fewer cows bedded on straw had moderately- or severely-impaired mobility compared to those bedded on concrete.

Q2: What is the average weight for cows fed silage only? For cows fed silage and concentrates?

Silage only: average weight of 574.35kg

Silage and grain: average weight of 643.98kg

For a detailed explanation please see the answer key below:

Module 4: Pivot Tables Learning Activity - Answer Key (1.49 MB / PDF)

Visualising summarised data using bar graphs

In the previous video, you learned how to use pivot tables to assess the influence of independent variables, or predictor variables, on dependent, or response, variables. The following tutorial will show you how to visualise these relationships to make them easier to understand, interpret, and act upon.

Learning Activity (click to expand)

Using the pivot tables you made in the previous tutorial (see above), generate:

Q1. A 100% stacked bar graph showing mobility score as a function of bedding type.

Q2. A bar graph showing average weight as a function of feed type.

Solutions (click to expand)

Find the answers, and the step-by-step process, in the answer key below:

Module 4: Bar Graph Learning Activity - Answer Key (1018.81 KB / PDF)

When you are finished engaging with these data tutorials, please take a moment to fill out this post-course questionnaire to provide feedback and be awarded your digital badge:

Course Feedback Form

This article was published on 2024-09-02