18 April 2014

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:


genealogy, Excel, tools, spreadsheet, family history, One Place Study
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.


genealogy, Excel, Breckenridge, fraternal societies, history, research
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.

16 April 2014

Press Release: Registration for Conference and Events Closes 22 Apr 2014

I received this press release this morning from the National Genealogical Society.







Press Release
Contact: Erin Shifflett
Phone: 703-525-0050                                                                                                                                 
eshifflett@ngsgenealogy.org                                                                                                             
For Release: 16 April 2014

Registration for the NGS Conference
and All Ticketed Events
Closes 22 April 2014

arlington, va, 16 APRIL 2014: Have you registered for the NGS Family History Conference in Richmond? The deadline for pre-conference registration is 22 April 2014. Registration will be available on-site beginning at 12:00 noon, 6 May 2014, in the Greater Richmond Convention Center.
Registration for all meals, social events, and workshops closes on 22 April 2014. No ticket purchases will be available on-site at the conference for meals, social events, or workshops. Likewise, registration for Librarians’ Day also closes on 22 April 2014. For conference information and to register, go to http://conference.ngsgenealogy.org/attend/.

Breakfast, Luncheons, and the NGS Banquet
Participating organizations sponsor several luncheons during the conference. Seats are still available for several of the luncheons, the NGS First-Timers Breakfast, and the NGS Banquet. Make your reservations now at http://conference.ngsgenealogy.org/attend/. The NGS First-Timers Breakfast is $24, luncheons are $32, and the banquet is $51. Menus are in the registration brochure at http://conference.ngsgenealogy.org/wp-content/uploads/2013/11/Registration-Brochure-Final-Rev-11.pdf.

Live Streaming
If you are unable to attend the NGS 2014 Family History Conference, ten lectures featuring some of the most popular topics and nationally known speakers will be available to you via live streaming.  Details about viewing the live streaming program and the costs can be found athttp://conference.ngsgenealogy.org/attend/live-streaming-at-ngs2014gen/. Registration for the live streaming program closes on 30 April 2014.

Society Night
On Wednesday evening 7 May 2014, many Virginia genealogical and historical societies will be available in the Richmond Marriott from5:15 p.m. until 7:00 p.m. to answer questions about local repositories and resources, discuss their group’s activities, and sell their publications.

Richmond Area Tours
There are a few seats left on the historical tours prior to the NGS 2014 Family History Conference through Richmond Discoveries’ Tours onTuesday, 6 May 2014. To register go to http://www.richmonddiscoveries.com/ngs.php.  The password is NGS2014 and is case sensitive.

Add Items to an Existing Registration
To add meals to your current registration, log on at http://www.ngsgenealogy.org, click on My Account, select My Events, and then click to Add Sessions. To add pre-conference events, click on My Account and then select Upcoming Events.

You really don’t want to miss this year’s exciting conference program from 7–10 May at the Greater Richmond Convention Center and Richmond Marriott.

Founded in 1903, the National Genealogical Society is dedicated to genealogy education, high research standards, and the preservation of genealogical records.  The Arlington, Virginia, based nonprofit is the premier national society for everyone, from the beginner to the most advanced family historian, seeking excellence in publications, educational offerings, research guidance, and opportunities to interact with other genealogists.  Please visit the NGS Pressroom for further information.
-End-

14 April 2014

Twitter Coaching Available in 2014




I am so excited about the crowdsourcing that is available on Twitter, I will be offering FREE "how to" sessions via Google Hangouts in 2014! 


These will not be HOA's, so they will not be streamed on YouTube, and they will not be recorded. They are private, intimate sessions for you to ask any question you want. We can work through the process together, learn my best "tricks" on Twitter, and in an hour or less, get everyone comfortable enough with the platform to be able to participate in #genchat . Each session will be limited to ten people - including me! - so I'm asking you to RSVP either by posting to this message or by emailing me at ancestralbreezes at gmail dot com.


There is no fee. There never will be. I just want all of my geneabuddies to understand the impact that Twitter can make on their research. The next session will be held on Wednesday, April 23 at 9:30am Mountain. I will add more hangouts to the schedule very soon, and will try to accommodate all the time zones. If you need a special time to fit with YOUR schedule, please contact me, and we can work together to set something up.



Join me in learning all about Twitter and what it can do for you, your research and your genealogy education!



Connect with me on G+: google.com/+JenBaldwin



9:30am Mountain
8:30am Pacific
10:30am Central
11:30am Eastern