Worksheets are not all numbers, of course, so Excel includes features to help make typing and editing text easier. AutoCorrect helps you fix common typing errors even before they become spelling problems. For the rest of the words in your worksheets, the spelling checker helps make sure you’ve entered your text according to Webster’s. You might even be able to get AutoComplete to do some of the typing for you. And finally, the Research, Thesaurus, and Translate features lend some real clout in your quest for perfect prose.
Perhaps you have to stop and think “i before e except after c” every time you type receive. Perhaps you’re a blazing typist who constantly hits the second letter of a capitalized word before the Shift key snaps back. The Excel AutoCorrect feature helps fix many common typing and spelling errors on the fly. Click the File tab, Options, and in the Proofing category, click the AutoCorrect Options button to display the AutoCorrect dialog box, shown in Figure 8-38.
Note
AutoCorrect works when you are entering text in cells, formulas, text boxes, on worksheet controls, and in chart labels. AutoCorrect does not work when you’re entering text in dialog boxes.
The AutoCorrect tab in the AutoCorrect dialog box contains the following options:
Show AutoCorrect Options Buttons Controls the display of a menu when Excel detects an error, listing actions you can perform on the affected cell.
Correct TWo INitial CApitals If a word contains both uppercase and lowercase characters, checks that only one capital letter appears at the beginning of the word. If not, Excel changes subsequent uppercase characters to lowercase. If a word is all caps, Excel leaves it alone (assuming that this was intentional). AutoCorrect does not attempt to modify “mid-cap” words like AutoCorrect, since they have become commonplace.
Capitalize First Letter Of Sentences Makes sure you use “sentence case” (even if your “sentences” aren’t grammatically correct), based on the position of periods.
Capitalize Names Of Days Recognizes days and applies initial caps. This does not work on abbreviations like Sat.
Correct Accidental Use Of cAPS LOCK Key Scans for this kind of random Shift key mistake.
Replace Text As You Type Controls the application of the replacement list at the bottom of the dialog box, which lists a number of common replacement items. You can add your own grammatical faux pas to this list using the Replace and With text boxes and the Add button.
In addition to correcting common typing errors such as replacing adn with and, AutoCorrect also provides a few useful shortcuts in its replacement list. For example, instead of searching for the right font and symbol to add a copyright mark, you can type (c), and AutoCorrect replaces it with ©.
All these AutoCorrect options use specific rules of order. They use similar logical methods to determine your real meaning. But don’t assume that AutoCorrect (or the spelling checker) knows what you mean. Always proofread important work.
Note
If you have other Microsoft Office programs installed, anything you add to the AutoCorrect list in Excel also appears in other Office programs’ AutoCorrect lists.
We like to refer to this feature as AutoAutoFormat. The AutoFormat As You Type tab in the AutoCorrect dialog box (shown in Figure 8-33 on page 239) offers the Internet And Network Paths With Hyperlinks option under Replace As You Type. This converts a string recognized as a valid uniform resource locator (URL) or network path into an active hyperlink. As you finish typing it, you can click it immediately to go there.
Note
For more about formatting as you type, see Extending Existing Formatting on page 238.
The Actions tab in the AutoCorrect dialog box shown in Figure 8-39 is the repository for customized actions that appear on floating option button menus when appropriate. Excel comes with several of them, and you can download additional actions as they become available on the Microsoft Office Online Web site (office.microsoft.com/).
Figure 8-39. The Actions tab in the AutoCorrect dialog box controls the display of special floating option button menus.
The following options are available in the AutoCorrect dialog box:
Enable Additional Actions In the Right-Click Menu Turns the list of available actions on or off.
Available Actions Lists the currently installed actions.
More Actions Connects to the Microsoft Office Web site to find additional custom actions you can add to the list. You need to be connected to the Internet.
Properties Tells you more about the action you select in the Available Actions list. You need to be connected to the Internet for this one.
Often when entering a large amount of data in one sitting, you end up typing the same entries repeatedly. The AutoComplete feature cuts down the amount of typing you need to do. It also increases the accuracy of your entries by partially automating them. AutoComplete is on by default, but you can turn it off by clicking the File tab, Options, and then Advanced, and then clearing the Enable AutoComplete For Cell Values check box in the Editing Options area.
When you begin typing a cell entry, AutoComplete scans all the entries in the same column and determines as each character is typed whether the column contains a possible match. (This works only when you are typing in a cell adjacent to other entries.) For example, in Figure 8-40, as soon as we typed Y in cell A14, AutoComplete finished the entry with the unique match found in the same column: Young, Rob. The text added by AutoComplete is highlighted, so you can either continue typing, if that isn’t your intended entry, or press Enter or an arrow key to accept the completion and move to another cell.
Figure 8-40. Type enough letters to match an existing entry, and AutoComplete finishes it for you. As shown on the right, keep typing to override AutoComplete.
AutoComplete matches only exact cell entries, not individual words in a cell. For example, if you begin typing Tony in column A of the worksheet, AutoCorrect doesn’t intervene because it is not an exact match for any existing entry. Wisely, AutoComplete does not work when you’re editing formulas.
Instead of typing, you can right-click a cell and click Pick From Drop-Down List on the shortcut menu to select an entry from the same column, as shown in Figure 8-41. After Excel displays the list, click the entry you want, and Excel enters it in the cell. Of course, you can’t add new entries this way, as we did in Figure 8-40; only existing entries in the same column are available in the list.
Figure 8-41. Right-click the cell directly below a list, and click Pick From Drop-Down List to display a list of unique entries in the column.
Tip
INSIDE OUT Create Your Own Typing Shorthand
You can use AutoCorrect to monitor your own common typing errors and create your own typing shortcuts. Click the File tab, Options, and then click the AutoCorrect Options button in the Proofing category. Add your shorthand entries to the Replace Text As You Type area on the AutoCorrect tab. (Figure 8-38 shows the AutoCorrect dialog box.) Type the characters you want to use as the shorthand “code” in the Replace box, then type the characters with which you want to replace them in the With box, and finally click Add. For example, you can type MS in the Replace box and then type Microsoft Corporation in the With box. Thereafter, each time you type MS, Excel replaces it with the words Microsoft Corporation. Be sure you choose unique codes; otherwise, Excel might apply AutoCorrect to entries you don’t want changed.
Click the Review tab on the ribbon, and then click Spelling to check the spelling of an entire worksheet or any part of it. If Excel finds any suspect words, the Spelling dialog box shown in Figure 8-42 appears. Keep the following tips in mind when using the spelling checker:
If you select a single cell, Excel checks the entire worksheet, including all cells, comments, Excel graphic objects, and page headers and footers.
If you select more than one cell, Excel checks the selected cells only.
If the formula bar is active, Excel checks only its contents.
If you select words that are in the formula bar, Excel checks the selected words only.
If the range you select for checking the spelling contains hidden or outlined cells that are not visible, Excel checks these as well.
Cells that contain formulas are not checked.
Figure 8-42. Use the Spelling dialog box to review your text and add often-used words to your dictionary.
Click Options in the Spelling dialog box to display the Excel Options dialog box. Click the Proofing category, shown in Figure 8-43. Here you can access the AutoCorrect dialog box, choose dictionaries in different languages, and select special options such as the Ignore Internet And File Addresses check box.
Besides Spelling, the buttons in the Proofing and Language groups on the Review tab—Research, Thesaurus, and Translate—provide some real horsepower when you are ready to invest time in the word play accompanying your numeric adventures. Clicking any of these three buttons displays a task pane docked to the right side of the screen, as shown in Figure 8-44. Note that only the Research button is a toggle—clicking it a second time closes the task pane. Clicking either of the other two buttons opens the task pane if it is not already visible, but clicking a button again does not close the task pane; it just changes what appears within it.
The cell or range selected when you open the task pane automatically appears in the Search For box. Click the arrow button to the right of the Search For box to execute the search; any search results appear in the main area of the task pane. If you already have the task pane open and you want to change the search text, you can either type it or press Alt while clicking a cell containing the text you want, which then appears in the Search For box.
The second box under Search For is a drop-down list that lets you narrow your search to particular resources, as shown in Figure 8-45. As you try different searches in the task pane, click the Back and Next buttons (a.k.a. Previous Search and Next Search) to peruse the various search results.
You can alter the contents of the drop-down list shown in Figure 8-45 by clicking Research Options, located at the bottom of the task pane, which displays the Research Options dialog box, shown in Figure 8-46. Here you can add places to look to the list, remove places, add to or remove the available options, and specify parental controls (if you are logged on as an administrator). For details on a particular service, select it, and click the Properties button.
At the bottom of the task pane is a Get Services On Office Marketplace link, which connects you to the Microsoft Office Web site where Excel then checks to see whether there are any updates to existing services or any new services available that you can add to your research options.
Tip
You can reposition or resize the task pane by clicking the small arrow next to the Close button and choosing the appropriate command. Clicking the Move command changes the cursor to a four-headed arrow, letting you drag the task pane away from its docked position on the right side of the screen. You can let it float above the workspace or dock it on the left side if you prefer. After clicking the Size command, drag the mouse (you don’t even need to click) to reposition the border of the task pane to make it wider, up to half the screen width when docked. Click when the size of the task pane is to your liking.
In actuality, you don’t need these commands at all. You can drag a task pane away from the dock by its title bar at any time. You can drag the borders of the undocked task pane with impunity, making it as large as you like. To restore a floating task pane to its previous docked configuration, double-click its title bar. To remove a task pane from view, click the Close button in its upper-right corner.