Fifty Office 2000 tips

More people use Microsoft Office than any other software suite. It's a huge and daunting package, even for those who have 'grown up' with it and remember the days when Word and Excel were independent products, and Access, Outlook and Powerpoint were just gleams in a developer's eye.

The current version, Office 2000, offers everything from 'smart' correction through email and intranet connectivity, to suite-wide integrated programming.

As always, the problem with software that is bristling with features is that you tend to get lost in the bristles while trying to find the features. This collection of 50 tips won't make you an Office expert overnight, but it is designed to make your Office experience easier, speedier and more satisfying.

We've included tips for overcoming irritations, saving keystrokes and mouse actions, solving problems, and exploiting features that perhaps you didn't know existed. We hope that, as usual, there will be something for everyone; from raw beginners to seasoned power users, from coping with junk email, through producing slick presentations, to performing case-sensitive searches in Access.

Although Office is primarily aimed at the business community, we haven't left the home user out. If you want to know how to create a digital photo album in just a few minutes, or get one-click access to sending an email to your best friend, read on.

We've grouped the tips into the five major components of Office: Word, Excel, Access, Powerpoint and Outlook, and with scrupulous even-handedness we've awarded them 10 tips each. However, although some of the tips are assigned to the section where they'll be most useful - for example, proofing tips in Word and graphics tips in Powerpoint - many of these, such as the drawing toolbar tips that are listed under Powerpoint, can be used in one or more of the other applications.

Word

1. Avoid master documents
Tempting though this may be, there have been many reports of lost or corrupted data when using master documents. If you want to split a document into chapters - possibly with chapter/page numbering so you don't have to reprint the entire document when one chapter is edited - use section breaks instead, or split the document into separate, independent files.

2. Updating hyperlinks
By default, Word 2000 turns anything it thinks is an email address or a URL into a live, working hyperlink. But there is a snag to this: if you mistype the address, and then correct it by using the cursor keys to 'get in' and edit it, the underlying hyperlink won't update. You need to right-click on the link, then go to Edit Hyperlink.

3. Getting rid of hyperlinks
If you find the auto-hyperlink feature irritating - after all, it's not much use in a printed document - you can turn it off from Tools, Autocorrect, Autoformat As You Type. You will still be able to create hyperlinks by highlighting text or graphics then pressing Ctrl K. Although Word will fill in anything that looks as though it's a valid address as the link, you can override this manually and have, for example, 'Visit my website' or 'Email me' as the visible title of the link.

4. Getting to know Autocorrect
This is one of the most useful, and most frustrating, features of Word. If you frequently mistype a word - e.g. intenet for internet or rebbot for reboot - right-click on the red-underlined word and then, instead of clicking the relevant suggestion, go to the Autocorrect option on the context menu. You'll see the same suggestions, but your mistake will get added to the Autocorrect list. You can also remove entries (Tools, Autocorrect ...) and make exceptions, so that PCs, for instance, doesn't get changed to Pcs.

5. Easy column selection
You probably know that you can select columns in a table by clicking at the top of them. But did you also know that you can do this for ordinary tabbed text? Alt and drag selects a rectangle of text anywhere on the page.

6. Selecting lots of text
Trying to select more than a window-full of text can be frustrating, especially with a fast display card, as the highlight tends to shoot back and forth past the intended selection. Left-click once where you want to start, use the scroll bar to navigate until the end of the intended selection is visible, and then Shift and click again.

7. Saving grace
Despite Word 2000's single document interface, there is a way to save or close all open documents without closing Word. Hold down the shift key as you drop down the file menu and you'll see two new commands, Save All and Close All.

8. Keeping tabs on the File, New dialog
If you want to create your own custom tabs on the File, New dialog, it's easy. Just navigate to your main template folder in Windows Explorer and create a new folder within it.

9. Careful formatting
It's a good rule to use paragraph or character styles to format text, rather than applying formatting directly. This saves time in the long run and keeps documents consistent. However, rules were made to be broken, and there may be times when you've created some custom formatting that you'd like to repeat. If you click the Format Painter button (it looks like a brush) then 'dip' it in the formatted text you want to copy, you can then 'paint' other text by dragging the brush through it.

10. What's it all about?
On a related note, you can see a list of the paragraph, character and direct formatting of any character by clicking on the 'What's this' button (arrow and question mark) then clicking in the text. Click on the button again (or just resume typing) when you've finished.

Powerpoint

11. Get organised
If you're used to organising your presentations in Powerpoint's outliner, then remember that you can also use Word's rather more powerful equivalent. Each top-level entry denotes a new slide, with lower levels forming nested, bulleted lists. 'Normal' text is ignored. A tip-within-a-tip: don't bother trying to import the Word file into Powerpoint. Instead, while still in Word, go to File, Send To, Powerpoint.

12. Phototastic
The free Photo Album add-in is available from http://officeupdate.microsoft.com/2000/downloadDetails/
album.htm. This lets you create a photo album from images on disk, a scanner or a digital camera. You have a choice of the number of pictures per page, frame style, backgrounds and captions. The last are generated from the image file name, and the whole business takes just a few mouse clicks.

13. Pack up and off you go
Having done this, you can use the Pack and Go Wizard to send the entire presentation or album to someone else (File, Pack and Go). If they don't have Powerpoint, you can include the Powerpoint standalone viewer. There is also an option to span multiple floppy disks. This takes time as the files involved are large, but the process just means inserting new disks when prompted.

14. Make it simple for the recipient
Unpacking the viewer and presentation is not granny-proof, so it's wise to include instructions. Two files need to be sent: the ppz file itself and pngsetup.exe. The recipient needs to run the latter, which will ask for a destination folder. This has to be typed in as there's no Browse button, and the folder name must be no longer than eight letters, without spaces. This will decompress the various files needed (again with prompts to change floppies, if applicable), and the recipient can then run ppview32.exe from the aforementioned destination folder. This will produce a standard File Open dialog with a ppt file visible in the contents. Select this, click Show, and the full-screen slideshow will begin.

15. Standalone toolbars
When you use the drawing toolbar, you can drag the nested palettes off the AutoShapes menu to create standalone toolbars. This tip (and the next two) work in all Office situations where the drawing toolbar is visible.

16. Getting sticky with it
If you double-click instead of single-click on a shape tool, it becomes 'sticky': i.e. you can continue to use the tool to create further shapes without having to select it each time. Click the tool again, (or any other tool) to un-stick it.

17. Inherently stylish
Having chosen fill, line styles and colours for a drawing object, you can right-click and set Autoshape Defaults so that all new shapes, except for text boxes, will inherit these styles,. Note that this exception doesn't work for Word and Excel.

18. Useful shortcuts
In addition to the standard Office keyboard shortcuts, Powerpoint has a few that are all its own. Ctrl M inserts a new slide, Ctrl D duplicates the current slide, Ctrl G toggles the guides on and off, and F5 starts a slide show.

19. Pause in black and white
If you're giving a slide show on screen, you can pause the show by pressing the full-stop key (or b). The screen will go black. Press the key again when you are ready to continue. If you'd prefer a white screen, press the comma (or w) instead.

20. The Office Assistant may be useful after all
Designing a good presentation takes special skills. As well as being a good communicator, you need a modicum of artistic talent if you plan to stray from the predefined templates. The much-maligned Office Assistant can be quite useful here, with the 'lightbulb' suggestions offering useful advice on layout and spotting potential problems. You can fine tune the rules from Tools, Options, Spelling and Style.

Outlook

21. Taking care of the journal
Among Outlook's many talents is the ability to keep a 'journal' of meetings and task requests, emails, and all the Excel and Word files you work with, noting the time and date of opening, saving and closing. Unless you really need this facility we recommend that you turn it off (Tools, Options, Preferences, Journal Options). It can add substantially to the time taken to open, close, and save files: as the journal grows, so does the delay.

22. Erase your history
Even with all the journal options turned off, you can still see a history of mail sent and received on a per-contact basis: go to the Activities tab on the contact's details.

23. Updating the date
Don't go trawling Tools, Options if you want to show the days of the week in the Calendar weekly view: this option is set outside Outlook. Go to Windows Control Panel, Regional Settings, Date and set the Long Date style to 'dddd mmmm yyyy'. The change will be reflected in Outlook immediately.

24. Plain text makes sense
Keep your emails lean: plain text messages are less than a third of the size of HTML or Outlook Rich Text, so you'll be saving both yourself and the recipient storage space and connect time. In addition, HTML and Rich Text messages may contain information that is unreadable by non-Outlook users. You can set this globally in Tools, Options, Mail Format. For individual recipients it is set in the General tab of their Contacts entry, or for individual messages from the Format menu in the message window.

25. Composing offline
If you're working from a dial-up connection, then it makes sense to read and compose your messages offline. You can set this from the File menu. Messages you compose will be stored in the Outbox until you click the Send/Receive button which will then instigate a dial-up connection. You should also check the Tools, Options, Mail Delivery dialog. Clearing the 'Check for new messages every ...' and setting the 'Hang up when finished ...' options will greatly reduce your online costs.

26. Adding addresses to the toolbar
If you frequently mail some of your contacts, they can each have their own toolbar button. From Tools, Customise, select the Mail Message command in the File category. Drag this onto a toolbar and release. Right-click and from the Context menu, first type a name for the contact, then make sure Image and Text or Text only is selected. Finally, from the same Context menu, go to Assign Hyperlink, Open and fill in the email address and, optionally, a default subject. Close the Customize dialog, and your new button will be ready to instantly launch a pre-addressed message.

27. Getting rid of Spam
You can opt to delete certain types of message without reading them. This can be useful, say, if there's a known virus going around, or you are being spammed with junk email. Run the Rules Wizard from the Tools menu and follow the prompts. There are too many options to go into detail, but it's all self-explanatory. One point to watch is that if you tick more than one filtering criterion, such as 'Specific words in the subject' and 'Specific words in the body', both conditions must be met. If you want either to apply, make separate rules.

28. Building your contacts
When you receive an email from someone, it's easy to quickly add their details to your contacts list. Double-click the message title in the In Box to expand it to a full window (this won't work in the multi-pane preview mode). Right-click on the sender's address at the top, and choose 'Add to contacts'.

29. Highlighting spam
Another way to deal with spammers is to hit the Organize button while viewing the Inbox, then turn on the Junk email feature. This gives you the option to either colour suspected junk messages, or move them to a specified folder. Having turned this feature on, you can right-click on message headers in the Inbox, and add the sender to the junk list. The same applies to 'Adult' mailings.

30. Get to know the shortcuts
Finally, Outlook has some handy keyboard shortcuts. When you're looking at the calendar, Alt Up Arrow takes you back a week, Alt Down, forward a week. Alt Home goes to the beginning of the week, Alt End to the end. Alt Page Up goes to the start of the month, Alt Page Down to the end.

Access

31. Discover the Zoom key
Access has a habit of presenting information in grids that only show part of each field. Of course, you can fiddle with font sizes and column widths, but a handy tip is to press Shift F2 to zoom into a field. What really happens is that the field contents open in a separate window, making it much easier for serious editing. This also works in the Query designer to save you typing long expressions into a tiny Criteria field, for example.

32. Don't store images as OLE objects
The official way to store images in Access databases is by using OLE (object linking and embedding) objects. Although this is easy to do, there are many disadvantages, including a bloated database size, unpredictable behaviour on different systems, and problems accessing the graphic using code. A better way is to keep the images as external files, storing just a path name in the database, and using the Image control to display the graphic on a form. If you need to store the image itself within the database, save it as a BLOB (Binary Large Object) rather than an OLE object. You can display it by writing code that saves the image to a temporary file before showing it on a form. Example code is in Microsoft's Knowledgebase or is widely available on the web. In this case, you can get better performance by keeping images in a separate Microsoft database (MDB) and linking the table.

33. Avoiding nulls
In theory, null values are a 'good thing' because they let you distinguish between unknown and empty values. In many cases, though, null values are a nuisance. To prevent them in a field, open the table designer. For text and memo fields, set Allow Zero Length to True and the Default Value to "" (two double quotes). For other field types, ensure that there is a Default Value. Note that this will not affect existing records. If you have to work with null values, use the IsNull function or the special criteria IS NULL and IS NOT NULL in the query designer. In general, if you are getting unexpected query results, null values are often the cause. Fields with null values will never be selected by a query unless you specifically ask for them.

34. Do case-sensitive searches
Access is generally regarded as case-insensitive. However, you can do case-sensitive queries using the StrComp() and InStr functions. Use StrComp to match a whole field, and InStr to match any part of the field. For example, here's how to search for 'van' but not 'Van' in an Artist field. Open up a query in design mode, add the required fields to the grid, and then enter the following at the top of a blank column:
InStr(1,(Artist),"van",0)
Access will prefix it with "Expr1:", but don't let that worry you. Now enter:
>0
in the Criteria row and run the query. This will return 'van Eyck' but not 'Van Gogh'.

35. Control start-up macros
In an Access database, if you name a macro AutoExec it will run automatically when the database is opened. If you press Shift, the macro will not run. If you are really insistent, you can prevent Shift from bypassing the start-up code by setting the AllowBypassKey property to False. Unfortunately, you have to do this using Visual Basic code, but a full example can be found in online help. Search for AllowBypassKey.

36. Use sub-queries in SQL
SQL sub-queries are a powerful technique for data manipulation. For example, imagine you have two versions of the same table and you want to append rows from one to the other, but only where they do not already exist. Assuming a primary key field called ID, here is the SQL:
INSERT INTO artists
SELECT NewArtists.*
FROM NewArtists
WHERE NewArtists.id Not In (select Artists.id from Artists);

37. Decompile mode - an undocumented recovery technique
Stuck with a corrupt MDB? According to Microsoft, the undocumented decompile mode for Access 2000 can fix corruption that the ordinary repair utility cannot manage. To use it, run Access with the command-line argument:
/decompile "dbname"
where dbname is the full path to your database. You can do this by editing a shortcut. This tricks Access into thinking it must change the binary format. Note: before trying this or other repair attempts, make a backup of the errant MDB.

38. Split data and code
If you share an Access database over a network, consider using one MDB for all the forms, reports, macros and VBA code, and another for the data. Use the Link table feature to attach the database tables to the application MDB. Now you have the option to store the application MDB on workstations while keeping the data in a shared location, thereby speeding up performance. The downside is that if you update the application, you must take care to replace all the copies, perhaps using a login script.

39. Defrag before compact
Two ways to improve performance are to defragment your hard disk and to use the Compact and Repair utility in Access to optimise an MDB. When you do this, it is best to defrag first. If you Compact first, the MDB is likely to be surrounded by used disk space, slowing access when data is added. Defragment followed by Compact is likely to leave the MDB with adjacent free space, which is the ideal scenario.

40. Use indexes with care
Indexing a column in a table speeds searches on that column, but every index you add slows down database updates. The value of an index therefore depends on how the data is used. If you have lengthy update routines on a large database, you can often improve performance by removing indexes other than the primary key before the update, and rebuilding them later using VBA code to automate the process.

Excel

41. Getting columns in shape
To quickly adjust the width of various chosen columns, hold down Ctrl as you click the column heading letters, then drag a heading boundary from any of the columns. A tip box will display the column width. To adjust the width of adjacent columns, hold down Shift as you click the first and last columns then drag the right-hand boundary. These tips apply similarly to rows.

42. Formula creation
To create a formula, click the often-neglected Paste Function tool (marked fx) on the Standard toolbar. Select a function and a description appears in the Paste Function dialog box. The Help button leads to examples of use of the function. As an alternative to this tool, click the equals sign in the Formula Bar and the Name Box on the left will list the most recently used functions.

43. Auto-calculation

For quick calculations on a worksheet, use the AutoCalculate feature. Select a range of figures and a total of them will appear in the Status Bar. Right-click on this total and you can change it to an average or a count of the numbers in the range. AutoCalculate can also show you the biggest or smallest number in the range. If the Status Bar isn't displayed, select it on the View menu.

44. Organising drawn objects
To line up drawn objects like rectangles with worksheet cells, first click Draw on the far left of the Drawing toolbar, then Snap, To Grid. To line up drawn objects with each other, click Draw, Snap, To Shape. To line up two different sized drawing objects with each other select them (by holding Shift as you click them both) then choose Draw, Align, Align Bottom.

45. Quick charts
The fastest way to create a chart is to select the full range of a table (including labels) and press F11. Excel will draw a column chart on a new interactive chart sheet, with the x and y axes labelled, colours chosen for the data series, and a legend box in place. You can right-click on any part of the chart to change any aspect of it.

46. Sorting out problems with links
You can often solve problems with links to other workbooks or Office applications under Workbook Options. Find this under Options on the Tools menu. Make sure the Update Remote References box is checked. If you check the Save External Link Values box you won't have to worry about maintaining links. However, if the link is to a large range it will greatly increase the size of your workbook file.

47. Simple sub-totals
To create sub-totals in accounts, use the function =SUBTOTAL(9,B4:B9). The 9 is a function number and represents SUM. The range to sub-total in cell B10 is B4 to B9. You could repeat this for, say, B11 to B16 with another sub-total in B17. Then if you click on cell B18 and the AutoSum tool it will display the total of cells B10 and B17 only.

48. Getting decimal
To convert pounds and ounces to kilograms, use the CONVERT function found in the Analysis ToolPak. Enable it by using Add-Ins on the Tools menu. If the pounds are entered in cell A2 and the ounces in B2 then the formula would be
=(CONVERT(A2,"lbm","g")+CONVERT (B2,"ozm","g"))/1000
Here lbm and ozm, entered in quotes, mean convert from pounds and ounces to g for grams.

49. Deleting versus clearing
Confused about the difference between deleting a cell and clearing a cell? If you click a cell and then press Delete or the Backspace key, the cell's stored value is removed but its formatting and comments remain. If you clear a cell, everything goes and it's as blank as a cell on a new worksheet. To do this, press Alt e then a a (the Alt key with 'e', then 'a' twice).

50. Perfect printing
If you have a multi-page worksheet to print and you want the column titles or list field names to print on every page, click the worksheet and choose File, Page Setup, and then the Sheet tab. Click the red arrow in the 'Rows to repeat at top:' box, then click on row number 1 (or hold Shift and click on 1 and 2). Then click OK.