Microsoft Excel

Using Microsoft Excel to analyze family history data

Recently I set up a post about my immigrant ancestors, where I gave some statistics about when and where my ancestors arrived and where they came from.

In order to compile this information, the easiest tool to use was Microsoft Excel.

The first thing I did was go through my family history and identify which ancestors had immigrated to Australia, and then entered the information about their immigration in an Excel spreadsheet

Name Country of origin Year of arrival Assisted or unassisted State arrived in Additional details Family group Which side Where died
John Hooton England 1840 assisted Victoria remained in Victoria Family Husband Victoria
Eliza Patchet England 1840 assisted Victoria remained in Victoria Family Husband Victoria
Joseph Whimpey England 1841 assisted Victoria went to South Australia, then returned to Victoria, Couple My Victoria
Margaret Price Wales 1841 assisted Victoria Couple My Victoria
Alfred Docwra England 1848 assisted Victoria remained in Victoria Single My Victoria
James Marr Scotland 1849 assisted Victoria remained in Victoria Family My Victoria
Martha Richardson Scotland 1849 assisted Victoria remained in Victoria Family My Victoria
William Reeves England 1849 assisted Victoria remained in Victoria Single My Victoria
Frederick Oliver England 1849 assisted Victoria remained in Victoria Family My Victoria
Susannah Campkin England 1849 assisted Victoria remained in Victoria Family My Victoria
George Oliver England 1849 assisted Victoria remained in Victoria Family My Victoria
George William Willis England 1849 assisted New South Wales moved to Victoria Single Husband Victoria
Mary Scott England 1851 assisted Victoria remained in Victoria Single My Victoria
Jacob Thomas Alexander Mulder Netherlands 1851 unassisted South Australia moved to Victoria Couple Husband Victoria
Louisa House England 1851 unassisted South Australia moved to Victoria Couple Husband Victoria
Robert Harley Scotland 1852 assisted Victoria remained in Victoria Family My Victoria
Euphemia Dandie Scotland 1852 assisted Victoria remained in Victoria Family My Victoria
David Clyne Scotland 1852 assisted Victoria remained in Victoria Single Husband Victoria
Mary Gill England 1853 unassisted South Australia went to Victoria Single My Victoria
Mary Scott England 1853 unassisted South Australia went to Victoria Single My Victoria
Anne Marshall Ireland 1853 assisted New South Wales moved to Victoria Single Husband Victoria
Thomas Laver England 1853 assisted New South Wales moved to Victoria Family Husband Victoria
George Laver England 1853 assisted New South Wales moved to Victoria Family Husband Victoria
Elizabeth Male England 1853 assisted New South Wales moved to Victoria Family Husband Victoria
Mary Ann Jones England 1853 unassisted Victoria remained in Victoria Couple Husband Victoria
Ann Slawson England 1854 assisted Victoria remained in Victoria Single My Victoria
William Edward Ball Sweden 1855 unassisted Victoria remained in Victoria Single My Victoria
Jacob Theisinger Germany 1855 unassisted Victoria remained in Victoria Family Husband Victoria
Catherine Lepper Germany 1855 unassisted Victoria remained in Victoria Family Husband Victoria
Garrett John Russell Ireland 1856 unassisted Victoria remained in Victoria Single My Victoria
Susan Greene Ireland 1856 unassisted Victoria remained in Victoria Family My Victoria
Thomas Henry Russell Ireland 1856 unassisted Victoria remained in Victoria Family My Victoria
Mary Moore Ireland 1856 unassisted Victoria remained in Victoria Family My Victoria
Dora Mansfield Ireland 1857 unassisted Victoria remained in Victoria Family Husband Victoria
John Mansfield Ireland 1857 unassisted Victoria remained in Victoria Family Husband Victoria
Mary O’Keefe Ireland 1857 unassisted Victoria remained in Victoria Family Husband Victoria
Hans Hansen Denmark 1859 unassisted Victoria remained in Victoria Single Husband Victoria
Isabella Kerr Scotland 1859 assisted Victoria remained in Victoria Family Husband Victoria
Ralph Kerr Scotland 1859 assisted Victoria remained in Victoria Family Husband Victoria
Margaret Kerr Scotland 1859 assisted Victoria remained in Victoria Family Husband Victoria
David Clyne Scotland 1860 unassisted Victoria remained in Victoria Family Husband Victoria
Elizabeth Sinclair Scotland 1860 unassisted Victoria remained in Victoria Family Husband Victoria
Thomas Doyle Ireland 1862 unassisted Victoria remained in Victoria Family My Victoria
Catherine Mills England 1862 unassisted Victoria remained in Victoria Family My Victoria
Maria Louisa Doyle England 1862 unassisted Victoria remained in Victoria Family My Victoria
James Smith Scotland 1862 unassisted Queensland moved to Victoria Single Husband Victoria
Henry Sharp England 1863 unassisted Victoria remained in Victoria Family My Victoria
Jane Elizabeth Oldroyd England 1863 unassisted Victoria remained in Victoria Family My Victoria
Sarah Jane Sharp England 1863 unassisted Victoria remained in Victoria Family My Victoria
Catherine Marshall Ireland 1868 unassisted Victoria remained in Victoria Single Husband Victoria

 

In order to generate the statistics based on this information, I used Pivot Tables.  To generate a pivot table, select the table, and select Insert from the menu, and then Pivot table from the menu.  You can then select where you want to place the pivot table, either on an existing spreadsheet or a new spreadsheet.

The first statistic I wanted to look at was the Country of Origin, so in setting up the pivot table I chose Country of Origin in Rows, and then Count of Name in Values.

Row Labels Count of Name
Denmark 1
England 23
Germany 2
Ireland 10
Netherlands 1
Scotland 11
Sweden 1
Wales 1
Grand Total 50

 

This information can then be displayed as a chart.  Select the Pivot table, and then choose Analyze from PivotTable Tools, and then select PivotChart.  You can then decide whichever type of chart you prefer to use.  I chose a pie chart.

image001

To generate a pivot table for each of the other statistics, I just changed the field in the Rows sections.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s