Dr. Mark Gardener
Dr. Christine Gardener

Gardeners Own Home
Education Home
Other Publications
About Us

Managing Data Using Excel

Organizing, Summarizing and Visualizing Scientific Data

by: Mark Gardener

Available now from Pelagic Publishing.

On this page find details about the example data used in the book. You can download the data, including some chart template files, referred to in the text.

See also the outline and table of contents page.


Publications page

Managing Data Using Excel: Support Files

Throughout the book there are examples of data. Most of these are also used in the practical Have a Go exercises.

Example files Notes | Downloads

Each file is mentioned in the text, either as a Have a Go exercise or as an example file to use in the self-assessment questions at the end of the chapters. There is a brief description of the data file each time it is mentioned. There are three kinds of data file:

  • Dataset – Excel spreadsheets that are complete data. You can use these for practice for various tasks.
  • Spreadsheet – basic spreadsheets that are not datasets but which demonstrate something specific that is mentioned in the text, such as the use of lookup tables or a particular function.
  • Chart Template – Excel 2010 chart templates that you can use to help format your own charts. These should work in 2007 and 2013.

The notes that follow give you a brief overview and the links in the sidebar allow you to download the separate files. You can also obtain all the files together in a ZIP archive in the downloads section.


Example data: Notes

Here you can find some details about the example files. You can download the files individually (using the links in the sidebar) or together as a ZIP archive from the downloads section. The following sections give notes about each file in alphabetical order.


This spreadsheet shows how you can use simple text functions to create abbreviated species names. There are three worksheets, the first is a simple list of secies names for you to practice on. The other two worksheets show simple and more complex abbreviations.



Air Quality

This dataset gives values for environmental variables in relation to ozone concentration in New York. The variables are: ozone, (solar) radiation, (air) temperature, and wind speed. The month and day of each observation is also given.

These data are essentially the same as the NYenvironmental dataset but with the date information.




This spreadsheet shows you how to convert a compas bearing for aspect into other useful measures such as: compass quadrant, northness and eastness.




This dataset gives values for the yield of varieties of barley for two years (1931 and 1932). For each year 10 varieties of barley were grown at 6 different sites. A second worksheet contains the same data but with examples of "useful" index variables, which allow the dataset to be managed more effectively.



Boxplot Template

This file is an Excel chart template. It was created in Excel 2010 but should work in both 2013 and 2007. Use this template to help formatting when making a box-whisker plot. You need to start by using a Stock chart (Open High Low Close type). Then use the template to apply appropriate formatting to the chart.

The making of box-whisker plots is demonstrated in Chapter 8.

The instructions for getting the template into your copy of Excel (for Windows) are shown in the book. However, here is a brief summary of how to get the template into Excel for Windows and Macintosh users.


  1. Open Excel and click on the Insert ribbon menu.
  2. Click the Other Charts button then choose All Chart Types.
  3. The Insert Chart dialogue window should be open; click the Manage Templates button.
  4. The appropriate folder opens in Windows Explorer; find the .crtx file you downloaded and place it into this window.
  5. Close the Explorer window when you are done and your template should be available immediately.

The folder path (for Win7 and Office 2010) looks something like: YourName > AppData > Roaming > Microsoft > Templates > Charts


You cannont access the chart templates folder from within Excel itself and will have to use Finder. I have only tested this on Excel 2011 using Mavericks.

  1. Click anyplace on the desktop so you are using Finder.
  2. Click Go on the menu toolbar to bring up a list of folders.
  3. Hold the Alt (Option) key and you will see the Library folder appear.
  4. Click the Library folder to open a new Finder window.
  5. Navigate to Application Support > Micorsoft > Office > Chart Templates
  6. Now you can place the downloaded .crtx file into this folder.
  7. The chart should be "visible" to Excel immediately – Charts > Other
  8. The Other window shows various chart types, scroll to the bottom to see the template(s).

Braun Blanquet.xlsx


Braun Blanquet

This spreadsheet shows how you can use a lookup table to convert a percentage value into a Braun-Blanquet scale equivalent. The proceedure would work for any ordinal scale such as Domin or DAFOR.

Butterflies and Site Management.xlsx


Butterflies and Site Management

This dataset shows the abundance of several sorts of butterfly species at a nature reserve. Data cover several years. There are five transects and each is split into sections. Some of the sections are managed (grazing) and others not.

Essentially the dataset looks at differences but the yearly data means that there is a time element.




This spreadsheet demonstrates how to take a text value from an ordinal scale, such as the DAFOR index, and return a numerical value. It is a simple use of a lookup table.

Date and time.xlsx


Date and Time

This spreadsheet demonstrates how to use various date and time functions, including how to calculate the Julian day for any date. These are covered in Chapter 2.

Error Checking.xlsx


Error Checking

This dataset contains some errors and is designed to accompany an exercise on error checking. There are several variables, species name, common name, quadrat number, Domin score and site name.

There are some spelling errors in the species names and some mistakes in the Domin scores. A second worksheet contains the same data but with the errors fixed.

Fly wings.xls


Fly Wings

This dataset shows the size of fly wings when fed a variety of sugar diets. There are two worksheets, one shows the data in sample format, the other in scientific recording format.



Hair and Eye Colour

This dataset shows the frequency of hair colour and eye colour for male and female students. The data can be used for association analysis and the file is used to demonstrate how to use a Pivot Table as well as various graphics.



Jackal Mandbles

This dataset shows the lengths of the lower jaws of several male and female golden jackals. There are two worksheets, one shows the data in sample format, the other in scientific recording format.

Mangrove Fungi.xlsx


Mangrove Fungi

This dataset shows the frequency of several polypore fungi species in three types of mangrove habitat in the West Indies. There are three worksheets, each showing the same data in a different form:

  • Long records – each row is a single observation (so there are no direct frequencies).
  • Short records – each row is a unique combination of species and habitat, the frequencies have been calculated.
  • Contingency table – the frequencies have been cross-tabulated into a completed contingency table.

The data can be used for association analysis. The dataset is used for showing how to use Pivot Tables and Pivot Charts.



Motor Trade Cars

This dataset shows several cars (from 1974) and for each one there are several performance related variables, such as number of cylinders, horsepower and qtr-mile time. There are two worksheets, the second shows the same data as the first but has some additional index variables.



Dot Chart Template

This file is an Excel chart template. It was created in Excel 2010 but should work in both 2013 and 2007. Use this template to help make a dot chart. Essentially a dot chart is a line plot, without the line!

Dot charts are demonstrated in Chapter 3 when they are used to help spot numerical errors in your data.

The template should work for Windows and Macintosh versions of Excel. See the boxplot template details for some instructions about getting the templates into your version of Excel.



New York Environmental data

This dataset shows the levels of ozone in New York over a period of time. Other variables are (solar) radiation, (air) temperature and wind speed.

The dataset is essentially the same as the airquality dataset but without the actual dates.




This dataset shows the chemical composition of various pottery sherds found at a number of sites (there are 4). Each sherd contains Al, Fe, Mg, Ca and Na. There is a column for each chemical and an index variable.



Titanic Survival

This dataset shows the number of passengers and crew that survived (or not) from various classes (1st, 2nd, 3rd, crew). There are separate data for males and females as well as their age (adult or child). Data are in short format, with a column for frequency.



UK Gas usage

This dataset shows the gas used in the UK for quarters during the years 1960 to 1986.



Data Validation

This spreadsheet is used to demonstrate how to use data validation tools. There are two worksheets; the first shows an entry sheet where input criteria are used on three variables.

The second worksheet is a lookup sheet containing "valid" list entries for two of the variables. This will probably not work for Excel versions prior to 2010 because data validation in earlier versions could not refer to secondary worksheets.



Warp Breakage

This dataset shows the number of breaks per loom for two sorts of woolen yarn under three different tensions. There are two worksheets, the second is the same as the first but includes additional index variables.



Height and Weight

This dataset shows the height and weight of a sample of women.

All support files as a ZIP archive



You can download individual files from the links in the preceeding sections. Alternatively you can download everything as a single ZIP archive file. (Available here).

Publications page

Back to top

My books on ecology and data analysis

Statistics for Ecologists is available now from Pelagic Publishing. Get a 20% discount using the S4E20 code!
Beginning R is available from the publisher Wrox or see the entry on Amazon.co.uk.

The Essential R Reference is available from the publisher Wiley now. See it also on Amazon.co.uk.
Community Ecology is available now from Pelagic Publishing.
Managing Data Using Excel is available now from Pelagic Publishing. Get £5 discount using MDUE20 code!

Managing Data Using Excel, Cover


See also...

Learn to use R for statistical analyses: Index page

Gardeners Own Home
Education Home
Other Publications
About Us