Using Excel in Genealogy

Several times during #genchat, I have mentioned that I use Excel heavily as a genealogy tool. 

And it seems every time it comes up, someone asks, "how?" I decided to put together a post about how I use Excel to answer those questions. Some may use it differently than I, but for me, this works. What I have included here are just some initial thoughts on this tool.


When I start a new project, I almost always immediately create a workbook for that project in Excel. Each tab is a piece of the project, so if you are unfamiliar with using Excel, you should start with a good how-to video from YouTube or a training class. My Excel skills are still on the beginner level in my mind, but I know enough to make it work for me.


Step 1:

Create workbook. This can be as simple as opening a new file and giving it a name, but there are times when I get a bit more creative and give it a cover page, too. This also helps when working in public spaces; if I have to step away from my computer for any more than a second, I flip it over to the cover page so no one can see any of the information I have compiled. This works well for me if I am engaged on a client project, where confidentiality is important.


To do the cover page, tab 1 in the spreadsheet gets a nice big title, usually with my name and an image. Sometimes this is my company logo, sometimes this is a family photo, or perhaps a photo of the area/town I'm working on. I remove all the grid lines, so it's just one white page.


Here is an example of one of my cover pages:

Cover Page for my Breckenridge One Place Study
©Jen Baldwin, 2013 - 2014

The tabs can be whatever you need them to be. They can be labelled for each individual person, creating a nice timeline, or they can be different aspects of an individual life. For my not-quite-yet-official One Place Study, my tabs are items like, "Churches," "Obits," "Mining Claims," "Businesses," "Fraternal Orgs," and so on; this creates a timeline of the entire community, not just one person.


Step 2:

I usually do the rest as I work. I start with a basic set of column headings on each tab, and adjust as I need throughout the process. Often, any given spreadsheet ends up with column headings like this:


Year |  Date |  Surname | Given Name | Fact/Finding | Notes | Images | File Name | Publication | Pub Date | Author | Citation Detail |


I always do the year first, as a separate column, so I can sort easily when needed. Also, sometimes the date is separated into two columns: month and day, depending on what I am working on, and again for easy sorting. When you create dates in Excel, it's important to remember that you need to tell the system how you want it to be displayed. For example, the pre-formatted standard is to put dates in the following format: dd-mm-yy. That doesn't necessarily work for me, mostly because the research I conduct could be in the 1800s or the 1900s, so I need the full year. Also, my habit is to put dates into the dd mmm yyyy format, so each new date column has to be formatted correctly for easy use.


Thanks to the flexibility of the rules at our local Courthouse, which is where I do a lot of my research, I am allowed to take photos of the documents. That means that nearly every spreadsheet has a place to indicate if I took images, made photocopies, or "other," and a place to include the file name. The goal, of course, is to ensure that nothing gets missed or lost in the transfer of information into the spreadsheet, or the digital copy.


Every workbook and sheet includes a place for Notes. For me, this is essential. Perhaps I need to note that there are comments in the margin, or some small detail that stands out and could be important later, so the notes column is included on every sheet. Not all information fits easily into a column or cell, you need to give yourself the room for free form text.


The top row is always locked, so no matter how deep the spreadsheet gets, the column headings are visible. I also add some color in, to break up the text a bit, and the header is usually in color, as well. To change them around, adding variety and "spice" to my projects, I will sometimes select a theme to work with, so I have different fonts and colors in each workbook.


Here is an example of a sheet from my Breckenridge work book.

Fraternal Sheet, Breckenridge One Place Study
©Jen Baldwin, 2013-2014

You  can see I've highlighted two cells, and that is because the information provided differentiates: they each give a different lodge number for the A.F. & A.M. Masonic Lodge in Breckenridge. This could be a type-o, but it deserves my attention later in the research process.


Other Elements and Citation Templates

Continuing to use my One Place Study as an example, I have tabs, or sheets, for all sorts of elements. One is a census tab, so I have all the Federal and State census data easily at hand. I entered each one that Breckenridge was found in, and created a full citation for it, with a link to my favorite online census source. That way, I do not have to search through a website to get to it; I can go directly there with the click of my mouse; and the citation is right there, waiting for me. I just change the names, and other pertinent data, and move on. Easy peasy. 


Noting a piece of information for later research is easy for me; I just make the cell a different color. That way, it stands out. I know that when I find something of interest, its easy for me to shoot off in a different direction than my intended research plan for the day, so by making it stand out from the rest of the data, I can easily find it again on a different day to pursue further. (As seen in the example above.)


I also create templates for sources that I use often, and in Breckenridge, that equates to the mining deed records. For this citation template, I actually use drop down menus' within the data cells, because many of the clerk's books are titled the same way. For this citation template, I have drop downs that include elements such as "Grantee" or "Grantor," "Warranty Deed," "Quit Claim Deed," and my personal favorite, "Misc Records 1" "Misc Records 2..." There are literally thousands of documents labelled as "miscellaneous" records at my Courthouse!


I also do this for publications that I use often, such as the local newspaper titles. Once I started to utilize these tools and your keyboard shortcuts, it saves me a great deal of typing and time, because I can really fly through the citation details as I enter information.


Addendum: Within hours of posting this, I had several requests and questions about the in cell drop down menus feature that I use. You can access this in the "Data Validation" part of Excel, found under the Data menu, but I strongly suggest you watch a video or read through the how-to to utilize it, if you don't know already. In order to use this feature, I built a bibliography of sources into my workbook (which I already had on file anyway), so I can easily utilize the information in this fashion, but also have an established list of citations available to me as well. Multi-purpose bibliography! Yippee! 


Here is a screen shot of what this feature looks like when in play:

Note the drop down menu within the cell under "Publication." This allows me
to easily choose which source I received the information from, and apply it to
the spreadsheet. Creating a resource list or bibliography as one of your sheets in
the workbook will help tremendously with this process, so do that first.
©Jen Baldwin 2013-2014

Most of these tricks I learned by watching YouTube videos or reading through the instructional information on Microsoft's website. My last Excel class was a long time ago, and I lost most of these skills over the years. I don't have any one favorite resource for learning, I just Google until I find something that can help me. If all else fails, ask someone who uses spreadsheets a lot (like an accountant) to explain what you want do to, and have them help you with the right terminology so you can search more accurately.


To do some of these... 

• To lock the top row: click on View in the main menu bar, then Freeze Pains, and you'll see an option to lock the top row.
• To select a theme: click on Page Layout in the main menu bar, then use the Theme drop down button on the far left to select a pre-formatted theme. You won't really see any changes until you start working with the data. 
• To name a tab: right-click on the tab and select "Rename"
• To format the date: select the entire column, then in the Home section of the main menu bar, select "More Number Formats" in the drop down where it says "General." Select "date" in the left category list and then the format you want to use. You can also use the "custom" category if you do not see a pre-formatted version that you like. 
• To create a cover page: Start with Sheet 1, and remove the grid lines (a check box in the "View" page from the main menu bar). Insert text, photos, etc as you would on Word or just about any other program. I like to give it color and depth, so I usually play around with it until I get something a bit more creative. You can also add a full back ground from the Page Layout menu. 
• The drop down items within the cells I mention for citations are a bit more complicated, so I'll leave it to you to Google it. 

I hope this helps to answer some of the questions. The more I use Excel, the more I learn, so these workbooks are really always changing and adapting. I have to admit, I am using Excel now more than any other program in my research process, and that includes my genealogy software. For what I do, it fits my needs incredibly well. The program provides just enough structure to keep everything organized, but also has the flexibility needed to conduct creative research and to "follow a hunch." I know there is a great deal more this program can do for me, and I'm slowly learning, one step at a time.

I would love to see how other's use it, as well. If you so desire, leave a comment or a link to a blog post so we can all learn from each other! 

Reference note: I use Microsoft Excel 2010.