Have some fun with Excel!

Fun? Yes you can! I know lots of people find Microsoft Excel really scary, but trust me it's not that hard.  Last week I found a family I'm related to in Goulburn, so I used the NSW BDM Index (online and free) to find who was related to who and expanded from there.  Very hard to do without Excel, so in this post I am going to give you my top tips for using Excel to make life easier.

This post turned out quite long in the end, but it will really save you time if you use all these tricks and tips.

One of them is colour, nothing to do with sheep, but don't you love this picture - and how easy it is to see individuals?

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 BDM

The 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).

I just put the family name in, and if the family has lived in the area for a long time you will get pages of names and data.

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.

My Births for the Nelson & Blackshaw families (both mixed in together) looked like this at first
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

Headings

 You 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 F

Very 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 Sort

Now 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:

Click on <Sort> and this will appear:

Here is how I sort to bring all the families together in a logical way:

1. Family Name
2. Father's given name
3. Mother's given name
4. 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 Duplicates

For 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.

To remove the duplicates just click on <Remove Duplcates>

Then you need to decide the criteria to use.  I use them all just to be on the safe side.

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 Formatting

On the Home Tab, you will find Conditional Formatting. 

I use <Highlight cells Rules> Then <Text that Contains>

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!

Colour

Colour 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.

-------------------------

I hope this helps you with some of your research.  Do you have any other tricks for using Excel?  If you do, just send them to me in the comments box below and we can share them around.

 --------------------------------------------

Wondering what my list looks like?  When here it is - and quite a bit of work ahead for me.