Recent Tips & Tricks

Access 2010 allows you to create a calculated field in a Table, rather than having to run a query or a report to carry out calculations. Where the calculated field refers to the values held in fields, those fields must be from the same table. The expression for the calculation is created in the Expression Builder.

• To create a calculated field in a table, switch to the Table Design View;
• On the Create Tab, select the Table Design button in the Tables group;
• Create the other fields, particularly those that will provide the data for your calculation;
• For your calculated field, enter the required Field Name and select Calculated as the Data Type;
The expression Builder will open to allow you to enter the Expression, selecting the appropriate Field Name, Operator, Function, and/or Constant .

Example expression: [Rate]*[Hours]
On each record, this field will multiply the value in the [Rate] field by the value in the [Hours] field.

Note that field names are enclosed in square brackets.

If you’re lucky enough to have Outlook 2010, it’s easy with Instant Search – just type the word(s) you want in the Search box at the top of the list. But this can give more results than you wanted, and it isn’t available in Outlook 2003.

In any version, make sure you can see the column headings of the message list (you may need to widen the list part of the screen). Then click a column heading and the messages will be grouped by that column.

So it’s easy to group by “From” or “Subject” but what if you want a combination? RIGHT CLICK on the column headings and choose “Group By Box”. Now drag several column headings into the area just above, in the required order – if you drag “From” to the Group By Box and then drag “Subject” to the right of “From”, the messages will be grouped by who they are from and within that, by Subject – it’s even clever enough to ignore things like “RE:” and “FW:” when grouping items with the same Subject. To remove the grouping, just drag the items back into the column headings. (Note: if your FIRST grouping item is a date you cannot add any more).

When you are dealing with a spreadsheet that you didn’t create yourself, it can be useful to locate the cells that hold formulas, particularly if you are checking for errors. Normally, you only see the formula contained in a cell when you select that cell: it appears in the Formula Bar.

On Excel 2007 and 2010, to show all the formulas, you can select the Formulas tab and click onto the Show Formulas button, which is in the Formula Auditing group. On Excel 2003, select Tools from the menu bar, followed by Formula Auditing, then Formula Auditing Mode. There is a shortcut on all versions: Hold down the CONTROL button and click onto the ` (grave accent) button, which is just below the Esc button.

If you simply wish to highlight all the cells that contain formulas, press the [F5] button (Go to), click onto the Special button and then select Formulas. This will select all the cells containing formulas, on your worksheet.

Excel has shortcut keys for practically everything but it’s hard to remember them all.

A few useful ones are: CTRL + 'copies the data from the cell above

CTRL + ; inserts today’s date (as a fixed value)

CTRL+SHIFT+$ formats the cell as currency CTRL+SHIFT+% formats the cell as a percentage

CTRL+SHIFT++ (CTRL and plus)inserts cells or rows (depending on what you have selected)

 CTRL+- (CTRL and minus)deletes cells or rows (depending on what you have selected)

You probably know that you can insert today’s date in a Word document, using the Insert Menu (Word 2003) or the Insert Tab (Word 2007) followed by “Date and Time”. But this is not always the date you need when dealing with documents that are often updated. Try using the Insert Menu (Word 2003) followed by “Field…” or the Insert Tab (Word 2007) followed by “Quick Parts…”, then “Field…”. In the Field dialogue, choose the Date and Time category, where you can select one of “CreateDate” (the date the document was created), “PrintDate” (the date the document was last printed) or “SaveDate” (the date the document was last edited and saved). Putting one or more of these into a document footer allows you to see instantly how up to date a printed copy actually is.

Don’t just insert a blank sheet and copy and paste the information you need… copy the whole Worksheet by dragging the sheet tab to where you want the new sheet to be and holding the CTRL key while you release the mouse button. Then you can adjust the new sheet as you wish.

This way, everything about the existing Worksheet is copied, including column widths, filter settings, page setup, headers and footers and so on.

Oh, and by the way, you can even drag the Worksheet to a different Workbook!

PowerPoint Tip

Animating your charts in PowerPoint 2007
Charts can be colourful and effective in displaying data, and you can bring them to life on a PowerPoint slide by animating the chart bit by bit: column by column or bar by bar. For example, if you have a column chart, you can present the chart so that each column is introduced one at a time, wiping upwards from the bottom. This is how you do it:

Click onto the column chart on the slide, to select it;
On the Animation tab, select Custom Animation;
On the Custom Animation Task Pane, select Add Effect
Click onto Entrance and then select Wipe
Ensure that the Direction is set to “From Bottom”
Select Effect Options for that item in the Custom Animation Task Pane
Select the Chart Animation tab in the dialog box that appears
Select By Element in Series or else By Element in Category
Check that Start Animation by Drawing the Chart Background is ticked
You can leave the Start at “On Click” allowing you to introduce each element when you are ready, as you run your presentation.

Have you ever received an email and thought “I need to put something in my diary to deal with that”, or “I need reminding to sort that out”.

No need to waste time re-typing things into your diary or task list. Just drag the email from your Inbox to your Calendar or Tasks folder (drop it on one of the buttons at the bottom left of the screen) and Outlook will create an appointment or task with the same subject and with the contents of the email in the Notes section so you know what it’s all about.

All you have to do is enter a date and save it. Then you can file the email knowing you won’t forget about it.

Oh, and if you receive an email from someone you’d like to add to your Contacts folder, just drag the email to the Contacts folder – job done (yes, all you experienced Outlook users, I know there’s another way to do that, but this does work as well!)