Microsoft Office 97 Tips and Tricks

Office: Problems Opening Files

When opening a file with a long file name, you get one of the following messages -

Path and file name could not be found. Check the spelling of the name, and verify that the file location is correct.
If you are trying to open the file from your list of most recently used files on the File menu, make sure the file has not been renamed, moved, or deleted.

Word cannot open the document. Try one or more of the following:
* On the File menu, click Open to open the document.
* Make sure the document has a *.doc extension (path and file name).

The path or file name for partial path and file name is invalid.

To work around this problem, follow these steps:

1. Double-click the My Computer icon on your desktop.
2. On the View menu, click Options. In the Options dialog box, click the File Type tab.
3. In the Registered File Type list, select Microsoft Excel Worksheet and click Edit.
4. In the Edit File Type dialog box, click Open from the Actions list. Click Edit.
5. In the Editing Action For Type dialog box, click to clear the Use DDE check box. Type "%1" (including the quotation marks) at the end of the command line that is displayed in the Application Used To Perform Action box. For example, the following command line
"C:\Program Files\Microsoft Office\Office\Excel.exe" /e
would be changed to:
"C:\Program Files\Microsoft Office\Office\Excel.exe" /e "%1"
6. Click OK and then click Close twice.
7. Repeat steps 2 through 6, clicking Microsoft Word document in step 3.
8. Repeat steps 2 through 6, clicking Microsoft PowerPoint Presentation in step 3.

Word: Selecting Text

In Microsoft Word, doing CTRL+A will select the entire document. But if you want to be more selective, this is where the F8 key comes into play. Tap your F8 key the first time to turn on this feature; then move your cursor right or left to select a single character. Now, press F8 a second time to select the entire word. F8 tap number three will select the sentence. Four F8 presses selects the paragraph. And if you want the whole document, hit F8 five times. Reverse the process by pressing F8 with the SHIFT key held down.

Word: Autogenerating Text Strings

This probably rates as one of the most pointless features in Microsoft Word, but if you want to quickly fill a page of text, this is the tip for you. On a blank line type "=rand(200,99)" (without quotes) and press the Enter key. Suddenly, your screen will fill with 200 sets of 99 instances of "The quick brown fox jumps over the lazy dog". Changing the numbers will change the output accordingly.

Word: Editing Text in Print Preview

You know the situation, you're in Print Preview ready to print a document, when you notice a typing mistake. Instead of exiting out of the preview screen, just click on the Magnifier icon in the toolbar. The mouse pointer should change from a magnifying glass to a normal pointer and should let you do some quick editing before printing.

Word: Hangs Opening a Document

When you open a Word document, the document appears to load OK, but Word then locks-up, your only option being to use CTRL-ALT-DEL to close Word down. Other Word documents open with no problem.

The most reliable way to get round this I've found is to open the document using WordPad, and to save it in Rich Text Format (RTF). You should then be able to open it again in Word and resave it as a Word document. The only downside is you might lose some of the formatting in the document.

Access: Cannot Print Black Lines on Report

When you try to print a black line on a report, the line is not printed. However, if you view the report in Print Preview, you see the line. If you change the line's BorderColor property to a colour other than black, the line is printed.

When the Spool Data Format setting for the printer you are using is set to RAW, black lines will not print on your report, and gridlines will not print on your table, query, or form in Datasheet view. There are 2 solutions for this problem, either change the line colour to another colour or change the printer spool settings to EMF, as follows -

• On the Start menu, point to Settings, and then click Printers. Select your printer.
• In the Printers dialog box, using the right mouse button, click your printer, and then click Properties.
• In the Properties dialog box, click the Details tab, and then click Spool Settings.
• In the Spool Settings box, in the Spool Data Format box, change the setting to EMF. Click OK. Click OK again to close the Properties box.

Excel: Quick Sums

I only discovered this Excel trick a few days ago and I'm amazed it's not more wildly known. When you have a set of numbers, you can perform simply arithmetic functions on it without using formulas. Select a range (in a column or row), then take a look at the middle of the status bar below. You'll see that the 'Sum' has been auto-calculated. Right click on the status bar and take a look at your options: Average, Count, Count Nums, Max, Min, and (of course) Sum. As easy as that, but a guaranteed time saver.

Office: Most Recently Used List

When you go to the File menu in Word, it always lists the last few documents that you've been working on. To disable this feature, in Word, select Tools | Options | General. Then, take the checkmark out of the "Recently used file list" field. Follow the same procedure for Excel and PowerPoint. Your Office tracks will now be covered.

Excel: Keyboard Shortcuts

Excel: Auto-Filling a Cell

Right-click a cell and select the 'Format Cells' option. Flip over to the Alignment tab and select 'Fill' from the 'Horizontal' menu. Click the OK button. Then enter a single character into the cell and press Enter. Make the column width as long or short as you wish, the area will always be flooded with that character.

Excel: Selecting Certain Types of Cell

Say you want to select all cells which contain a certain type of data. Press CTRL+G and click the 'Special' button. If you want to format all the numbers-only cells, select 'Constants' and remove checkmarks from all but the 'Numbers' option. Once this is done, you'll be whisked back to the main document - with all the appropriate cells highlighted. From here, you can format them at will.

Word: Custom Dictionary

Ever added a misspelled word to the Custom Dictionary? To remove it search for the file CUSTOM.DIC and open it in Notepad. simply remove the line that contains the word you want to get rid of and save it.

Excel: Hiding Cell Contents

There are times when you need to know the answer but don't want anyone else to see it. Want to hide cell contents in the spreadsheet and from the printer - but not in the Formula bar? Select the cells you're wishing to keep private, open the Format menu, click Cells, and then go to the Number tab. In the Category list, click Custom. Then, in the Type field, replace whatever is in there with: ;;; (3 semi-colons) , If you edit anything else in those cells, be sure to do it in the Formula bar.

Office: Mastering the Amersand

Some programs may interpret the amersand, "&", differently. In some programs "Fred & Son" will be displayed as "Fred Son". The solution is to double it, so "Fred && Son" will give you the desired result.

Word: Creating Non-Breaking Spaces

Picture the scene. You're working on an important document, when suddenly the line wraps in an inappropriate spot. You need to put a space between two words, but you need them to be on the same line. It's time to use a non-breaking space. In Microsoft Word, this can be done by holding down CTRL+SHIFT when you tap the space bar. No matter what formatting is applied in the future, these words will never be split apart (unless the non-breaking space is replaced by a normal space).

Word: Sorting a List

Say someone sends you a list which you want to sort into Alphabetical order. Click on the Table menu and choose Sort. Make sure Sort By is set to "Paragraphs", Type is set to "Text", and you've selected either Ascending or Descending, then click on OK. If there are secondary conditions to be met, then you'll need to select "Field 1" in the first Sort By option.

Excel: Creating a Range of Numbers

To create a range of numbers in Excel, firstly type in a number into a blacnk cell. Select the cell and hold down the CTRL key. Move the mouse over the bottom right corner of the cell and you should see the cursor change to a big plus sign with a little plus sign. Now, click and drag either vertically or horizontally. Down or right will increase your initial number; up or left will decrease it.

Word: Comparing Documents

If you've got two versions of a document and you are not sure what the differences are then try the Compare Documents feature. Load one of the documents up and Click on the Tools menu, select Track Changes, then Compare Documents. It will then prompt you to select the document to compare with the current document. Once you've selected the other version of the document it will then highlight the differences for you.

Office: Open the 'Close All' Feature

When you want to close a document then you simply Click on the File menu and choose Close. But what if you have 15 documents open? Then the SHIFT key becomes your friend. Hold down the SHIFT key in Word and click on the File menu and you will see two new options "Save All" and "Close All". You can do the same in Excel, but there you only get the "Close All" option.

Word: Using the Work Menu

As everyone knows the Recent Documents menu stores the last 4 documents that you've worked on. Now you can expand that out to the last 9 documents by clicking on Tools \ Options \ General, then editing the "Recently used file list" field.

But what if there are a number of documents that you always work on? Then you need the Work menu. To add the Work menu to your toolbar, click on the Tools menu, then choose Customise, Command, and Built-in Menus. From the right-hand pane, drag & drop "Work" somewhere onto your menu. Close all the dialogue boxes and open one of your favourite documents. Then click on the newly-created Work menu and select the "Add to Work Menu" option. Essentially this is a "favourites" feature for Word that very few people know about.

Excel: Reset Spreadsheet Formatting

If you see a spreadsheet that's visually too loud or want to take care of any rogue formatted cells, then press CTRL + A to select all, then click Edit, Clear, Formats, to reset all the formatting back to default.

Excel: Switching the Function of ENTER

When you enter a number into Excel and tap the ENTER key, by default, it will change the focus to the cell directly below. What if you think that it should move to the right? Select the Tools menu, then Options, and Edit. Either change it to another direction or turn the feature off altogether.

Excel: Transposing Data

Have you ever wanted to take data that is listed vertically and change it so it is listed horizontally. Then select the cells and goto Edit and Copy. Then select the destination and click on Edit and Paste Special. Make sure that the Transpose option is checked.

Word: Selecting Blocks of Text

In Word, hold down CTRL+SHIFT+ALT while you select an area of text, and instead of highlighting a sequential series of words, you are highlighting a rectangular area. This will be useful to those of you wanting to copy column characters not sitting inside a table.

Word: Keeping Your Place

Pressing SHIFT+F5 will cycle through the last 5 cursor positions in your document, handy if you loose your place.

Word: Tracking Changes

Passing a Word document around an office in paper form allows you to easily see who made changes to the text, but electronic format is a different story - unless you're familiar with the Track Changes feature. To see how this works, enable Track Changes by clicking Tools - Track Changes - Highlight Changes - Track Changes while editing. In a blank document, the only difference you'll notice right off is the TRK that lights up in the status bar at the bottom of the window. When you begin to fill the screen with text, however, you'll see that everything is colourised and underlined. This notes a change made to the original document since Track Changes was enabled. That said, be sure you have your first draft completed before you enable the feature and pass it along to the next stop. Once the modified version returns to your desk, each person's changes will appear in a different colour. If you mouse-over the text, up will pop the author of the changes and the date/time the modifications were made. Now, right click the hacked text and you'll have the option to accept or reject the changes.

Word: AutoCorrect

Word automatically recognises text you type and can change it to symbols and special characters. For instance typing (tm) automatically inserts a trademark symbol. The same is true with (c) for copyright and (r) for registered trademark. The same can be done for inserting arrows into documents. Entering: --> ==> <== <=> will result in instant arrows. This is all done through the AutoCorrect feature. When you select Insert \ Auto Text \ Auto Text from the toolbar, the AutoCorrect dialogue is launched. Flip to the AutoCorrect tab. If you peruse the list under the "Replace text as you type" checkbox, you'll see the dozens of different words that are defaulted to be replaced. You might notice that the list is full of common typing and spelling errors; they'll be instantly replaced when a mistake is made.

Excel: Interpretation of Fractions

Is 1/4 a fraction or a date? Excel will normally make a pretty good guess at it but it can go either way, more often than not though it will interpret it as a date. To tell Excel that it is a fraction, add a zero to the front, then excel will know that "0 1/4" is definately a fraction.

Another annoying fraction fact lies in how Excel formats a number when you change the format of something like 4.233 to display as a fraction, "4 1/4" is displayed. If you're going to use a precision of 3 decimal places, you likely want better accuracy than that. Click Format, Cells, Number tab and then choose Fraction from the list of number formats. Up will pop a list of fraction display choices, allowing you to change from the default of one fraction digit to two or three, turning 4.235 into "4 4/17" or " 4 47/200". If your purpose is for inch-based measurements, then you might want to specify that all fractions should be in eighths or sixteenths.

Excel: Using the Scroll Lock Key

When you move around a worksheet using the arrow keys, the cursor moves from field to field. Handy, but when you want to move down the worksheet to check on a bit of data, you'll end up losing your place because the cursor moves down the page with you. Toggle the Scroll Lock key ON and then move around. Now the page moves around - but the active field never changes. Toggle the Scroll Lock key OFF to reset it back.

Word: Startup Switches

Passing command line switches via the shortcut or command prompt allow you to control how Word starts up. Some of them are great value in troubleshooting launch problems because you can effectively disable features that might be the culprit.

/a - Prevents add-ins and global templates from being loaded automatically.

/m - Disables AutoExec macros during startup.

/n - Starts Word without creating a new document.

/w - Starts a new instance of Word that runs in a separate process from existing sessions.

/c - Launches Word, then invokes NetMeeting.

/q - Starts Word without displaying the splash screen.

Just add these switches (one or more) to the end of your Word shortcut(s).

Word: Making Borders More Interesting

Open the "Format" menu and click on "Borders and Shading;" once inside that dialog box, click on the "Page Border" Tab. Now, click the "Custom" box and then click the drop-down arrow under the "Art" list box. When the list expands, you'll find a number of borders that can spruce up your document wonderfully. All that is left now is to click on a border, click OK, and voilá! The border will be applied to your document.