Excel Tips For Genealogy Spreadsheets

Here are tips and tricks that I regularly use when working with genealogy spreadsheets.

Table Of Contents

How To Enable Blocked Macros

Here is the video answer. Read on for the written instructions:

You opened a workbook and took it out of protected mode by clicking “enable editing” at the top of the sheet.

If the workbook is still blocked, you will now see this at the top:

You need to take some extra steps. Be sure that you know where the spreadsheet came from and that you trust the source before you proceed with these steps.

1. Right-click the file in Windows Explorer.
2. Choose properties.
3. Scroll to the end of the General tab.
4. Check the box labelled “Unblock”.
5. Click “OK” and open the workbook.

How To Insert An Image Into Excel

1. Go to the Insert tab in the ribbon.
2. Expand the Illustrations drop-down and choose “Pictures”.

You can select images from your local machine.

Microsoft also provide a selection of stock images and sources for online pictures.

How To Freeze The Top Row

1. Click on the “View” tab in the top ribbon.
2. Click “Freeze Panes”.
3. Choose “Freeze Top Row” from the drop-down menu.

How To Remove Blank Lines From An Excel Worksheet

Here are the steps:

1. Highlight the entire column
2. Click on the “Find and Select” menu in the Home tab.
3. Choose “Go to Special” (the fourth item in the menu)
4. Select the “Blanks” option and click OK
5. Click “Delete” in the Cells box of the Home tab (it’s to the left of “Find and Select”)

Video walkthrough

If you’d prefer a video demo, then watch this quick walkthrough (the link jumps you to the correct section).

How To Remove Duplicate Rows

1. Highlight column.
2. Go to the Data tab in the top menu
3. Click on “Remove Duplicates” in the Data Tools section.

Text Filtering On Notes Fields

You may have columns in the spreadsheet that contain notes. You can use text filters to search and filter for a single word or name within this column.

An example is the 23andMe Relatives Spreadsheet. This has a column with a list of surnames provided by each relative.

Many people aren’t familiar with text filtering in Excel, but they are very easy to use.

Here’s an example where I’ve chosen the Text Filters from the filter drop-down list. Then I choose “Contains” as the search option. This will find a word (or string) within a longer string of text.

How To Enable The Developer Tab In Excel

You don’t see the Developer tab in Excel? Follow these steps:

1. Expand the File menu in Excel
2. Click on the “Options” link at the very bottom
3. Click on “Customize Ribbon” (near the middle of the left pane)
4. Ensure that “Main Tabs” is selected at the top of the right pane
5. Turn on the “Developer” checkbox (near the bottom of the list)

How To Create A Macro In Excel

We have some articles in which we give you the code to put in a macro. If you’re not sure how to create the macro first, follow these steps.

1. Go to the Developer tab
2. Click on the left-most icon labeled “Visual Basic”

The top left box, the project window, shows folders and files where you will store your new code. By default, the current worksheet is highlighted.

This is probably Sheet2. If you saved or renamed the sheet, you’ll see your chosen name.

3. Create a new module

Click “Insert” in the top menu and choose “Module” from the drop-down. (Don’t choose “Class Module”).

The big window on the right is your new code page.

You can now either enter code or copy-and-pase some in this window.