I was working on a project with an administrator recently which involved mailing out to a long list of contacts. This was the second time that the organisation had mailed this list, but the administrator was typing in the address from scratch again!

My advice would be, if you are going to type the list anyway, you may as well type it up into a sheet of labels in Word, and then you can print it out as often as you like later. This would be my suggestion for someone with limited word processing skills.

For those with more skills, or more willing to learn them, I would suggest typing the list into a spreadsheet, with each line of the address in a new column. Here's an example of the layout:

You can then use this spreadsheet of data to create labels, letters and contact lists. When you need to update the data you only need to update the one Excel file, rather than a number of lists and letters.

Setting up a list in Excel

A spreadsheet has rows across the page, and columns down the page. By creating a list in Excel you are actually creating what is called a database, even though it is in a spreadsheet package. Each address in the row is called a record. Make sure that each separate line of address is in a separate column (this is called a field in database language). It is particularly important to put the postcode or area in a separate column because you can then order your list into geographical areas or postcode areas. While you may not need to have this function right now, you might find in the future that this would be very useful.

Give your list relevant column headings, avoiding spaces. This will ensure that you can mailmerge the list later on. Make the column headings bold so they stand out.

In my next post I will use an Excel list to mailmerge to a letter.

There are times when you have produced a wonderful spreadsheet with lots of clever formulae in place, which you really don't want to give to other people because they might make unauthorised changes.  You can easily protect parts of your spreadsheet from changes, and this is how you do it.

Have a spreadsheet containing some data handy.

In this example, I want to have empty cells that users can add data too, a formula then operates, and an answer is shown.  The cell containing the formula/answer is locked, but the data entry cell isn't. 

When you protect a worksheet it locks all the cells so they cannot be edited.  Therefore, before you lock it you have to set the editable cells to be unlocked.

Select the cells in the spreadsheet to be unlocked and accessble for data entry.

In the Home ribbon go to the number section and click on the little arrow in the bottom right hand corner.  You will get a familiar old formatting dialogue box.  Now click on the Protection tab.

Take the tick out of the box next to the word Locked and click on OK.

Now go to the Review ribbon and click on Protect Sheet.  (Entering a password is optional.  Without it any user can go in and unprotect the sheet at any time.  If you set one, it needs to be entered before you can unprotect the sheet.  Make sure you remember it!).

All cells, except for those you unlocked, should now be protected.  Try it an see!

You can see how this can be a really useful starting point for an online form.

Sorting data is one of the most useful features in Excel but it is surprising how often novices struggle with this tool.  This post is an outline of the basic sort functions.

Sorting is a lot easier if you have good quality data to start with.  Firstly, make sure that you have a header column, and make this bold.  This will help to separate it from the rest of the data.  Try to ensure that you do not have empty cells in your data.  If you sort a column it will only sort as far as the first empty cell.  Any cells below that will not be sorted.  This is a factor in other database functions in Excel so it is a good habit to get into.

Now that your table is well laid out, you can begin to sort.  To sort simply by column in alphabetical or numerical order, click in the column you want to sort and go to the Sort & Filter button:

You can choose A-Z or Z-A depending on what order you want the data sorted.  Please note:  Do NOT select the column.  If you do this it will sort the data in the column but not the whole table. All you need to do is click in the column that you want to sort by.  Then the complete table will sort too.

You may want to sort by multiple columns.  For example, you might want to sort by surname, and then by first name so that Ann Smith appears before Jane Smith in the list.  To do this, make sure you are clicked somewhere in your data table, go to Sort & Filter and select Custom Sort. 

Click on the first drop down arrow and choose the column heading you want to sort by, changing the order if necessary.  Then click on the Add Level button and you will be given a second drop down.  Click on this new drop down arrow and select the second column heading, changing the sort order if required.  This screen shot illustrates my example above.

You can carry on with further levels, but they become less useful the more you add.  I hope you find this post useful. 

This week I will be covering some short Excel tips that might help to improve the way you use this programme.  They are all very easy, but not always well known to less experienced Excel users.

Today we will look at wrapping text.  Sometimes you need to have a column heading that is quite lengthy, and yet you don't want to have particularly wide columns.  If you make the column narrower and then type in the next cell along you get truncating like this:

To get around this, select the cell and choose Wrap Text from the Home ribbon:

And this is the result (left).  The text will only wrap if the column is narrow enough, so you might need to reduce the width of the coloumn.

Tomorrow we will look at some simple sorting techniques

Not everyone likes the new Office 2007 look and its features.  I think Excel particularly has improved and has some great tools that have come out from behind the menus.  I covered some conditional formatting last week, in today's posts I want to touch on the tool next to it, 'format as table'.

This tool is so simple to use that I'm almost embarrassed posting about it, but I think it is more a case of pointing out that it is there. Look at the home ribbon, and in the styles section you will see the button 'Format as Table'.

If you open a spreadsheet and click inside the data, when you select a style for your table it will be applied to the data table that you clicked in.  In fact you can hover over the styles on offer to see them previewed in the data.  When you find the style that suits your needs just click to select it from the list and your table is formatted.

Note that if you click outside the data table Excel will not be able to find the cells to format.  Also if you have empty rows or columns in the table the formatting may not be uniform.  It is not good practice to have empty rows and columns in a spreadsheet and this is one of many good reasons why.

You can also choose to remove the formatting from some parts of the table.  To the left of the styles you can see a selection of Table Style Options.  A tick in the box means the format will be applied, removing the tick means the formatting won't be applied to those cells.

Have a play around with this tool and see how you like it.

A tool available in all Office packages that I feel is rather under used is the alignment feature for images and objects.  If you have several objects in a document/presention you can line them up so they are evenly spaced or align to the top or bottom of the objects.  Here's how you do it.

Select all the of objects you want to align.  To do this click on one object, hold you finger down on the shift key on the keyboard, and click on the remaining objects.  They should all be selected.  If you are having trouble achieving this, select each object, right click and turn text wrapping on.  You should then be able to select them all.

In Office 2007, when you select the pictures you get the picture formatting ribbon.  You will see the align button on the right.

In Publisher if you go to the Arrange tool at the top and choose align, you will get a very similar dialogue box (left).

Then you have to simply select the desired option.  As you can see you can align all of the selected objects across the top or bottom, or down the page to the left or right.  If you have more than two objects you can also distribute them equidistant from each other across or down the page.

So, a very useful tool to improve the look of your documents and presentations.

I intended to write this post about PowerPoint, and then realised that in fact the technique is the same for Word and Excel too, so you only need to learn it once to become an expert in three programmes!!  In earlier versions of Office you needed to insert these objects from the drawing toolbar, but in more recent versions from 2007 they have come out of hiding, so to speak.

In Word, Excel and PowerPoint you go to the Insert ribbon at the top and choose SmartArt from the Illustrations segment.

You are then given a very wide range of objects to choose from.  When you single click on each one in the list you will get an explanation of what they are used for in the right hand pane of the dialogue box.

Select the object you wish to use.  I'll choose the organisation chart which is the first one in the hierarchy category.  When you double-click on the option, or click on OK the object is inserted into your document/spreadsheet/presentation.  Click where it says [Text] to type in the required text.  Note when you select the object you get some new ribbon tabs at the top of the screen called Design and Format.  If you wish to change the size, colour or style of your object just click on these and experiment with what's on offer.

The default setting of the object is to re-size the text to fit the object.  If you put too much information in the text will become too small.

Note that in PowerPoint another way of inserting a SmartArt into a slide is to click on the icon when you create a new empty slide: