New Views of Your Ancestors in Spreadsheets and Maps
by Jennifer Holik | Jan 24, 2013
As a genealogist, do you always know exactly what information you have, or do not have, for each person within your database? Can you quickly gather information to prepare for a research trip? Does your genealogical database present the information to you in a way that fits your needs? Do you sometimes just need to look at the data in a brand new way to fully understand what it is saying or what you are missing?
If you answered "yes" to any of those questions, it is time to start thinking outside the box and outside your genealogy database. When it comes to analyzing your data, use more than your database to find the answers you seek. In this article we'll explore ways to use spreadsheets, such as Microsoft Excel, and maps to move your research forward.
You might associate spreadsheets with accountants, but you don't need to do any math for them to be useful. Use your spreadsheet, whether it's Microsoft Excel, Numbers (for Mac OS), the spreadsheet function in Google Drive, or any other spreadsheet program to sort and rearrange your data.
My great-great-grandparents Joseph and Magdalena Kokoska had 10 living children. I wanted to make sure I had every census record possible for each member of the family. I also wanted to examine other records that contained addresses for the family so I could track each child in the city and see where they lived in relation to their parents after they grew up, married and moved out of the family home.
I created an Excel file that contained the names of each family member in one column and a column for each census year. I entered the addresses I had from the census records. Additionally, I added columns for years I had an address found on a vital record. When one of the family members died, I greyed the future boxes to remind me not to look for their records. As I entered the information and began looking at it I noticed a few things.
First, I was missing the 1920 census for most of the family. Second, questions arose. A couple of the grandchildren had Joseph's address as their birth location. Did that mean that child and his family lived with Joseph and Magdalena at that time? Or did that mean the daughters and daughters in-law came to the home to give birth?
Pulling the data out of my database and arranging it this way answered some questions, allowed me to narrow down a ward and enumeration district to search for the 1920 census, page by page. It also raised some questions that are worth investigating.
Do you ever find yourself thinking on a gorgeous day that you should visit the cemetery and look for your ancestors? Can you quickly pull together a report so you know who you should look for or what information you already have?
Create a custom report in your genealogy database program to export to Excel. Many have similar features; the following is how I did it using Family Tree Maker.
- Select Publish in FTM. Choose Person Reports. Then select Custom Report.
- Enter a report title (optional).
- Select All Individuals under "Individuals to include."
- I make sure the Background is set to "none."
- Select the fields you want in the custom report. Surname, Given Name, Burial, Date of Birth, Date of Death, and Grave Location (this may be the description field in your burial fact). I include Date of Birth because it is sometimes helpful to know approximately how old the person was if only a burial date and age are listed on a cemetery record.
- Add fields by hitting the + sign.
- Click the field name to add it to the report.
- Click OK and then the Share icon.
- Select "Export to CSV."
- Select "Export data as columns."
- Hit "Export" and open the report.
Viewing the information in your spreadsheet, you can sort it a variety of ways. For instance, you can sort by cemetery and then create a tab for each cemetery listed in the database. To move the information, copy the rows from the main tab and paste them into the appropriate cemetery tab. From here you can sort by surname or grave location (burial description) to see how the information shakes out.
Viewing the data this way shows me where gaps occur. When I went through this process I noticed I had a husband listed at a cemetery with a grave location but his wife is not listed. Why? Did I miss her at the cemetery? Did I see her and not enter the information into her record? Is she buried somewhere else?
Using this system I can go back to the main tab of exported data and sort by surname. Then I can copy rows of information into the cemetery tab where I think it belongs or into a new tab for information I need to locate. I can print the report and take it to the cemetery or put the file on my phone, laptop, or iPad and update it at the cemetery. When I return home all I have to do is enter the new information in my database.
Maps are a valuable tool in family history and can be used to search for the place from which you ancestor came and illustrate a point you want to make in your research and writings. Here are three ways maps can help you see your data in a new way.
Illustrating the relationships between families
After recording all the Kokoska addresses in Excel and locating the families, my goal was to look at relationships between families. At the turn of the century, many of my families lived near one another. A child grew up, married, and typically moved within a street or few blocks from the parent's home. Was this true for all of the Kokoska children?
I started with my great grandfather Joseph Kokoska and compared where he lived in relation to his future wife Bessie Zajicek. I located a map of Chicago around 1928 and was able to plot out where the two families lived in relation to one another. I also plotted where the couple then lived after they married. This gave me a visual example of how the families had a close relationship. These maps can then be added to the story I write about these families.
Research property history
Locating maps that illustrate what a neighborhood or family home looked like during a given time period is one facet of property research. When we visualize the home or neighborhood, it helps bring our ancestors to life.
The Sanborn Fire Insurance Maps show the layout of buildings on a piece of property. They give a description of what the buildings are made of and how over time, they were modified. You need to look at several years' worth of Sanborn Maps to see how a piece of property changed. Use these maps to help tell the story of where your ancestors lived.
Illustrate military movements
Maps provide a great visual to accompany text in our family history writing, even when we do not have all the information on a person or family.
I wrote a story about my great grand uncle Michael Kokoska who died in France during World War I. Michael served in the 32nd Infantry Division, 127th Infantry, Company L. Unfortunately, when I began writing his story I had very little military information to use because his records burned at the National Personnel Records Center. I did however, have his burial file which gave me his death location. I read the 32nd Division Unit Histories and the World War I Order of Battle Books and had an idea of where his unit served as a whole.
I created a map with a timeline of the service of Michael's company during the dates he served. This finished map showed where his unit was from the time they disembarked in France to the time they were in the Alsace region where he was killed. This map not only makes a great addition to his story, but also helped create a research plan so I could dig deeper into his military service story. (You can read more about researching servicemen in "Telling the Stories of the Lost: Remembering Fallen Service Members.")
Experiment with spreadsheets and maps as a way to locate gaps in your data, create research plans to help move your research forward, and write your family's stories. Examining the data outside the box you may discover a goldmine of information.
Find Records Now for Free
Start your free trial today to learn more about your ancestors using our powerful and intuitive search. Cancel any time, no strings attached.