Meeting Report: Excel for Family Historians
Speaker: Stephen Miller (Webmaster)
At this session, we explored how Microsoft Excel can be used effectively to support family history research, particularly for organising and analysing data from sources such as parish registers and certificates.
Overview
The talk began with a brief history of Excel and how it has developed into a powerful tool capable of handling large datasets—far beyond earlier limitations.
A simple ring binder analogy was used to explain structure:
- A workbook as the binder
- Worksheets (tabs) as sections
- Rows as individual records
- Columns as specific data fields
This helped attendees visualise how to organise genealogical data clearly.
Worksheets and Layout
We looked at:
- Creating, renaming, moving, and copying tabs
- Keeping different record types (e.g. baptisms, marriages, burials) on separate sheets
Emphasis was placed on good data structure, ensuring each column contains only one type of information to allow proper sorting and filtering.
Handling Dates
A key issue discussed was Excel’s limitation with dates prior to 1 January 1900.
Recommended approach:
- Store dates as separate day, month, and year columns
- This ensures accuracy when working with historical records
Working with Rows and Columns
Practical demonstrations included:
- Resizing columns for readability
- Inserting and deleting rows and columns
- Keeping layouts tidy and consistent
Sorting and Filtering
Filtering was demonstrated as a powerful way to analyse data:
- Filtering by surname, place, or year
- Applying multiple filters at once
This allows researchers to quickly isolate specific groups of records without manually scanning large datasets.
Using Real Examples
Sample spreadsheets were shown based on:
- Baptism records
- Marriage entries
- Burial registers
This helped illustrate how Excel can mirror traditional record formats while remaining searchable and flexible.
Formatting
Basic formatting techniques were covered:
- Clear headings
- Consistent text styles
- Improving readability without overcomplicating the sheet
Introduction to Formulas
The session also introduced simple formulas and how they can help clean and manipulate data.
Examples demonstrated included:
| Input | Result | Formula | Description |
|---|---|---|---|
| left string | le | =LEFT(A2,2) | Extract characters from the left |
| right string | ng | =RIGHT(A3,2) | Extract characters from the right |
| spaces | spaces | =TRIM(A4) | Remove extra spaces |
| surname | SURNAME | =UPPER(A5) | Convert to uppercase |
| forenames | Forenames | =PROPER(A6) | Convert to proper case |
| Jan | 1 | =MONTH(A7&1) | Convert month text to number |
| john peter smith | john peter | =TEXTBEFORE(A8," ",-1) | Text before last space |
| john peter smith | smith | =TEXTAFTER(A9," ",-1) | Text after last space |
| — | smith, john peter | =TEXTJOIN(", ",TRUE,C9,C8) | Combine text with separator |
These examples showed how Excel can:
- Split names into components
- Standardise formatting
- Prepare data for sorting and analysis
Conclusion
Excel was presented as a valuable companion tool for family historians. While it does not replace dedicated genealogy software, it is particularly useful for:
- Transcribing records
- Organising large datasets
- Cleaning and standardising data
- Exploring patterns and connections
The session provided a practical introduction aimed at helping beginners feel confident using Excel in their research.