Have some fun with Excel!
This post turned out quite long in the end, but it will really save you time if you use all these tricks and tips.
If you are an advanced Excel user, these quick tips may jog your memory, if you are a novice, just scroll down to find out how to use these tips.
About the NSW BDMThe great thing is that this is an index, and just a large database (like excel) that is searchable. This means its very compatable with excel. Yeah!!
If I know where a family is from, and remember back in the 1800's this did not change a lot, I use the box at the bottom of the criteria to help narrow down the search (see below).
Copy and paste each page into an excel spreadsheet.
- Don't for get to seperate the Births, Deaths and Marriages, by putting them on seperate sheets, or just put a heading at the top of each set.
- Copy and paste the column headings on the first page to make it easier to search, but don't worry about them after that.
BUT IT WENT ON FOR 112 ENTRIES:
If this happens to you do not despair, learn how to slice and dice the data by following some easy steps.
POWER USING EXCEL
HeadingsYou need to have headings on your colums so that it is easier to sort, fortunately the BDM has them ready made - if you forgot to copy and paste them, insert a line and put them in. Bold them and change the colour of the text so that Excel knows they are headings. By the way, Delete any columns you do not need. the top of my list now looks like this:
Ctrl FVery useful if you know what you are looking for! The Control F keys bring up the search box, this makes it quick if you are searching for a particular name. You cannot search two columns, so if you were searching for Ivy Nelson, I would just search for Ivy. Hit <find next> to find all the "Ivy's".
Data SortNow we are power searching. Go to the data tab.
First, highlight all the data you want to search, including the column headings.
Here is the data sort box:
Here is how I sort to bring all the families together in a logical way:
- Family Name
- Father's given name
- Mother's given name
- Given name
A couple of things to remember, to add a sorting level just click the <Add Level> key at the top left, and make sure the <My data has headings> is ticked.
Now I have all my families grouped together in a logical order, A-Z. Makes it a lot easier to find everyone.
Remove DuplicatesFor some reason you will find there are duplicates in the list, you can simply ignore these, but I do find it easier to remove them. I had to amalgamate two lists, after I sorted them they looked like this, with lots of duplicates.
After you click OK Excel will tell you how many duplicates it removed, just so you can feel good about it.
Now you should have a very clean list to start using.
Conditional FormattingOn the Home Tab, you will find Conditional Formatting.
Try Highlighting all the cells that contain one of the Surnames with a colour. It will help you divide the list into families.
Here is one I did, notice all the "Blackshaws" are now green.
So, we are beginning to use colour!
ColourColour makes it easier to see common things or uncommon things, so keep this in mind as you utilise colour. The two buttons to use are the FILL and TEXT colour buttons on the HOME tab.
How you use colour is an endless list, but here are some ways I colour code a list:
- When you have finished using an item, simply change the colour of the text to red.
- Fill each family group (where the parents match) with a colour.
- If there is a matching death record, change the colour of the text.