Notes from the Help Desk…

Most of you probably know about the Extend box in Excel, even if you don’t know its name. The Extend box is the little square at the bottom right corner of the current cell or range. When you put your cursor on it, the cursor changes to a small black cross and the fun begins.

Extending a Cell’s Contents

The simplest way to use the Extend box is to drag it, either down or across, over a range of cells. When you let go, the range will fill with the contents of the original cell or range, based on these rules:

• Text will be copied
• A single number will be repeated.
• A range of numbers will create a series based on the differences between the numbers
• A formula will be copied and all cell references will change appropriately.
• A day, month, or quarter name will be extended into a list: Monday will be followed by Tuesday, Wednesday, etc.
• A custom list will be expanded. (You can create custom lists by highlighting the range, then selecting Tools | Options | Custom Lists and adding the range.)

Smart-Tag Options

With the advent of Smart Tags in Excel 2002, when you finish extending, a Smart Tag will appear. Clicking the Smart Tag will display a list of choices that will vary based on the type of data you are extending:

• Text or formula will let you extend with or without formatting, or just the formatting.
• Numbers will let you create a series (1, 2, 3, etc.) and extend with or without formatting.
• Dates will let you fill days, weekdays, months, years, with or without formatting.

Right-Dragging the Extend Box

If you extend by right-dragging, you’ll get a Quick Menu with the Smart Tag options as well as a link to the Fill Series dialog box, where you can create quite complex series, if you need them.

Double-Clicking Magic

Often, when you are working with a list, you often need to add a formula, as shown in the top picture above. If you double-click the formula (in C2 and D2 above), Excel will extend this formula to the length of the list, as shown in the picture on the bottom. (In our example, we used a range of two cells. We could have done C2 and D2 individually.) This is especially helpful if you list is a long one. Be aware that it will stop at the first blank cell in the column to the left.

Finding special characters is a useful way to clean up a document. The special characters can be added from the Special button list or directly as codes.

See the Find and Replace Tips – Formatting for the general process. The only difference is that you enter the codes instead of text.

For example, suppose you wanted to turn part of a letter from a block style with a blank line between paragraphs to an indented paragraph with a regular space between paragraphs. The process is to find the paragraph separators (2 paragraph marks) and replace them with a paragraph mark and a tab. The paragraph mark ends the paragraph and the tab begins the next one.

1. Select the portion of the letter to change.
2. Click into the Find field. Enter two Paragraph mark codes. Either select Paragraph mark from the Special button menu two times, or type ^p^p (lowercase p).
3. In the Replace field, enter a single Paragraph mark code and a tab code (^t).
4. Replace All will affect just the selected text.

Another common problem is getting mailing lists back into a format that is mail mergable. For example, suppose you’ve gotten a list formatted as follows:

John Doe
123 East Street
Anytown, MA 00001

Sally Smith
345 West Street
Yourtown, CT 00002

Each of these lines ends with a paragraph mark (^p code) and each record is separated by an additional paragraph mark. The solution is to use intermediate replacement text to get rid of the paragraph separators, then turn each line of the address into a tab separated value:

1. Replace ^p^p with a pattern that is not likely to be in the text. I like @#@ since it is easy to type and not at all common.
2. Replace ^p with a tab character, ^t.
3. Replace @#@ with a paragraph mark.

This will convert your list into a tab-delimited file, easily used by Word or imported into Excel, Access, etc. If you plan to export it, make sure to save the file as a text file to remove extraneous Word codes.

If you have a mix of 3 and 4 line addresses, as is more common, this is a little trickier. I end up importing it into Excel and using a set of text functions to parse the text as well as to break the City, State, and Zipcode lines into separate fields. But that is another tip.

In the 80’s, search and replace (as it was known then) seemed the second coolest thing about word processing. The first, of course, was never having to re-type a document. These days, with the use of templates, styles, and Word’s New From Existing Document, it seems less necessary.

However, for cleaning up documents and combining files with inconsistent formatting, nothing beats Find and Replace. Except, perhaps, for Find and Replace in Word coupled with some of Excel’s features.

First off, you can easily Find and Replace formatting:

1. Select Find or Replace from the Edit menu (or CTRL+h).
2. Click the More button to display your options.
3. In the Find field, type any text you want to search for. Leave it blank if you just want to search for formatting.
   Make sure your cursor is still in the Find field and click the Format button. Select the formatting from this menu as you would any formatting from the Format menu. You can search for fonts, attributes, font size, indentation, styles, and more.
4. Click into the Replace field. If you have text in the Find field, make sure to add it to the Replace field or Word will delete all occurrences of the text.
5. Select the new formatting from the menu on the Format button.

It is a good idea to use Find Next and Replace for the first couple of changes to make sure it is behaving properly.

TIPS:

• Remove formatting by clicking into the Find or Replace field, then clicking No Formatting. You’ll have to do this separately for Find and Replace. If you forget, Word will combine your next search for text with the formatting you have selected.
• You may have to make several passes. If so, be strategic and leave formatting you can find for the second go-round. If you want to go from Bold and Underlined to Italic, make sure to Find both Bold and Underlined and replace it with Italic and No Underline. This will replace your Bold and Underlined text with Italic and Bold. Then search for Bold and replace it with Not Bold. Tricky. The problem is you can’t search for Bold and Italic at the same time.
• If your Search and Replace becomes Search and Destroy, an immediate Undo (CTRL-Z or Edit | Undo) will undo your last replacement. You did save your document before your Find and Replace, didn’t you?
• If the Find fails, make sure that you don’t have formatting you don’t want included in the Find. Also, check the Direction of the Find. If it is Down or Up, Word will start at the insertion point. Change it to All to guarantee you’ll find every instance.

When you have to create a PowerPoint presentation on one computer and show it on another, you face a number of potential problems. Are the fonts you used installed? Did you copy all the graphics and charts you needed? Does the new computer have PowerPoint and is it the right version? Microsoft has addressed these issues, but in PowerPoint 2003, they have solved it. If you use PowerPoint extensively, you’ll want to upgrade to 2003.

Package for CD has replaced the Pack and Go Wizard in earlier versions of PowerPoint. The Package for CD option includes the PowerPoint Viewer by default. You can also use the Package for CD option to package files to save into a folder on your hard drive. This is useful if you want to backup your presentation or transfer it to a USB thumb drive or zip disk.

To package a presentation for use on another computer, you’ll need a CD-R or CD-RW drive, but the process is pretty straightforward:
1. Open the presentation you want to save.
2. Select File | Package for CD…
3. In the Name the CD field type a name for the CD.
4. Files linked to your presentation and the PowerPoint Viewer are included to the package by default. To change the default options, click the Options… button.

The Options dialog allows you to:
• Add the PowerPoint viewer, so that anyone can view the presentation whether they have PowerPoint 2003 or not.
• Include any linked files so that these files are available.
• Add all TrueType fonts so that the person replaying the presentation does not have to have all your custom fonts installed.
• Set a password to open and to modify each presentation to keep the information secure.
5. Click the Copy to CD button.
6. If you are copying the presentation to a Folder or a zip disk, click the Copy to Folder... button, navigate to the folder you want to save the presentation in, and click OK.
7. After PowerPoint transfers the files, Close button.

Moving to different cells using the mouse is all well and good, but what happens when you have a list with 5,000 odd rows? Moving to the end of the list or, worse, highlighting to the end of the list is cumbersome with the mouse. (See the end of this tip for an exception.)

The solution is to use the END key. The END key is used differently in Excel from other programs. The behavior of the End key is taken from Lotus 1-2-3 so if you know that program, you can expect Excel to behave the same way.

To use the END key to jump:
1. Tap the END key.
2. Tap any arrow key. (Make sure NUM LOCK is not on if you use the keypad.)
3. The current cell will jump in that direction. Excel will stop when:
• It reaches a blank cell.
• It reaches the edge of the spreadsheet.
• It starts in a blank cell and it reaches a cell containing text.

To get to the end of a range containing blank cells, you may have to repeat the END key operation until you reach the last cell you want.

Keep in mind that using the END key is a sequential operation: you tap the END, then tap the arrow. You do not hold the END down and tap the arrow key simultaneously.

To use the END key to highlight:
1. Hold down the Shift key.
2. Tap the END key.
3. Tap any arrow key, keeping the SHIFT key held down.
4. The current cell will jump in that direction, highlighting as it moves.

Now that you’ve learned all of that, if you have Excel 2003, you can use the mouse to jump like the END key:

1. Put your mouse on the edge of the cell in the direction you want to jump. (If you want to jump down, place your cursor on the bottom edge of the cell.)
2. A four-way arrow will appear.
3. Double-click. The current cell will jump the same as it does with the END key.

If you hold down the SHIFT key when you double-click, Excel will highlight as it jumps.

Excel provides a Special number format for zipcodes which allows those of us in New England to display our zipcodes properly. However, when you do a mail merge in Word 2002 and 2003, this formatting will not come through. A zipcode of 01040 will display as 1040. You can fix this in Excel by converting the number back to text, but if your list is extensive, you won’t want to do this. This tip will show you how to display 5-digit zipcodes properly in a Word mail merge without having to reformat your data.

After you create the merge document and attach your Excel workbook containing the addresses:

1. Insert the merge field for your zip code where you want it.

2. Press ALT+F9 to display the field codes. The Zip code field will look something like this (the text in quotes will be the name of your zip code field). Ignore any shading that might appear.

{ MERGEFIELD “Zip_code” }

3. Change the field to include a number format, as shown below. Make sure to place it inside the curly braces and include the spaces, etc. Make sure to use a backslash \ and not a forward slash /. Note that the number format is enclosed in quotes (”):

{ MERGEFIELD “Zip_code” \# “00000″ }

4. Press ALT+F9 again and preview the merge.

5. You may have to update the field by selecting it and pressing F9 in order to see the results of your change.