Technology Q&A

Technology Q&A

Sort Excel cells that contain text and numbers…Give Excel cells a descriptive name…Get Excel to produce the right number of blank worksheets…Convert e-mails into calendar appointments with priorities…Stop Word’s AutoFormat from second-guessing on numbering lists…Shortcuts.

Key to Instructions
To help readers follow the instructions in this article, we use two different typefaces.

Boldface type identifies the names of icons, agendas, URLs and application commands.

Sans serif type indicates instructions and commands that users should type and file names.

EXCEL
Q. I use a spreadsheet that includes a list of machine parts identified by both words and numbers—for example, Part 2312. Since the cells contain both text and numbers, how can I sort them in numerical order?

A. There are two ways. Either nest the text within quotes—as in “Part” 2356—or put a forward slash in front of the number—as in Part /2356. Either of those symbols—the quotes or the slash—tells Excel to ignore the text in its calculation and sort only by number.

Q. I’ve been trying to give descriptive names to spreadsheet cells—rather than use cell-location codes (A1, B2 and so forth)—and while sometimes it works, other times I get error messages. Can you help?

A. Perhaps you’re committing a naming error. Many people fall into the trap because of a programming idiosyncrasy. But before showing you the error, let me describe how—and why—names are used in Excel. Giving names to cells, or even groups of cells, makes Excel much easier to use. For example, it’s far easier to recognize a cell if you name it Travel Expenses rather than a code, A1, for example.

But—and this is where you probably made your mistake—if a cell’s name is made up of more than one word, there can be no spaces between the words. If the name does have more than one word, fill in the space with an underscore (_). You can also use periods (.), backslashes (\) and question marks (?). In addition, names must start with either a letter, a backslash or an underscore.

There are two ways to name a cell: After you highlight the target cell, go to Insert, Name, Define Name and enter the name you want under Names in workbook and click on Add. All the names you added will be displayed on the screen, and when you highlight one, its location will be disclosed under Refers to: (see screenshot at right).

A faster way to add a name is to highlight the target cell and then type your choice of name—Sales in this case—in the Name box in the upper left-hand corner of the worksheet. In the screenshot, the box contains the word Sales.

If you want a cell to show sales minus travel expenses, you would type in this formula: =sum(Sales-Travel_Expenses).

Q. When I create a new spreadsheet workbook, Excel opens three worksheets. All I want is one, and those extra worksheets bug me. How can I get Excel to stop it?

A. That’s one of Excel’s defaults. But worry not, you can easily change it. Go to Tools, Options and click on the General tab. Under Sheets in new workbook, change the default to 1. By the way, you can set the number as high as 255.

Notice also, while you’re in the Options menu, that you can change several other defaults: You can increase (or decrease) the number of files in the Recently used files list. (When you click on File, Excel displays the addresses of your most recently opened files for fast launching.) You also can change the default location (folder) of an initially saved file. In addition, you can adjust a worksheet’s default font style and its size.

Now what do you do when you have reduced the default to just one new worksheet and you suddenly find you need another worksheet or two? To add a new worksheet, choose Insert, Worksheet, and Excel will add the worksheet and name it (in this case) Sheet2. An even faster way to create a new worksheet is to right-click on any worksheet tab; that produces a drop-down menu and one of the items is Insert. Click on it and you have a new worksheet, and that, too, will be prenamed.

What do you do if the order of tabs now is out of sequence—Sheet2, Sheet1, Sheet3, for example? To sort it manually, just grab any tab, drag it to a new location and release the mouse button.

OUTLOOK
Q. I often get e-mails that demand a timely response or require that I schedule something in my calendar. But when the e-mails come in rapidly, I’m sometimes too pressed to respond immediately. Isn’t there a way to link my e-mails to my calendar so I can create instant reminders for myself?

A. There are two things you can do. The simplest is to add a follow-up flag to the new e-mail. In your Inbox, right-click on a message; this generates a menu that includes a Follow Up item identified with a little red flag. When you click on it, the following screen at right appears:

Add the date for follow-up in the Due by: space, and Outlook will alert you on that day. In the meantime, the red flag will stay on the e-mail message in your Inbox until you clear it (see screenshot below).

Another thing you can do is drag the message from your Inbox onto the Calendar icon on the Outlook Bar. That evokes this screen:

Enter the date and time you want to reply, and specify any additional options you want; it will save to your calendar as an appointment.

WORD
Q. Every time I make a list—for example 1, 2, 3 or a, b, c—Word second-guesses me and tries to complete the numbering sequence on its own. Some people may think that’s cool, but I find it annoying. Is there some way to stop it?

A. Not only can you halt the numbering mischief, but you can halt other instances of Word’s second-guessing. The culprit is the AutoFormat As You Type utility that’s included in AutoCorrect. While the idea behind AutoCorrect is great—and it even does some jobs pretty well—many users have become frustrated when it incorrectly anticipates what you want to do—imposing the numbering sequence, for instance, when you’re making a list.

Here’s how to turn it off and get rid of similar AutoCorrect problems. Click on Tools, AutoCorrect Options. Then click on the AutoFormat As You Type tab, and that will bring up the screen at right.

Now, under Replace as you type, uncheck the box marked Automatic numbered lists. If you dislike the automated bulleted lists, then uncheck that, too.

If you don’t want Word to automatically convert your typed URLs and e-mail addresses into hyperlinks, uncheck that box also.

Shortcuts

Excel: A fast way to delete a cell—not the contents of a cell but the cell itself: Highlight the cell and press Ctrl+- (minus sign). The cell will disappear, and you will be offered options on which adjacent cells will shift to fill the empty space.

Word: To create an em dash (—), which is a long dash, press Ctrl+Alt+- (the minus key on the numeric keypad). However, beginning with the 1995 edition, Word will do that automatically if, after typing the last letter of one word you type two dashes in a row and then type the first letter of the next word.

Excel: Two quick ways to select a range of cells: Click in the first cell and then hold down the Shift key while you use the arrow keys to select the range. Or click in the first cell, press Shift and click in the last cell of the range.

Excel: To translate Arabic to Roman numerals, use this formula: =ROMAN (Arabic number). So 1999 becomes MCMXCIX and 2003 becomes MMIII.

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to Senior Editor Stanley Zarowin via e-mail at zarowin@mindspring.com.

Because of the volume of mail, we regret that we cannot individually answer submitted questions. However, if a reader’s question has broad interest, we will answer it in a forthcoming Technology Q&A column.

On occasion you may find that you cannot implement a function I describe in this column. More often than not it’s because not all functions work in every operating system or application. I try to test everything in the 2000 and XP editions of Windows and Office. It’s virtually impossible to test them in all editions and it’s equally difficult to find out which editions are incompatible with a function. I apologize for the inconvenience.