Microsoft Excel

Using Microsoft Excel Pivot Tables

In preparing my Sunday Afternoon Genealogy Fun – Middle Names post, I provided the following statistics:

So we have in these names:

  • 2 where middle name is father’s middle name
  • 2 where middle name is father’s given name
  • 2 where middle name is grandfather’s given name
  • 1 where middle name is grandmother’s given name
  • 2 where middle name is mother’s given name
  • 1 where middle name is mother’s middle name
  • 1 where middle name is mother’s surname
  • 9 where middle name is aunt/uncle’s given name
  • 1 where middle name is a cousin’s given name

So out of 53 individuals, 21 had a middle name from a family member, 14 had no middle name and 18 had unique middle names not in recent family members.

To get these figures, I could have counted the number of each category, but I decided to use a Pivot Table in Microsoft Excel.

Firstly, I copied the details from my post into Excel, and deleted the empty lines between family groups. I then added a column to indicate which category each name fell under.

Excel table

Then I selected all the entries in my “Middle name from” column, and set up the Pivot Table.  To do this I selected Insert from the Menu, and Pivot Table from the Insert Ribbon.

PivotTable

From there, you can either set up the Pivot Table on a new worksheet, or on the existing worksheet.

On the Pivot Table fields, there is only the one field – the “Middle name from” field.  When the box next to the field is checked, this field is added to the Rows.

Row Labels

To get the count of each, you drag the “Middle name from” in the top section into the Values box.

Count

In this example, if you’re not that familiar with Excel it might be quicker to do a manual count.

However, if you have more data you are wanting to analyse, the Pivot Table feature in Excel makes the task so much quicker, and reduces the risk of making an error, than by performing a manual count.