Microsoft PowerPoint/Excel Tips

horizontal rule

INDEX OF MICROSOFT WORD/EXCEL TIPS

MS WORD TIPS

 

Controlling the default Zoom setting

Stop document print jobs

Apply frequently-used Word styles with shortcut keys

Preview files before you open them

Controlling how Word selects words

Built a table of contents for part of your document only using Bookmarks

Embedded Excel worksheets make calculations easy

Lock Word's fields to protect them from changing

 

 

MS EXCEL TIPS

 

Combine data from multiple cells

Reverse searching in a worksheet

Counting text entries in Excel

Indenting text in a cell without adding spaces

Hide Excel values in a cell

Format cells for proper data column sorting

Selectively format text within worksheet cells

Force a new line in a worksheet cell

MS WORD TIPS

Controlling the default Zoom setting
Word displays documents at the 100% magnification level as its default zoom. However, if you like to see more of your document by reducing the zoom factor, you can change the default by opening the Normal.dot template and set the zoom factor to your new default value. When you save the Normal.dot template, the new default settings will come into effect for all new documents. Remember that you do not have to accept Word's Zoom factors, e.g. 25%, 50%, 75%....etc. You can type into the dropdown any value you like. When you save any document, Word remembers the zoom settings for the next time you open the document

 

 Return to top
Stop document print jobs
Have you ever sent a document to the printer, only to realise that you need to make a correction to it and then print it again? If you're quick, you can use Word to cancel a print job that you've already sent to the printer. 

 

When Word's Background Printing feature is turned off, you can cancel an outgoing print job simply by pressing the Esc key or clicking Cancel when Word notifies you that it's sending your document to the printer. 

 

If Background Printing is turned on, you can instead double-click on the printer icon that appears in the status bar when Word sends your document to the printer. This method is often much quicker than using the Windows operating system to cancel print jobs, since you don't need to leave Word to stop the print job. However, do remember that the smaller your document is, the faster Word is able to send it to the printer. This means that you'll have to act quickly if you want to cancel a print job from Word. 

 

Background uses additional system memory to allow you to continue working in Word while a document is being printed. However, it usually takes a little bit longer for documents to print when Background Printing is turned on. To check whether Background Printing is active, choose Tools/Options from the menu bar and then click on the Print tab. Select or clear the Background Printing check box in the Printing Options panel, and then click OK.

 Return to top
Apply frequently-used Word styles with shortcut keys

If you frequently use custom character or paragraph styles in your documents, you might find scrolling through the Style dropdown menu, located on the Formatting toolbar, is a bit tedious to find and apply your chosen style. 

As an alternative, you can use the Style dialog box by selecting Format/Style from the menu bar. However, using the Style dialog box to apply a style does not really make the process any easier or quicker. 

The best way is to assign shortcut keys to the styles you use most frequently as follows. Open a document or template that contains the style you want to assign to a shortcut key. Next choose Format/Style from the menu bar to open the Style dialog box. Select your chosen style from the list box and click Modify. In the Modify Style dialog box, click Shortcut Key. Make sure the insertion point is in the Press New Shortcut Key text box, and press the shortcut key-combination you want to use. If the key-combination is already assigned to another command, Word will tell you beneath the Press New Shortcut Key text box. If the style has already been assigned a shortcut key, Word lists it in the Current Keys list box. 

To save the shortcut key-combination you pressed, select the document or template in which you would like to save the key-combination from the Save Changes In dropdown list and click Assign. Close the Customize Keyboard, Modify Style and Style dialog boxes. To apply the style using your new shortcut key, select the text you'd like to modify and then press the shortcut key you specified

 Return to top
Preview files before you open them
Word allows you to preview a document's contents without opening it. To access the Open dialog box, choose the File/Open menu bar options, click the Open button on the Standard toolbar, or press [Ctrl]-O. 

 

In the Open dialog box, select the file you want to preview. Next, click the dropdown arrow next to the Open dialog box's Views button, and then choose Preview. A preview pane appears along the Open dialog box's right side and displays the selected document's contents. Larger documents may take a few seconds to load the preview. 

 

You can preview a different document just by clicking on it. The preview window can display the contents of any file Word is able to open. Once you've activated the Preview view, it remains the Open dialog box's default view until you change it.

.

 Return to top
Controlling how Word selects words
Selecting text is not very difficult but sometimes it can be a bit tricky because the mouse pointer is not a very precise pointing device.  It can awkward using the mouse to "paint" over the desired text starting and ending the selection at the right place. As often or not, you end up starting a selection on the second or third character of a word when you meant to start at the beginning. Or you might over- or undershoot the end of a word when you wanted to extend a selection exactly to its end.

 

Fortunately, Word's Automatic Word Selection option makes selecting text easier.  When this option is on, you can start selecting text anywhere in a word, and as soon as you extend past the end of that word, Word selects the entire word. In addition, each time you extend the selection into the next word, Word selects that entire word too.

 

You may prefer to turn off Automatic Word Selection if you do not want to select complete words. To do this, choose Tools/Options from the menu bar and click on the Edit tab. Next, deselect the When Selecting, Automatically Select Entire Word check box and click OK. 

 

You can still quickly select an entire word when the option is not active. Simply highlight the word you want to begin the selection by double-clicking on it. Continue to hold down the mouse button after you make the second click. Then, drag the mouse to extend the selection a whole word at a time.

 

 Return to top
Built a table of contents for part of your document only using Bookmarks

Word makes it easy to create a table of contents, i.e. the TOC, built automatically from your document's styles. Usually, you are likely to want to crate a TOC based on the entire contents of your document. However, sometimes you may wish to limit a TOC to reference just a portion of the document body. You do not need to thin down a full-document to create the reduced TOC. Instead, you can use a bookmark to indicate which portion of your document you want to reference. 

 

Start by selecting the portion of your document you want to reference in a TOC, then choose Insert/Bookmark. Enter a one-word name for the bookmarked selection in the Bookmark Name text box, and then click Add. 

 

Next, place the insertion point where you want the table of contents to appear, and then choose Insert/ Index And Tables (Insert/Reference/Index And Tables in Word 2002). Click on the Table Of Contents tab, and use the available options to configure your table of contents. Click OK when you have finished. Word adds a new table of contents that references your entire document. 

 

To reference just the bookmarked portion, select the table of contents, and then press [Shift][F9] to reveal the TOC's field code. It will look something like this: 

 

{ TOC \o "1-3" \n \h \z \u }. 

 

Position the insertion point on the left side of the field code's closing brace (}), and then type "\b BookmarkName" (without quotes) where BookmarkName is the name of the bookmark you created earlier. 

 

Your field code should now look something like this: 

 

{ TOC \o "1-3" \n \h \z \u \b MyTOC }. 

 

When you've finished, press [F9] to update the field. If Word asks what you'd like to update, choose the Update Entire Table option, and then click OK. Your table of contents should now reference only the information you bookmarked.

 Return to top
Embedded Excel worksheets make calculations easy

Word tables can perform rudimentary calculations using the Table/Formula command. However, when you need to perform complex calculations, an embedded Excel worksheet is a more powerful and efficient alternative. First, position the insertion point where the new worksheet should appear. Next, click the Insert Microsoft Excel Worksheet button on the Standard toolbar, and then select the number of cells you want to display. If you get the number of cells wrong you can always correct the size of the displayed sheet later. Word embeds an Excel worksheet into your document and activates it for editing. Now, add your data and calculations formulae into your embedded worksheet just as you would a normal Excel worksheet. When you have finished, click anywhere in your document to resume document editing. 

When you need to edit your embedded worksheet, you simply double-click on the embedded worksheet it to activate it. To increase the number of visible worksheet cells, double-click on the embedded worksheet to activate it for editing, and then drag any of its sizing handles to increase its display area. To increase the size of the embedded worksheet without increasing the number of visible cells, click on the worksheet once to select it. Then drag any of its sizing handles.

 Return to top
Lock Word's fields to protect them from changing

Using fields is a convenient way to automatically insert and update information in your documents. For example, if you want to include your mailing address in your document, you can insert a UserAddress field rather than typing the address by hand. This way, if you change the mailing address on the Options dialog box's User Information tab, the UserAddress field updates accordingly. As convenient as the updating capabilities built into Word fields may be, you'll occasionally want to prevent a field's results from updating later on. To do so, lock the field by clicking on it and pressing [Ctrl][F11]. To unlock a locked field, click on it and press [Ctrl][Shift][F11].

 Return to top

MS EXCEL TIPS

Combine data from multiple cells

Excel provides two easy ways to combine information stored in separate cells into a single cell; these are the CONCATENATE function or the ampersand (&) concatenation operator. 

As an example of how CONCATENATE works, imagine a worksheet with first names stored in column A and last names stored in column B. When you want to combine the names with the first name in column A and last name in column Bin cell C1 enter the following formula: 

=CONCATENATE(A1," ",B1) 

You will see that we have separated the first name and surname with a literal text string. You can also concatenate numeric data. The CONCATENATE function can accept up to 30 arguments. 

To produce the same result using the concatenation operator, enter the following formula in cell D1: 

=A1&" "&B1 

Note that this approach can also concatenate numeric data, but it isn't limited to 30 items.

 Return to top
Reverse searching in a worksheet

When you use the Find or Replace feature in Excel, it's all too easy to click the Find Next button one too many times. Fortunately, you do not have to cycle through all of the matches in the worksheet to get back to ones you missed the first time. Just hold down the [Shift] key when you click the Find Next button and Excel will search in the opposite direction through the worksheet as it hunts for matches to your search string.

 Return to top
Counting text entries in Excel
If you use the =COUNT function in Excel to count cells ever tried to perform a simple count, such as the number of names in a list, you may find that you did not get the result you expected. For example, say you have a series of names in cells D22:D28. In cell D29, you enter the formula: 

 

=COUNTD22B2:D28) 

 

Instead of the expected answer of 7, the formula returns 0. The reason is that Excel's COUNT function actually only counts numeric values. 

 

To count any entry, regardless of type, you must use the =COUNTA function. For example, the formula: 

 

=COUNTA(D22:D28) returns the number of name entries. 

 

The =COUNT function counts any non-blank cells in the specified range. This means that a cell will be counted even if just displays an error message.

 Return to top
Indenting text in a cell without adding spaces

To indent text entries in worksheet cells, such as for items beneath a column heading, you may currently use the spacebar to insert spaces at the beginning of the data you want indented. 

 

A much easier way to achieve the same result, without compromising your data, is to chose the Format/Cells from the menu options bar. Click on the Alignment tab and select Left (Indent) from the Horizontal dropdown list and use the Indent spinner box to set the number of spaces you want to appear between the left edge of the selected cells and the start of your text data. 

 

You can indent up to 15 spaces. When you've finished, click the OK button. 

 

Remember that there are two toolbar buttons on the Formatting toolbar, i.e. Decrease Indent and Increase Indent, that let you accomplish the same results.

 Return to top
Hide Excel values in a cell

You can hide a value in a single cell by assigning a null number format to the cell.  To do so, select the appropriate cells. Then, choose Format/Cells from the menu bar. Select Custom from the Category list box. Then, in the Type text box, enter three semicolons (i.e. ;;;). Finally, click OK.

 Return to top
Format cells for proper data column sorting
Most people format column headers such that they are visually distinct from your data. However, you may not know that there is an added value. Excel looks at the formatting you set in the first row of a column to determine whether the first row in a table range contains column headings or data when you execute the Sort command. Excel assumes that, if the formatting is different, the first row is a header row and excludes the first row from the sort. If the formatting is the same as all of the other rows, the data in the first row is sorted along with the rest of the data in the table columns.
 Return to top
Selectively format text within worksheet cells
Everybody knows how to format Excel data by applying bold, underline or italic and most people know how to apply colour formatting to worksheet cells. However, you may know that you can also apply formatting to just certain text within a cell. For example, you may want to emphasize a particular word or phrase within a long string of text. To do so, select the cell and then select the text you want to format within the Formula bar. Then, simply apply the types of formatting changes you'd typically make and press [Enter] when you've finished.
 Return to top
Force a new line in a worksheet cell
Excel can store large amounts of text in a cell but it quickly becomes unreadable when it is in a long string. You can break textual information in a cell into multiple lines by inserting a manual line-break. You do this by pressing [Alt][Enter] and your insertion point moves down to a new line. This works both when you enter text in the formula bar or directly in a cell.
 Return to top

Sandy Pratt is a director of 4-consulting, click here to view his profile.

 

Return to top

horizontal rule

4-consulting 15 Palmerston Road, Edinburgh, EH9 1TL 
Tel 0131 668 2112

Copyright © 2008 4-consulting