Dr. Mark Gardener

Home
About
Home > Publications > Managing Data Using Excel: Example files

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.

There is also a list of keywords at the end (this is comprehensive but not complete). See also the outline and table of contents page.


Outline & TOC

Top

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.


Top

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.


Abbreviations.xlsx

Top

Abbreviations

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.


airquality.xlsx

Top

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.


Aspect.xlsx

Top

Aspect

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


Barley.xlsx

Top

Barley

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.crtx

Top

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.

Windows

  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

Macintosh

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

Top

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

Top

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.


DAFOR.xlsx

Top

DAFOR

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

Top

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

Top

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

Top

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.


HairEyeColor.xlsx

Top

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.xlsx

Top

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

Top

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.


mtcars.xlsx

Top

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.


NoLine.crtx

Top

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.


NYenvironmental.xlsx

Top

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.


Pottery.xlsx

Top

Pottery

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.xlsx

Top

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.


UKgas.xlsx

Top

UK Gas usage

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


Validation.xlsx

Top

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.


warpbreaks.xlsx

Top

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.


women.xlsx

Top

Height and Weight

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


   

All support files as a ZIP archive

Top

Downloads

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).


My Publications

Follow me...
Facebook Twitter Google+ Linkedin Amazon

See also:
Excel Tips & Tricks
Writer's Bloc

Keywords:

Here is a list of keywords – it is by no means complete!

Data, Average, Mean, Median, Mode, Standard Deviation, Standard Error, Range, Max, Min, Inter-quartile Range, IQR, Kurtosis, Skewness, Distribution, Histogram, Normal distribution, parametric, non-parametric, bar chart, column chart, box-whisker plot, boxplot, dot chart, dotchart, scatter plot, line plot, pie chart, Pivot Table, Pivot Chart, Filter, Advanced Filter, Sort, Data Validation, Scientific recording format, sample format, biological recording format, contingency table, long records, short records


Top DataAnalytics Home
Publications
Contact GardenersOwn Homepage