Some advanced Excel magic

Warning, once you start using Excel, you may get as hooked as I am!  Here are a few more tricks and tips that may be useful when you have a lot of data to get through.

How to separate data into more than one column, remove hyperlinks, start to use filters, and get the column and rows the right size automatically; as well as how to apply these to genealogy.

 Separating Data from one column to two

This really is for advanced users, and before you do it I recommend you make a copy of your data on a separate sheet.  I tell you this from experience in mucking it up!  Undo does work on this process, but I have learned to err on the safe side.  Once you have done it, you will find it very easy from then on, and useful from time to time.

When you have data in a column like this registration info from the NSW BDM  -  17654/1882  -  it might be useful to separate the number from the  date so that you can sort by year, or include year in your sorting.  To separate the data, you need to tell Excel exactly how to do it.

To do this, there must be a character that can be used as the indicator for separation, this is usually a comma, space, and in this case </>.

Here is how to do it:

1. First insert a column to the RIGHT of the data you want to separate
2. Then highlight the data NOT the headings
3. Go to the Data tab
4. Click on the <Text to Columns> button
5. A Wizard box will appear
6. Make sure the first box, Delimited, is checked
7. Click Next
8. Now it gets tricky, you need to choose which character is the indicator for separation.  To use </>, click <other> and type  /  into the box next to it.  Of course if your data is separated by a different character use that, or click on one of the boxes that matches it (eg. comma).
9. At the bottom of the wizard, you will automatically see the data separate.  There should be a line indicating a new column.   If it looks correct, click Next
10. Then make sure <General> box is selected and click Finish.  
11. Click Yes in the final box asking if you want to continue. You should now have the registration number in one column, and the date in the one next to it.  Don't forget to put a heading onto the new column, and to delete the extra copy if you made one.

 Here are the pictures of the process from start to finish, and how the data changes.
 

 

Remove Hyperlinks

Easy, peasy, just:

• Highlight the data with hyperlinks in it
• Right click your mouse and 
• Click on <remove hyperlinks>

 Get columns the right width

1. Highlight the columns you want to use
2. Place the cursor on the line between two of the columns on the header (see below) until you get the cross with arrows

 

1. Double left click your mouse and the columns will adjust.

 For row height, you can do the same thing, but put the arrow in between the rows.

 

  Data Filters

Turning on Filters

 Data filters are one of the most useful tools in Excel when you are dealing with a large number of entries.  To put filters onto a table simply:

1. Go to the data tab
2. Highlight the data you want to add filters to
3. Click on Filter at the top of the page

Now you will have little arrows (the filters) on your data.

• To turn off the filters, you simply click Filter again.

 Using the Filters:

1. Click the arrow in the table header of the column you want to filter.
   
2. In the list of text or numbers, uncheck the (Select All) box at the top of the list, and then check the boxes of the items you want to show in your table.
   
   
   Some ways to use filters:
   
   • Filter data by the mother or fathers name
   • Filter by surname
   • You can use two filters, so you can filter by both father and mother's name
   • Filter by year.
   • Use the <text filter> option to create a custom filter and see only those born in a particular decade.  To do this use the <begins with> filter and put in the first two digits of the decade (eg.19 for the 1900's).
   
   
   
   
   --------------------------
   
   
   I do hope you have some fun with Excel and enjoy playing around with genealogy data, and don't forget that Microsft has an extensive instruction manual online.  If you find any more tricks and tips just put them into the comments section below and we can share them around.