E-zine - Microsoft Word & Excel Tips

Tables are great tools for organising and presenting numerical data information. However, you can miss the key messages in the “fog” of all the numbers. To make numerical data easier to understand quickly, you can transform your table into a chart.

Place the insertion point anywhere within the table and choose Table | Select | Table from the menu bar. Next, choose Insert | Picture | Chart from the menu bar. The Microsoft Graph Chart mini-application will launch and create a new chart and datasheet based on the table data you selected earlier. You can now customise the chart as desired. When you've finished, click outside the chart object to return to your document.

Return to top

Apply automatic, in-text numbering with ListNum fields

You can apply automatic numbering to paragraphs easily using the Bullets And Numbering feature on the Format menu or the Numbering button on the Formatting toolbar.

Usually, this automatic, paragraph-based numbering meets your needs but when you need to apply automatic numbering to items within a paragraph, rather than to the paragraph itself, Word's automatic numbering features may not seem so accommodating at first. This method of in-text numbering is often used in legal documents. Although you could simply type numbers where you would like them to appear, this solution does not automatically renumber your text if you make any additions or deletions.

However, you can use a ListNum field to apply in-text numbering that automatically adjusts to your additions and deletions. Your in-text numbering will remain consistent and consecutive.
To insert a ListNum field, place the insertion point where you would like a number to appear and then press [Ctrl][Alt]L.

To promote or demote the numbering level applied to a ListNum field, right-click on it ([control]-click in Word 2001) and choose Increase Indent or Decrease Indent from the resulting shortcut menu. Alternatively, select the field and then click the Increase Indent or Decrease Indent button on the Formatting toolbar.

In addition to unnumbered paragraphs, you can use ListNum fields to incorporate in-text numbering within paragraphs that already use paragraph-level bullets or numbering.

Return to top

Draw attention to text with a negative indent

There are many techniques that you can use to draw attention to titles, headings and similar text items. For instance, you can apply heading styles to them, centre them on the page, increase their font sizes, apply bold or italic formatting or indent the text that appears beneath them.

However, a less known but equally effective technique is to apply a negative indent, also known as an outdent, to important text so that it overlaps the left page margin. This gives other text the appearance of being indented, although in reality it is aligned with the left page margin.

To apply a negative indent, place the insertion point in the paragraph you'd like to outdent, and then choose Format | Paragraph from the menu bar to open the Paragraph dialog box. Next, click on the Indents And Spacing tab if it is not already active. In the Indentation panel, use the Left option's bottom scroll button to change the setting to a negative number, such as -0.5 inches.

The Preview window displays the effect that the new setting will have on your text. When finished, click OK. You can also achieve this effect simply by dragging the Left Indent marker, located on the ruler, to the left of the Left Margin marker.

To make the text print correctly, outdented text must be positioned within the document's printable area, which is determined by your printer.

Return to top

Add numbering to table rows and columns

When you're working with large tables it can be difficult to verbally refer someone to a specific table cell. This is particularly true where tables are set up like a list and do not use specific row headings. Without row headings, a table's only points of reference are its column headings and its sort order. However, you can easily add a point of reference to your table rows by numbering them.

To add numbering within your table's leftmost column, select it, or those cells that you wish to number, and then click the Numbering button on the Formatting toolbar.

Alternatively, you can position row numbers in their own separate column. To do so, select your table's leftmost column. When you do, the Insert Table button on the Standard toolbar changes to the Insert Columns button. Click the Insert Columns button to insert a new column on the left side of your table. Next, with the new column selected, click the Numbering button to apply row numbers.

After you have applied row numbers, you can fine-tune their formatting and positioning just as you would any other numbered item. Keep in mind that you can also use this technique to number table columns and other cell ranges.

Return to top

Draw AutoShapes from their centres instead of from their corners

You can draw various shapes in your documents by selecting the AutoShape you wish to draw using the Drawing toolbar and then by clicking and dragging in your document to draw it.

When you click and drag to draw an AutoShape, Word anchors one corner of the shape in the location you clicked on and then it draws the rest of the shape in the direction you drag. This makes it difficult to estimate where your AutoShape's centre will end up when you have finished drawing it.

As an alternative, you can draw your AutoShapes from the centre instead of from the corner. To do so, select the AutoShape you want to draw using the Drawing toolbar. When you do, the insertion point changes to crosshairs. Next, position the top of the crosshairs where you want the centre of your AutoShape to end up. Then hold down the [Ctrl] key as you click and drag to draw the AutoShape.

Return to top

Another way to paste in Word

If you frequently find yourself copying, cutting and pasting items in your Word documents, you may find that using Word's shortcut keys is faster than using its Cut, Copy and Paste buttons located in the Standard toolbar.

Word's default shortcut key for the Paste command is [Ctrl]V. However, if you prefer using shortcut keys, you can further streamline the pasting process by configuring Word to use the [Insert] key to paste objects. To do so, select Tools | Options from the menu bar and then click on the Edit tab. Select the Use INS Key For Paste check box and then click OK.

The next time you want to paste an item from the Clipboard in your Word document, simply press [Insert]. Remember that this feature pastes only the item most recently added to the Clipboard, even when using Word Clipboard toolbar.

Return to top

Add space between the header or footer and the document text

If your document uses a multi-line header, or if the header uses a particularly large font, the space left between the bottom of the header and the beginning of the document text can begin to look pretty cramped. The same thing can happen when you use a multi-line or oversized footer.

To add space between the header or footer and the document text, many people insert blank lines in the header/footer or in the document itself to create the much-needed “space”. However, this approach lacks precision and polish. Instead, add the extra space by increasing the top and bottom page margins. (This is the way that Microsoft intended it to be done)

Although you can change your document's page margins by selecting File/Page Setup to access the Page Setup dialog box, you might find it easier to use the vertical ruler in Print Layout view. First, select View/Print Layout to switch views. Your document's header should be greyed out, but viewable, at the top of the page. Look at the vertical ruler that appears along the left edge of the document window. (If you cannot see the vertical ruler, select View/Ruler to display it.) The grey portion of the ruler represents the margin, including the header area, and the white portion represents the text area.

To adjust the space between the header and the document text, hover the mouse pointer over the ruler where the grey area meets the white area. When the mouse pointer changes to a two-headed arrow, click and drag to increase or decrease the page's top margin as desired.

If you wish to add space between the footer and the document text, scroll down to the bottom of the page, then use the same technique to change the page's bottom margin.

(Note: You can also adjust the page's top and bottom margins using the vertical ruler in Header And Footer view. You can also view precise measurements by holding down the [Alt] key as you drag a margin setting on the vertical ruler.)

Return to top

Apply the Symbol font on the fly

The Symbol font contains a collection of Greek alphabet characters and many common symbol characters, such as arrows, trademark symbols, and mathematical operators.

If you frequently use the Symbol font to add these types of characters in your documents, you probably know many of the Symbol font's character mappings by heart. For example, to insert the Greek omega character in your document, just type the letter W, then select it and apply the Symbol font. Similarly, you can insert the square root symbol by activating the Symbol font, and then pressing [Alt]0214 using the numeric keypad (with NumLock turned on).

The mainstream technique for inserting Symbol font characters is to choose Insert/Symbol from the menu bar and then use the Symbol dialog box. You can use the Symbol dialog box to insert extended characters from many other font sets as well. However, if you know certain character mappings by heart and you just want to get the job done quickly, it is much easier to apply the Symbol font on the fly. Instead of using the Font dropdown list on the Formatting toolbar, you can apply the Symbol font easily using its built-in shortcut key. To do so, simply press [Ctrl][Shift]Q.

Return to top

Add numbering to table rows and columns

When you working with large tables, especially tables that are set up like a list and do not use specific row headings, it can be difficult to verbally refer someone to a specific table cell.

Without row headings, a table's only points of reference are its column headings and its sort order. However, you can easily add a point of reference to your table rows by numbering them.

To add numbering within your table's leftmost column, select it , or select only those cells that you want to number, and then click the Numbering button on the Formatting toolbar

.
As an alternative, you can position row numbers in their own separate column. To do so, select your table's leftmost column. When you do, the Insert Table button on the Standard toolbar changes to the Insert Columns button. Click the Insert Columns button to insert a new column on the left side of your table. Next, with the new column selected, click the Numbering button to apply row numbers. After you have applied row numbers, you can fine-tune their formatting and positioning just as you would any other numbered item.

Remember that you can also use this technique to number table columns and other cell ranges.

Return to top

MS EXCEL TIPS

Indent text data without adding spaces in Excel

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.

However, there is a much easier way to achieve the same result without compromising your data. Start by selecting the cells that contain the data you want to indent and choose Format | Cells from the menu bar. Click on the Alignment tab and select Left (Indent) from the Horizontal dropdown list. Next, 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 set an indent of up to 15 spaces. When you've finished, click the OK button.

Note that two toolbar buttons on the Formatting toolbar, Decrease Indent and Increase Indent, let you accomplish the same results.

Return to top

Colour-coding Excel 2002 worksheet tabs

Excel 2002 lets you apply colour to worksheet tabs to categorize and manage your data better. To format the tab for the active sheet, choose Format | Sheet | Tab Colour from the menu bar, or right-click on the tab and choose Tab Colour from the shortcut menu. Then, select the colour you want to use from the Tab Colour palette (or select No Colour to clear any formatting) and click OK.

Return to top

Start counting pages from a specific number

You can include page numbers in a worksheet's header or footer. However, sometimes you may need to start numbering pages from a number other than the default value of 1. For example, you may need to print a worksheet that is part of a larger report and the sheet should begin numbering as page 8.

To do this, start by choosing View | Header And Footer from the menu bar. Click the Custom Header or Custom Footer button. Then, click the Page Number button, which is to the right of the Font ("A") button. Excel will insert the following:

&[Page]

Next, after the page number code, add one number lower than the page number you want to start with, so that the code looks like:

&[Page]+7

Finally, click OK.

If you use Page Setup Preview, you will se that the first page of that sheet becomes page 8 automatically and the rest of the pages will follow sequentially.

Return to top

Cross-checking row and column totals in Excel (97/2000/2001/2002)

When you create sums based on a table of data, you should sum both the rows and columns of the table. The cell at the intersection point of the total row and column traditionally contains a grand total of all the calculated sums. However, creating the grand total using just the column or row sums will not uncover errors that may be caused by an incorrect subtotal. To be sure that the grand total is correct, you need to be verify that the sum of the row subtotals equals the sum of the column subtotals.

To ensure that the totals match, you can use a custom formula to calculate the grand total instead of just a traditional SUM formula. One way to flag totalling errors is to use a formula in the form of:

=IF(SUM(range1)=SUM(range2),SUM(range1),"message")

where range1 and range2 are the addresses of the two total ranges you want to check and message is the string you want Excel to display if the two sums don't match.

If the sum of the values in the first range is equal to the sum of the values in the second range, the conditional test will be TRUE. Therefore, Excel will return the sum of the values in the first range, as specified with the function SUM(range1). If the sums of the values in the two ranges are not equal, the conditional test will be FALSE. As a result, Excel will return the string specified by the third argument of the IF function.

If you frequently need to perform this type of cross-check, you can simplify the process with a user-defined function.

Return to top

Spell check all sheets in a workbook at once

If a workbook contains several sheets, most people will spell-check each sheet individually. However, If you would like to spell-check an entire workbook, first right-click on any sheet tab, and choose Select All Sheets from the resulting shortcut menu.

To spell check specific sheets, hold down the [Ctrl] key and click on the appropriate sheet tabs, or hold down the [Shift] key to select a block of adjacent worksheets. After you have made your selection, press [F7] to launch the spell checker.

Return to top

Easily transpose a range of data (Excel 97/2000/2001/2002)

Occasionally, you may have data stored in a worksheet column that you would prefer to have organized in a row or vice versa.

To do so, select the range that you want to transpose. Select Edit/Copy from the menu bar and then select the new destination for your data. Choose Edit/Paste Special from the menu bar. When the Paste Special dialog box appears, select the Transpose check box and click OK. If the range to be transposed contains multiple columns and rows, the top row of the data from the source range is placed in the left column of the new range and the left column of the original range becomes the new range's top row.

Return to top

Display the day of the week of a date in an Excel cell

When you enter a column of dates in a worksheet, it can be useful to know the day of the week on which each date falls. This is easy to achieve by applying a custom date format to the cells containing the dates.

Excel displays the full name of the day of the week on which the date values falls e.g. Monday, Tuesday, Wednesday, and so on.

To apply this format, select and right-click a range containing dates and choose Format Cells from the shortcut menu. If necessary, click on the Number tab. In the Category list box, select Custom. In the Type text box, enter DDDD and click OK. The selected cells now show the day of the week instead of the date. If you select a cell and check the contents of the Formula bar, the date is still stored in the cell.

You can use the related custom format DDD to display truncated day names, e.g. Mon, Tue, Wed, and so on.

Return to top

Instantly spruce up your worksheets with AutoFormat

The way in which your data is formatted can do a lot to improve the information's readability and provides a way to draw attention to specific elements. The AutoFormat feature lets you apply predefined collections of complementary formatting options to a range.

The attributes stored in an AutoFormat are:

- Number format
- Font
- Alignment
- Border
- Patterns
- Width/Height

To apply an AutoFormat, simply select the data you want to format and then choose Format/ AutoFormat from the menu bar. Excel displays a dialog box that shows samples of each AutoFormat. Choose any one of the samples and click OK. If you do not like the effect that the AutoFormat produces when applied to your data, choose Edit/Undo AutoFormat from the menu bar to restore the original formatting.

Return to top

Shortcuts for navigating within a selected range

By default, when you press the [Enter] key, the cell selector moves to the cell below the active cell. Pressing [Tab] moves one cell to the right. Likewise, pressing [Shift][Enter] or [Shift][Tab] move the cell selector up and to the left, respectively.

Although you may be aware of this behaviour, you may not know that these shortcut keys are also applicable to selected ranges and there are several other shortcuts that make it easy to move the cell selector within a particular range.

For example, if you have a cell range selected, the [Enter] and [Tab] keys (as well as [Shift][Enter] and [Shift][Tab]) still move the cell selector, but movement is restricted to the confines of the selected range.

When the cell selector is at the edge of the range, pressing a shortcut key moves the cell selector back to the beginning of the range. In addition, you can use the [Ctrl][.] shortcut to move among the four corners of the currently selected range. If non-contiguous ranges are selected, the keystroke combinations [Ctrl[Alt][Right Arrow] and [Ctrl][Alt][Left Arrow] let you move the cell selector to the selected range that's to the left or right of the current range.

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

Return to top