How to Use Excel for Genealogy

Learn how to use Excel for Genealogy. This episode shows how to use Excel and some tricks to using it for genealogy. Here I show how to edit, color, move, delete, insert cells, rows, columns and more. Learn different reasons to use Excel for family history, like outlining DNA matches, extracting data from indexes so it can be filtered to your answers or research questions, and correlating evidence. This is a great video for beginners and intermediate Excel users alike. It is a step by step process of how to use Excel. If I can do it, you can too!
OTHER VIDEOS
🔴 #1 Way to Break Down Brick Walls (uses Excel to extract census).
• #1 Way to Break Down B...
🔴 #1 Way to Break Down Brick Walls Updated 2020
• #1 Way to Break Down B...
🔴 Extract and Filter City Directories Using Excel
• Extract Ancestors from...
🔴 Extracting Census Data into Excel to Find More Ancestors: Tiny Tip Clip
• Extracting Census Data...
🔴 Organizing DNA Cousin Matches Using Excel Spreadsheets
• Organizing DNA Cousin ...
TIMING
0:00 Intro
0:18 Why use Excel?
2:33 How you can use Excel
3:33 Using Excel for DNA Matches
5:58 FAN Club worksheet and filtering
8:28 Handouts and how to get them
9:52 How to use Excel for Genealogy
10:20 Basic template for correlating evidence
10:51 Cell width formatting
11:50 Expanding cells for notes
13:30 Creating a source hyperlink
15:24 Save early save often
16:34 Undo and redo button options
17:30 Moving cells
17:52 Cell handle to copy/drag/delete
18:12 Insert a column
20:44 Create a filter
22:21 Extracting census record data
25:09 Highlighting/coloring cells
30:00 Conditional formatting for DNA matches
33:39 Birth year calculation formula
34:29 Correcting infant birth year
38:20 This equals that formula
❤️ JOIN the GENEALOGY TV ACADEMY
genealogytv.org/about-genealo...
📝📝📝THREE DIFFERENT WAYS to find the HANDOUTS!
📝 1) Become a CHANNEL MEMBER at the INFO ACCESS level $9.99 per month. This gets you all the handouts. kzbin.infojoin.
As a Channel Member, find the handouts by scrolling through the blog posts on the Community tab at kzbin.infoC....
📝 2) All handouts can be PURCHASED INDIVIDUALLY ($5 and up) at genealogytv.org/handouts/. Here you will get a redirect and an email to download the handouts.
📝 3) Become a monthly PATRON at the HAPPY DANCE level ($15/Month) or more. You will get early release of the videos and the handouts are emailed to you, when they become available. / genealogytv
❤️BECOME A GENEALOGY TV CHANNEL MEMBER and help support Genealogy TV - see perks!
kzbin.infojoin
👉 Subscribe to Genealogy TV
/ @genealogytv
🛍️ Genealogy TV’s AMAZON STORE full of Connie’s favorites. www.amazon.com/shop/genealogytv (Amazon Affiliate)
✅ Genealogy TV WEBSITE
genealogytv.org/
✅ GTV on FACEBOOK
/ genealogytv
✅ SIGN UP For GTV’s NEWSLETTER
mailchi.mp/a0862c6473aa/genea...
🌳 Subscribe to Ancestry.com (affiliate) prf.hn/click/camref:1101l4aFW...
📰 Subscribe to Newspapers.com (affiliate)
www.anrdoezrs.net/click-91101...
❤️ TIP JAR - If you like the videos, please consider becoming a Patreon member.
/ genealogytv
📰 Subscription to NEWSPAPERS.com (affiliate)
www.anrdoezrs.net/click-91101...
▶️ SUBSCRIBE to NC ANCESTRY (North Carolina Ancestry) at
/ ncancestry
I am a fanatic for genealogy, family history and DNA to research my American ancestors. I create the best free genealogy videos and webinars on KZbin. Learn genealogy research skills to help you with your family tree and family origins. I am a professional genealogist; I teach research skills and records research. The best videos on “Genealogy TV” (KZbin) are about learning research notes, logs, staying organized, genetic genealogy, finding missing ancestors, and where to find family history records.
Best KZbinrs for genealogy are Genealogy TV, The History Guy, Aimee Cross Genealogy Hints, Family History Fanatics, Geneavlogger, Legacy Tree Genealogy, , Ancestry, FamilySearch, Useful Charts, Dear Myrtle, Genealogy with Amy Johnson Crow, and 23andMe.
#Genealogy #GenealogyTV #FamilyHistory
Music Credits for Song on Word Tree Open
Circus Waltz Kevin MacLeod (incompetech.com)
Licensed under Creative Commons: By Attribution 3.0 License
creativecommons.org/licenses/b...

[@minikitz5642]

Wow. Thanks so much! Learned a lot of cool tricks & tips I never knew about Excel. - Debbie from Phoenix.

[@GenealogyTV]

Thanks Debbie from Phoenix. Thanks for your continued support. 😀

[@geneewert7591]

I'm pretty good with Excel, but your videos on Excel are such an encouragement. One trick is when you want to copy a formula (or anything down in a column), when the cell is highlighted and that little black square appears in the lower right of the cell, double click on it and it will fill to the bottom. That is really great if you had copied 15 pages from the census and have several hundred rows. Keep up the good work & great info! People also need to be aware that if you copy a cell with a # or a number at the end, Excel with automatically increment it. To get around that, say that it's 1860 you want to copy down, type it in 2 cells, highlight both THEN copy down and it won't increment.

[@GenealogyTV]

Great tips, thank you.

[@storminight]

You can also cont shift down (or left right whatever) arrow and it will go to the bottom of a column. (I think it’s alt shift, it’s so second nature now.)

[@patrickdornan4789]

Thanks, Connie. I've been experimenting with ways to use Excel for tracking and documenting my research. This has really moved the ball down the field.

[@GenealogyTV]

Awesome!

[@edavissmith467]

Thank you, thank you, thank you, Mrs. Connie! I spent the whole day watching this video and taking notes. I got 11 pages of notes. This was just what I`ve been wanting to use with my research but I was just looking for a video on Excel when I saw you made the exact video I needed: a how-to lesson on not only using Excel but using it with Genealogy and DNA cousin matches. I'm so excited! I'm going to share this with some of my friends who want to learn ways to organize and analyze their research. This was such a blessing. Thanks, again! 🤗❤🌷

[@GenealogyTV]

You are so welcome. If you want my notes with images... you can purchase the handout out genealogytv.org/handouts/ for $10.

[@theresaw4750]

I have been looking for your Excel stuff. Going in and out of your different documentations trying to figure out where the Excel sheets were in how you kept it a data because I really liked the one time I saw it and I was not find it so I’m so glad you posted this one today.

[@GenealogyTV]

Excellent!

[@debrahunt5176]

Thanks for the info on how to cut/paste from excel Census Records. That will save a ton of time and help prevent human errors.

[@GenealogyTV]

Yep... glad to help.

[@carokat1111]

Thanks you! This was the video I needed.

[@GenealogyTV]

Glad it was helpful!

[@bv2858]

Thank you so much. You are such a great teacher. I feel that after watching this video several times I'll be able to different things. Not sure about the DNA part. Right now my brain is just like your little picture at the end.

[@GenealogyTV]

LOL... one task at a time.

[@arlenebishop9489]

Yup, I'm the same with the DNA section, but the rest is GOLD! Haha!

[@cababyboomerq6012]

This is JUST what I need! Thanks!!!

[@GenealogyTV]

You’re welcome.

[@dsbennett]

You can use conditional formatting to flag dates that indicate wrong date or wrong person. Like, when George marries Lucy who is 3 years old. Stuff like that. Great instruction. Thanks.

[@GenealogybySamGilsenan]

New Subscriber!! Fantastic Video. And i learn new things each day!! 🙂

[@GenealogyTV]

Awesome! Thank you! Welcome to the GTV family Samuel.

[@debralarrabee4530]

Very helpful, thank you!

[@GenealogyTV]

You're welcome!

[@garylhaas2005]

good stuff. I use excel to extract data from websites into workbooks. (without copy and paste. Power Query and Power Automate Desktop do the heavy lifting) the best thing for a new user is to find a mentor. Connie, you are a great mentor. In most cases you don't have to be an uber geek and design things from scratch. Find a geek, smooze the geek, borrow their workbooks.

[@GenealogyTV]

Thanks Gary

[@ginagaladriel]

You can use conditional formatting with text and other kind of data, if text you just need to add " " to the text you want to have formatted, HOWEVER.... it will not help for the purpose of cM's in the way that you're showing us (which is wonderful BTW, thank you for doing this) Important thing to remember when you link cells (formula), what ever you change on the original, will change on the other spreadsheet as well automatically (lol and as I typed that you mentioned it lol)

[@GenealogyTV]

LOL... yes... thanks Gina.

[@schweizer2605]

Great job, thanks.

[@GenealogyTV]

Thanks

[@arlenebishop9489]

Woah! Copying census records into excel? Absolute game changer! Subscribed, thank you!!!

[@GenealogyTV]

Thank you. Yes it really is. If you liked that episode, watch this one... it was a game changer for me. kzbin.info/www/bejne/q6HIlIePesirpNk

[@arlenetatum9511]

Awesome Job 👏 👌 👍 😍

[@GenealogyTV]

Thank you! Cheers!

[@reneosbaldomoralesjacobo8413]

Me ha dado muy buenas ideas 💡

[@GenealogyTV]

Awesome

[@KimtheElder]

Mind-blown indeed😮

[@GenealogyTV]

Awesome! I love it when I get that reaction.

[@tossyv]

My head is spinning. ⭐️

[@GenealogyTV]

Well that is why we take it one step at a time.

[@johnchase9054]

Nice instructions on how to manipulate Excel -- but I was wanting to know how I can leverage Excel for genealogy. I gleaned a few tips but this is really about how to manipulate Excel with genealogy as the background.

[@GenealogyTV]

Just for you... I have created a playlist of videos about how to use Excel for genealogy.

[@ProgRockKeys]

I used Excel to document godparents, Baptismal sponsors, for my paternal grandmother’s large Irish family. I noticed some recurring last names, which could indicate cousins as the family had recently emigrated from Ireland.

[@GenealogyTV]

Yes! Exactly... this is how Excel can help.

[@scottridings6008]

Ctrl+Shift+L will add filters. You need to have the active cell in the data where you want to add the filters, though.

[@GenealogyTV]

Awesome. I love keyboard shortcuts.

[@gerry8558]

Hi Connie You ar so wonderfu at teaching is there a way to transpose the Census Using Google Sheet I have been tring to do this for a year However no one knows Thankyou again

[@matttolmanaz]

People can Google Drive for Desktop. There are a couple ways you can set it up one of which is that it mirrors the file on the desktop and google drive.

[@GenealogyTV]

Good to know Matt. Perhaps I can get you do demonstrate one of these days.

[@gopherlyn]

I used to use Excel for everything, until Genealogy. The issue I ran into was it does not sort dates, I would copy and paste children with birth and baptism dates from other sources, then I wanted to sort them from oldest to youngest, Excel has an issue with this, so for Genealogy I started using Open Office Calc spreadsheet, it works wonderfully for dates, you format the columns you want a date in prior to filling it, even if for example have formated the date filled as Dec 25, 1945, but typed in 12-25-1945, as soon as you hit enter it automatically changes the date to the format you selected, say you wanted to change the date format as soon as you change the date format for that column, all the dates automatically to the new format. You then can sort by the date and it works perfectly.

[@GenealogyTV]

Good to know

[@anellawrites]

Thank you for this. I can see the merits in working with spreadsheets in genealogy, but recently I’ve discovered that I likely have Dyscalculia so obviously numbers and formulas are hard for me.

[@GenealogyTV]

Yeah me too.

[@anellawrites]

@@GenealogyTV Really? You sound so confident in your videos.

[@robertshade8631]

Need help in finding the death dates and burial places for three of my maternal grandmother sisters.They mostly resided in the rural areas of Columbus Georgia.

[@tiatistimaren]

Hi Connie! Love your videos and was able to catch the panel discussion of the East Coast Genealogical Conference (?). It was worth every penny. Here's my dilemma: I've been building 'dirty' branches in my Ancestry tree on DNA matches of 60-30cM cousins. I often look at Shared matches of the cousin. I have several people with no obvious connections (i.e. closer cousins). I think my next step is to export each Match's direct ancestry branch and throw all those into Excel then sort on places. I'm very good with Excel as well as rearranging a GEDCOM into a file that will easily import into Excel. So here are the questions: How to export only the Match's direct ancestors? Or..is there a better way to find common places between Shared Matches without knowing what those places are? Thanks so much! Christy PS RootsMagic? Family Tree Maker?

[@GenealogyTV]

tiatistimaren this is a tricky question. To the best of my knowledge you can't directly export a matches direct ancestors. However, you might reach out to them and ask them to export their tree as a Gedcom file and send it to you. That might be a little intrusive at first, so I'm not sure they would do it. If they did, you could import it into something like Family Tree Maker and export it as a cvs file. Interesting concept you have going on there.

[@charlottekerns5633]

Re: the issue with the 10 month old infant: By noting the enumeration date and counting back 10 months would give the prior year which, for the purposes of discussion, would help with the spreadsheet formulae. This video was useful information.

[@GenealogyTV]

Thanks Charlotte

[@theropesofrenovation9352]

Gosh darn. I've been doing Genealogy for 11 years. How in the world does one start over using Excel. I think it'd take another 11 years. I'd love to do it though. New overwhelmed subscriber. lol

[@GenealogyTV]

I’m not sure I would start over using Excel. I use it as a tool to filter certain records and specific research strategies.

[@GenealogyTV]

Thank you for your CHANNEL membership.

[@henrywheeler5661]

Circa 2013 and memory is foggy, a fellow developed a spreadsheet for the census, that folks could download, I tried it and it was pretty slick. Did you mention this excel spreadsheet in your presentation?

[@GenealogyTV]

Yes. genealogytv.org/product/handout-for-how-to-use-excel-for-genealogy-research/

[@boonieloo4792]

Miss Connie, I would love any recommendations on what laptop computer you feel is best for you when doing genealogy. TIA

[@GenealogyTV]

Oh boy... I'm not an expert at computers. I use a custom built desktop for video editing... and I do love my little Surface Pro for travel because it is so light weight... but they are expensive. The one thing I have learned is to get the most robust computer you can afford because you'll likely be using it for 5- 10 years. I do use multiple monitors with my desktop which is a HUGE plus for genealogy. If you can do it, get two monitors. It will change your life.

[@boonieloo4792]

@@GenealogyTV thank you so much for the response. My Birthday is Thursday so I’ll share this info with my husband. haha!! 😁

[@melallegrina3890]

Thank you so much, I have incorporated a lot of your Excel teachings. However in relation to your extracting Census data from Ancestry I have a problem when extracting data that has only one name field, usually consisting of First name, Middle name or MI,& Last name all in one cell. For filtering purposes could you also show a tutorial where you can separate the First name and or First & Middle name (ie: Mary Jones or Mary Anne Jones) into 2 separate columns automatically. ------->IE: Mary Jones or Mary Anne Jones. This is a problem I have with Canadian Census's which only has one name field. I have ot found a solution to be able to do this in one step before being able to filter data. Thank You

[@GenealogyTV]

I don't know that you can do this automatically, but use the find feature instead under Find & Select in the Editing section of the ribbon.

[@melallegrina3890]

@@GenealogyTV Thank you for the reply Connie. I will try that and keep looking.

[@shelleymonson8750]

Connie, how do you do the squiggly lines connecting cells? You used them in your cM worksheet.

[@GenealogyTV]

Hi Shelley. In Excel, Insert>Shape>Squiggly line with arrow. Then click on it and grab the handles and move them around.

[@shelleymonson8750]

@@GenealogyTV Thanks!

[@leslieaj7732]

@@GenealogyTV In my version of Excel, Shapes are under Illustrations, along with Pictures, Icons.

[@tabathasteele7547]

Is there something similar for the census that just lists hashtags for family members instead of names? To verifyu you have the correct ancestor?

[@GenealogyTV]

Research the neighbors from decade to decade to see if they are the same. That will help you decide if they are the right folks or not.

[@roberthopgood1894]

you could convert months to a decimal year

[@dougshaw4287]

I was about to make the same suggestion. At ~34:15, you could change the cell to =10/12 and maybe adjust the number of decimal places shown in the cell format. Then the formula for estimated birth year will roughly work, maybe off by a year depending on when the census was actually taken.

[@GenealogyTV]

Good point guys. I hadn't thought of that at the time.

[@nicolegibson2547]

I joined and paid, however am having a hard time finding the hand out under the community tab!

[@GenealogyTV]

Hi Nicole, I see you are at the support level. Thank you. To access the handouts you need to upgrade to the Information Access level. To do so, go to your KZbin account in the upper right corner, drop down to Purchases and Memberships and upgrade.

[@janabarnes1791]

Is there a way to extract 1840 and 1830 census data using a 2019 version of Excel? I watched one of your videos in January 2023 and was able to successfully use your method on my Windows 10 or 11 computer for the 1850-1910 censuses. When I try it now, it puts all of the information in one column. I use the "Paste Special", then "Text" method.

[@GenealogyTV]

You can try pasting special, Unicode Text. I've seen some people use that. You could also try Google Sheets, Paste Special, Values Only.

[@janabarnes1791]

@GenealogyTV Thank you for your suggestions. I'll save them. In the meantime, I was able to get it to work by switching browsers from Firefox to Chrome. I don't know why that worked, but it did. I'm thrilled to be making progress as I search for clues for my mystery ancestors! I have learned so much from Genealogy TV!!! Thank you!

[@debjordan4399]

Wouldn't it be wonderful if everybody would upload their test results to GEDmatch!?

[@whyaskwhybuddry]

It would be nice if these Genealogy Software providers put in a way to export/import Trees directly to/from Excel via a .csv file.

[@GenealogyTV]

Interesting idea

[@whyaskwhybuddry]

@@GenealogyTV Without it you'd have to learn SQL code to extract what you want and place it how you want it. Family Tree Maker allows for Custom Reports for most Data Objects, but the data gets word wrapped into single cells when exported to Excel. If your export file is longer than Excel can handle, your screwed. I've had to Print to PDF, export PDF into Word, put in Special characters and put the Data into Tables for Excel Import. Not a quick process. All I want to do is to fix locations/GPS with Dates in Excel and reimport

[@varon52]

Allow me to suggest an idea. Often when a video contains a demo that uses a pointer, the pointer is small and difficult to see, especially with poor vision folks along with the difficulty to follow that said pointer and to pay attention what you are saying at the same time. By, at least temporarily as one does a video demo, changing the size (extra large) and color (black) of the pointer, things are just a little easier. Thank you.

[@GenealogyTV]

Good idea

[@SuggieBear]

I hope you don't mind, but I need to ask a question. How do I find an ancestor that doesn't seem to exist before 1900 yet he was born around 1858?

[@GenealogyTV]

That is a complicated question. You may want to join the GTV Academy where we tackle strategies for genealogy research. GenealogyTV.org/Academy

[@SuggieBear]

@@GenealogyTV It has been complicated. I did recently find some newspaper articles recently that could be about him, but it doesn't offer any clue as to who he was and who his family was.

[@jackybowen7470]

Is anyone using Office 365 on a Mac able to do this? I've used Excel for years! This just doesn't work.

[@GenealogyTV]

If you are using Firefox, this won't work. Use Chrome of Edge browsers.

[@debbie122]

@@GenealogyTV Thank you! I've been trying to get this to work for over an hour!

[@MichaelDeBusk]

Is this restricted to Excel, or does it work with any spreadsheet application?

[@GenealogyTV]

It works similar on Google Sheets. The filter function on Google Sheets (which is free) is Edit > Paste Special > Values Only.

[@roberthopgood1894]

Where do you get danish records?

[@GenealogyTV]

They are at www.sa.dk/en/services/arkivalieronline/archives-online/ but can be challenging to navigate until you understand how they work. Some are also available on Ancestry, FamilySearch, and MyHertiage.

[@roberthopgood1894]

@@GenealogyTV I have been trying to find mom's 2nd husband date & place of birth in Denmark She doesn't remember it

[@Rusty_Gold85]

My problem is how to show 7 or 8 Generations of Geneology and how Families were linked by marriages , and then show their siblings plus children. While adding a ton of photos and data on them all , that a lot of alive cousins can read

[@dmaeder]

Excel does not handle dates before 1900.

[@GenealogyTV]

Interesting. I change the cell to text format if I have any issues.