Excel and Word Group Sheet Templates

I had a blog reader ask me if I’d be willing to send him my group sheet templates in Excel and Word.  I thought for SURE that I had made those available to download on this blog, but I can’t find them, so I must not have!

I’m not sure if all of my column settings download correctly for everyone or not, but here they are. If you need setting details, let me know and I’ll be happy to share.

Blank Group Sheet – Excel

Blank Group Sheet – Word

Read Full Post »

Creating a Research Log

I was using my Research Log this morning and I was thinking about how helpful it is to me for keeping track of what I’ve already searched.  I thought I might re-post this blog from 2009 to see if it could help anyone else.  Happy Researching! ~Lisa

Last week-end, I had a 3 hour car ride to contemplate what my “ideal” research log would look like.  I had printed out my notes for Joseph Scott (1832-1914, Russell County, Kentucky) and purposely did not bring along anything else so that I would be totally focused on the one family.  For 3 hours, I read and re-read the notes jotting down questions and making sure all my little scraps of paper and post-it notes were included.  I decided to create a 1 page document with Joseph’s basic vital information along with a timeline of locations for his family.  After that, I began making a list of questions I have along with a list of sources I could search to find the answers.  I also included sources I had already searched.

Of course, I used Excel to create the log so that it will be easy to update and easy to adapt to other ancestors.  I had already created a database of sources available at the Allen County Library for the counties I research in, so those sources were easy to add to the log.  I’m in the process of searching the Kentucky Archives catalog and the FHL catalog for additional sources that I don’t have access to at the library.  I’ll also add web sites that could help my research.  My ultimate goal for the log is to have a record that I can add to my ancestor’s folder so that when I grab the folder to take to the library, the log will be waiting.  I’ll know what I’ve already researched and what I want to look-up on my next trip.  I need to be able to update it quickly, especially if I read an interesting article or twitter tweet that gives me a new idea of a source to search.  But more than anything, I need to FEEL organized and confident that I have thoroughly analyzed my notes and that I don’t need to take the time to analyze them again.  That, more than anything, keeps me from working on my research.  I don’t usually work on my research unless I have a full day – which I seldom have during the school year.  So if I can take smaller chunks of time to do the analyzing – and creating the log – then I’ll be more likely to jump into my research when opportunities do present themselves.

So here my the first version of my research log.  While I don’t want this log template to become a huge project, I am trying to think if there are other things I need to add to be as effective as possible.  It just occurred to me that I should include a list of people that I know are also searching Joseph along with email and snail mail addresses.

Please feel free to comment if you think something else would be helpful!

Read Full Post »

Keeping Track of the Clues

A couple of years ago, I posted several articles about using Excel to help with genealogy research.  I created my own version of Family Group Sheets and this week, I’ve begun making modifications to those to help me keep track of clues that I’m finding while I’m trying to determine if a certain family connects to mine. The time frame for this family is late 1700′s to early 1800′s so I’m not able to find potential birth dates based on census records or anything like that, so little clues from wills, deeds and court records can help me put the pieces together.

But I need to be able to keep these clues in an organized fashion – not on post it notes on my bulletin board or even in a notebook because sooner or later, I’ll lose track of those.  So I’m using hidden rows in my Excel Group Sheet. The beauty of hidden rows is that I can print a regular group sheet

or I can print an expanded version showing the clues that I’ve been gathering. A Group sheet fits on one side of a sheet of paper, but these rows can be whatever size I need to add every little clue that I find and I won’t have to worry about it ruining the look of my group sheet.  I can transcribe parts of documents or write out my thought processes to help me match potential ancestors with information I already have.

To include hidden rows in your Excel file:

1) Right click on the number of the row below the location you’d like to insert a row.  Select “insert”.

2)  I prefer for the row to be one cell with centered, red, italic print.  You can select all of these options by highlighting the cells, right clicking and clicking on the options you’d like.

3)  My group sheet has some areas for notes, but I usually add a hidden row under each child’s name and any other place which I might want to add information such as birth/death/marriage dates or parents’ names.  To hide a row, right click on the number at the beginning of the row you’d like to hide.  Select “hide” and the line will disappear.  Notice that the number also disappears, which can be a reminder that you have a hidden row.  If rows are hidden, they do not appear on your print out.

4)  To make the row visible again, highlight the rows above and below, right click and select “unhide”.  Once rows are unhidden, they will show up on your print out.

5)  If you have more than one hidden row, you can reveal them all by highlighting the entire group sheet or a group of rows, right clicking and selecting “unhide”.

Using hidden rows keeps me organized.  Knowing that I can add as much information as I want without messing up the look of my traditional group sheet makes it more likely that I will add the information to my sheet.  I won’t have to worry about missing the clue that ties everything together.

Would you like a copy of the Group Sheet in Excel?  Just click here!

Read Full Post »

Timeline in Excel – Part 3

Adding Callouts

Callouts are like thought balloons in a comic.  There are various designs for call-outs including starbursts, clouds and rectangles.  You can also add various effects to make them look three dimensional or tilted, etc.  The nice thing about callouts is that you can adjust the line coming out of the shape to point to an exact spot – in my case, to the date on the Date Bar.  To add a callout:

• From the Insert tab at the top, click on “Shapes” and then select the Callout shape you’d like from the choices.
  
• I chose a rectangle to keep it simple.  Draw the callout by clicking and dragging the mouse into the desired dimensions.  You can type your text immediately and then adjust the size of the callout to fit the text and the look you are going for.
  
• The yellow diamonds on the callout line allow you to adjust the line to point to the exact point on the timeline that you’d like by clicking and dragging the line wherever you’d like.
  
• Each callout can be formatted with color and other effects to bring attention to specific details.  For example, I chose colors for my callouts that match the colors I chose in the location bar to quickly show which county the fact was from. You can add effects by selecting “Shape Fill” under the Format tab.
  
• You can add as many effects as you’d like to make the timeline look as 3-D or as simple as you’d like.
  
• After you have inserted all of your callouts, some of the lines may be in front of the boxes.  To fix this, click on the callout and then under the “Format” tab, click on “Send to Back”.
  

There is no end to the number of effects you can add to your timeline.  For myself, spending time working on the timeline sometimes brings details into focus or shows me new areas to research.  It can show me areas where I’m making wrong assumptions and because I can add information for other family members as well, it can consolidate information in a way that makes sense visually as opposed to my notes which are filed by individual families.

I’d love to hear ideas of other things to include on a timeline!

Read Full Post »

Timeline in Excel – Part 2

Create a Location Bar

I decided that above my Date Bar, I’d make a simple Location Bar to show which County/State my ancestor was living in through the years.

• Select a row for your Location Bar.  (Row 4)  It should be a row that is high enough to enter the information you’d like.  I wanted to enter a date range and a location.
• Using the Date Bar as a guide, follow the same steps as for the Date Bar to create the Location Bar.  I merged cells that would have been the date range for a specific location.  For example, I have information that says my ancestor was born around 1813 in North Carolina.  The next location record I have for him is an 1840 census in Mississippi.  So I merged cells to cover 1813 – 1840.  Because I don’t know if he moved during that time, I put 1813 – ? and North Carolina.
• Merge appropriate cells across the time range until all locations are entered.
• Again, you can border each cell.  I chose to put a border on the top and bottom and then I used color to indicate if there was a question as to how long he lived in a certain location.  As a minimum, I suggest doing each location in a different color so it is easy to see when your ancestor moved.  To fill in color:

• • Highlight the cell(s) and click the icon for fill color.
  • You can choose one of the theme colors or you can click on “more colors” to choose from a color wheel of sorts.
  • You can also choose to add a texture or effect to convey information.  Because I don’t know exactly when my ancestor moved from North Carolina to Mississippi, I chose to add a gradient effect to make the color gradually fade out.  This reminds me to keep my timeframe for research flexible.

• • • To make a special effect with your coloring:  Right-click in the cell you will be formating and choose “Format Cell”.
      
    • Under the “Fill” tab, click on “Fill Effects”
      
    • There are various choices here.  You can select your color or colors at the top and you can choose different shading styles at the bottom.  Look at the different options and then click on the box with the desired design.   Because I knew a specific date that my ancestors WAS in a location but didn’t know when he moved from that location, I chose to start my box with solid coloring that gradually fades out.
      
• I used the same steps to also add a Location Bar for my ancestor’s father directly above his Location Bar.

That can show me areas that I may not have considered searching before because all I had in my group sheet was a date and location.  For example, census records say that William Stephens was born around 1813 in North Carolina.  But William’s father, Welcome’s timeline shows that he is listed in the tax records of Adair Co, Kentucky in 1812.  So either William was born in Kentucky, or he was born before 1812.  If he was born before 1812, then I might search in Buncombe Co, NC, where Welcome was located in the 1810 census.  A good researcher already is looking at this, but seeing it in a visual format keeps it in the front of my mind – not jumbled in with all of the dates and locations in my mind as I’m looking at group sheets.

Coming next time – adding Call Outs to show additional information.

Read Full Post »

Working It Wednesday – Timeline in Excel (Part 1)

As my school year gets closer to the end, my days become more and more hectic.  Students who are struggling are finally asking for help after school.  And my students have to pass the state test given at the end of the school year in order to graduate, so that means creating extra practice problems for that.  Bottom line is, I don’t have as much time to get to the library to research as I “normally” do.  (But summer is coming, PTL!) So I use my spare minutes here and there to update my notes and files and to think of ways to make my research more visual because I am a very visual person.

Keeping on with my Excel series, I decided to think of my Excel Group Sheets sort of like my “research wall”.  I don’t have to keep all of the information within a certain image size since I’m not printing it, (although I could print the group sheet only by printing page 1 only) so I can put all kinds of information in the worksheet and scroll to whatever section has the information I’m looking for.  Last week, I showed how to use comments as post-it notes.  This week, I’ve decided to add a timeline to help me zero in on the most likely locations to look for records for my ancestors.

I did a search online for a timeline template and found one that I think is incredible at http://www.vertex42.com/ExcelArticles/create-a-timeline.html, but while I loved the way it looks, I think it would take a pretty advanced Excel user to modify it in any way (like having it show up next to my group sheet).  I’m not looking for something that gives me MORE work to do, but a timeline that uses the very basic skills of Excel.  Knowing how to do these things are all that are required to make my timeline.

• Highlight a column
• Adjust the width of a column
• Merge cells
• Format cells (left justified, top or bottom, centered, etc.)
• Add color to cells
• Add call outs and adjust their size and shape.

These are the steps to make a replica of my timeline using Excel 2007.  I believe everything I do in 2007 can be done with earlier versions except some of the color gradients.  I also know that a lot of personalizing can be done to the task bar, so yours may not look exactly like mine in the images.

Due to length, I’m going to make this a multi-part posting.

Choose the location for your timeline.

Because I can’t change the width of columns A-I without changing my group sheet, I decided to put my timeline to the right of my group sheet.  I’ve removed my comment balloons for my illustration photos, but I left a couple of columns between my group sheet and my timeline to leave space for those.  So my timeline begins at Column L.  I also had to consider which row had the height that I wanted to use for my timeline bar.  I wanted to have notations above and below and I wanted a row that was high enough to read my dates.  I chose Row 6.  I plan to put timelines with different information above my date bar, so I wanted to be near the bottom of several rows with the same height.  I also wanted to add call-outs below the tame bar, so the thinner rows below Row 6 were good for that.

Format your columns:

I wanted to be able to indicate specific years for items on my timeline, but I didn’t want to take the space to type every year at the top of each column.  So I adjusted my columns to be very small and then merged the cells in the timeline bar to groups of 5.

• Decide where you want your timeline bar.  Remember, you can’t adjust the height of the top 40 rows or it will alter the height in your group sheet.  (Mine is Row 6)
• Highlight several columns (for mine, I highlighted columns L-P)
  • Clicking and hold your cursor on letter L at the top of the column and then drag your cursor to the last letter before releasing your click.
    
• Right click on one of these columns and choose “column width” and adjust the width.  I chose a width of 1.
• Instead of trying to highlight 100 columns, I only adjusted 5 or so and then clicked on one of the letters in the center and then clicked on “insert” over and over until I had the number of thin columns that I needed.  Every inserted column has the same width as the column to the right.
  

Create the Date Bar

• On the Date Bar row (Row 6), highlight the first 5 cells of the date bar. (Column L – P).  Right click that area and select “format cells”.  Under the alignment tab, select “merge cells”.
  
• Type in the date you’d like in the box.  In my tool bar, I told it to left justify and put it at the bottom of the cell.  You can also italicize or choose a different font for different looks.
• Repeat the last two steps working your way across the bar until all of the relevant dates are included.
• Highlight the entire Date Bar and select a fill color.
  • You can also add borders around each cell, or across the top and bottom – whatever you’d like for the look you’d prefer.

Your Timeline should now look similar to this.

Next time – Creating a Location Bar

Read Full Post »

Working It Wednesday – Using Excel Comments as Digital Post-Its!

This is something new I started using this week and I think it’s going to be a great asset in my research!

Several years ago, I fell in love with a free program called Fototagger.  (http://www.fototagger.com/)  I used it to identify people in photos or to add information about locations and such.  I especially loved to use it with newspaper clippings – specifically for obituaries.  I could add little balloons around an image to point to specific pieces of data and add information that I knew.  For example, in a list of survivors, I could add balloons with birth/death dates, relationship to deceased, corrections to information, questions to follow up on, etc.  If another researcher had the Fototagger software on their computer, they could see my balloons and add information and send it back.  If a person didn’t have the software, they would see the original image, but no balloons.  I absolutely loved the program!  BUT, when I upgraded my laptop to use Vista, the program caused my computer to shut down any time I opened an image, whether I had used it with Fototagger or not.  I checked the web site several times for updates, but it didn’t appear that anything had been done with the program for quite awhile and I had to stop using it.

This week, I decided to see about creating my own version of this using Excel and it’s ability to add comments.  This is my first time using comments with my genealogy stuff, so I may eventually find more uses for this, but I’m excited with what I’ve come up with so far.  Here is an image of one of my group sheets with no comments. (Click on the image to see a larger version in another window.)

Because I know I’ll want to have comments on both sides of my chart, I’ll add columns to the left side.  I can change the width of the columns if needed to give me more room.

If I have a question or comment (or source citation, since I can’t add those to Excel like I do in Word) I can add a comment that shows up in a balloon next to the cell I click on.  To do this, click on the cell and then under the “Review” tab, click “New Comment”.  The balloon shows up with the cursor in place to type my comment.  I can always go back and change the comment just like I would for any text.

 

When the balloons pop up, they are directly to the right of the cell – which means that at times, the balloon is right in the middle of my chart.

I can move the balloon to any part of the page I’d like, change the size of the balloon and have a line point to the piece of information I’m referring to. To move the balloon, put your cursor along the edge of the balloon until the cursor becomes a cross.  Click on the balloon and move it wherever you’d like.  To change the size of the balloon, place your cursor on one of the corners or center of an edge.  The cursor become a double arrow.  Click and drag the box into any size you’d like.

I like these balloons for source citations, follow up questions, “to do lists”, information on other researchers I’m sharing info with or why I think information might be wrong.  I can then email my file to other researchers who can see my questions and comments and then add their own before emailing it back.  With one click of the button, I can hide individual comments or all of the comments.  Small red triangles remain in the upper right corner of any cell that has a comment.  The comments do not show up in the print out (or at least I haven’t found a way to make them show up.)

I think I’m most excited about leaving post-it notes behind!  Adding comments to my pages as soon as I think of things will help keep my thoughts organized in the midst of the dozens of interruptions that I experience every day!

Read Full Post »

Working It Wednesday – Linking Group Sheets

I have groups sheets written in Excel – which I love.  But I’ve moved more to group sheets written in Word because I love the ability write my citations easily and to keep them well organized.  But since my group sheets in Word are sized to fit in my planner, I do still use my Excel group sheets when I want to print a full sized version for my folders and binders.

My focus lately has been completely on my Stephens line.  I’ve been working on 4 generations of Stephens families who scattered near and far and I get so frustrated with the number of William’s in each family!  A man has a son named William and he and most of his siblings name a son William as well! They also tended to continue the tradition so before long, I’m looking at these different group sheets with Williams all over the place and I get confused on the relationships between them all.  I ended up taping my group sheets to the wall so that I can see them all to find how each William is related! How much easier it is to look at a group sheet, click on a name, and go directly to HIS group sheet without searching the wall!  Hyperlinks allow me to do this.  I can link to group sheets for any name on the group sheet – siblings, wives or parents.

This is convenient because the more group sheets I make in the Excel workbook, the more clicking I need to do to find the tab for the sheet I’d look to look at next.  I have 22 group sheets in this particular workbook and to quickly switch back and forth between two group sheets that don’t happen to be next to each other on my tabs can be a bit of a pain.  By hyperlinking my group sheets, I can switch quickly to any group sheet that I’d like.  Here are the steps for linking group sheets in the same Excel Workbook.

For my example, I will show my group sheet for Andrew J. Stephens and his wife, Lucy Stephens.

Let’s say that I want to make a link to Andrew’s son-in-law, William George.  First I will right-click on the cell with William’s name.

A list of options will appear.  I will click on “Hyperlink”.

A box of choices will appear.  Since I want to link to an Excel page within this workbook, I will be sure to choose the “Place in this document” selection on the left and then a list of my various groups sheets will appear in the larger box on the right.  I will find William George in my list and double click.

Now William’s name in the original group sheet is “hypertext blue” and I know that if I put my cursor over William’s name, the cursor will become a hand. When I click, I will go directly to his group sheet.  (If I want to edit the text in the box, as long as the cursor is not a hand, I can click on the cell and edit the text.) From William’s group sheet, I can then make a hyperlink for his wife, Elizabeth and it would bring me right back to the group sheet I started from.  I can hyperlink any name in my group sheet to move directly to their group sheet. Handy!

Next week, I’ll write about other ways that I use hyperlinking in my genealogy work.

Read Full Post »

Discovering New Information with Excel

About 2 years ago, I was thrilled, but overwhelmed with the information available in a book I had ordered online.  It was published by the local historical society and it contained an abstract of all of the church records available at the church founded by my husband’s ancestors in Kankakee County, Illinois.  Before purchasing the book, I had found a list online of all of the voting members of the church in 1878 and I knew that several of his ancestors were members.  What I never imagined with the wealth of information available on those ancestors in the church records!  (Confession:  My shyness has kept me from every going to a church to ask what type of records they keep!)

As I flipped through the  500+ page book, I could see that if I didn’t come up with a system quickly, I’d be losing out on valuable information.  In addition, the book stated that the records of the early church were all written in German, so even if I had been able to get my hands on the actual records, I would not have been able to understand most of it.  The book was a translation of the records, so it was a double bonus!

I set up an Excel file to begin entering information.  I created a column for Record type (birth, marriage, death, baptism, confirmation, etc.) Year, Name of record subject, Father, Mother, Spouse, Page #, Witnesses and Comments.  I created identical pages for each surname I was researching so that I could quickly click between surnames as I went through the book.

To begin, I used the index and began going through the book entering the information as I went.  No single record contained ALL of the information, but I knew I’d be able to sort columns to decide which records belong to the same person.  Looking at the information from all of the records for a person gave me much more information that a single record could.

After entering all of the information, I sorted the Name column (being careful to choose the ‘expand the sort’ option so that all rows stayed together) so that each individual would either end up grouped together or close together.  This also helped me to see if there was more than 1 person with the same name to be aware of in other research, such as census records. (My images do not include all columns to make them easier to read.)

I could further sort these specific names by date to see if that revealed information to help differentiate people with the same name.  Finding the death of 1 individual with continuing records after that date helped to sort men with the same name.  Often, this simple sort gave me a lot of information that I wasn’t expecting including middle names, various spellings and multiple spouses. 

I could also perform a sort of the names in the Father column.  Based on this, I am able to write a sort of family group sheet.  Often, I would find a child that I didn’t know about and I discovered that often, if a child died, the next child was given that same name in honor of them.  That solved some puzzles I’d listed in my notes. 

2 wives named Katherine

I can also do a custom sort forcing the program to list names in alphabetical order and each identical name in chronological order.  This could give me an approximate timeline for when a spouse died and when a second marriage occurred. Another thing that this sort gave me was maiden names for spouses and multiple spouses. 

I did a similar sort for the Mother column.  When I sorted by female names, I discovered German names for husbands that I never would have known about otherwise.  Why research Ehranreich Betz when my ancestor’s name was Alexander Betz?  I can also manually move rows that match the individual I’m focusing on so that all of the information is together.

Alexander/Ehranreich Betz

Although this has nothing to do with Excel, I was also very pleased with some of the information I gleaned from the witness or comments section of the records.  For example, I had a date of death and a tombstone for an ancestor only to discover through the comments section that this person had committed suicide and was not actually buried in the cemetery, but on a nearby farm.  This explained why I had not been able to find a will and led me to search for the newspaper story about his death, which I never would have thought to look for if I had been content to know the date of death of “place of burial” only.

I also have the great joy of researching the surname “Smith” on my side as well as my husband’s side and I use these processes in Excel in the same way.  The only difference is that I keep records from multiple books, microfilms and web sites citing the sources in a separate column.  I love being able to manipulate the data like this and know that I won’t mess anything up – it all goes back to my original format as long as I don’t save the manipulated file when I’m finished!

Read Full Post »

Group Sheets using Excel

I LOVE excel for my genealogy work.  I use it for all kinds of databases I have compiled because it is useful for sorting information in different columns to reveal patterns and missing information.  I use Excel to make my own 5 Gen charts and family group sheets.  I designed my own family group sheets using Excel for several reasons.

Family Group Sheet created in Excel

1. I HATE my handwriting.  I love being able to keep my group sheets readable and professional looking.  And often, the spaces in pre-printed forms are too small for me to write my information in.  So when I take my binder of group sheets to the library, I write notes all over them and later make changes to my Excel file then reprint.  Often, as I’m typing things into the Excel file, more questions come to mind that I didn’t think of at the library.  (Why does that always seem to happen?)  I include these questions in my bottom “Notes” section to allow me to continue my train of thought.
2. I was able to include fields that the forms I had been using didn’t include or take out fields that I never use.  For example, my forms have a space for cemetery name and location, more spaces for children, space for notes for the husband and wife and then an additional notes box at the bottom for general family notes or a “to-do” list.  I do not have spaces for christenings or sealing dates since those don’t apply to my family.
3. I can use color and formatting to help keep track of my research.  For example:

•  
  • Questions or comments to myself are in red.  Un-confirmed information from another researcher can be in green.  Contact information for that researcher would also be in green in the “Notes” section at the bottom.
  • Boxes that will never have information can be shaded or colored.  For example: a child who dies at birth will not have marriage information.     
  • The name of my direct ancestor can be in bold.  This helps me in families that have multiple uses of the same name.    
  • You can’t see it in the image, but I use the footer to indicate the life span of the husband and the counties that I have found records in. 
        

The only thing I DON’T like about group sheets using Excel is that I can’t use the reference tools that I can with Word.  (At least not in my current version.  Maybe it’s been updated?)  In Word, I click a reference button and it automatically selects my footnote number and then jumps to the footnote location for me to enter my citation.  If I find new information closer to the top of my group sheet, it automatically rearranges my footnotes and updates all of the numbers.  In Excel, I can enter my own superscripts for footnotes, but they don’t change automatically if I add something new.  And I have just enough OCD tendencies that I can’t stand to see my numbers out of order.  (Must be the math teacher in me…)

So I recently started converting my charts to Word files to take advantage of the reference options there.  The conversion is slow, but so far, I like it.

Read Full Post »