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.

 

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