C.6. Tools Menu
The Tools menu provides quick access to a variety of Excel features, including change tracking, what-if data analysis, formula troubleshooting, and the omnipresent spell checker. You can also use the Customize command to tailor Excel's menus and toolbars and the Options command to review and tweak a cornucopia of Excel settings.
The spell checker (Tools Spelling) begins scanning the current worksheet from left to right (in the current row), and then moves down to cover all the rows below the current row. The spell checker examines only cells that contain text, and it verifies each word against its dictionary. When it finds an error, the spell checker prompts you with a list of suggestions, just as in other applications like Word. For more information about customizing the spell checker, see Section 5.3.1 (Chapter 5).
The Research task (Tools Research), which appears on the right side of the Excel window, lets you retrieve all kinds of information from the Internet. The Research task provides a fairly impressive set of services that let you search a dictionary for detailed definitions, a thesaurus for synonyms, and the Encarta encyclopedia for information on a range of topics. You can also get stock market quotes from the MSN Money Web site. Although the Research task's data is usually more suited for a Word document than an Excel workbook, it still provides a great tool for quickly getting oodles of free information. Microsoft hopes that third-party companies will develop services that plug into the Research frameworkbut, currently, only a few exist (and they're all fee-based).
This command performs an error check of your worksheet, beginning from the current cell and moving rightward through the columns and then down to subsequent rows. Every time Excel finds a cell with a formula that's produced an error (see Table 7-2 in Chapter 7 for information about the different Excel error codes), the Error Checking dialog box appears with the offending formula. You can then click Next to skip the error, Edit in Formula Bar to try and fix it, or Show Calculation Steps to try and find out where the problem occurred.
The "Show Text To Speech Toolbar" command in this menu launches a toolbar that lets you use Office's text-to-speech feature. You can then speak into a microphone and Excel will try to translate your commands into words and numbers , which it then inserts into the current cell. (You can also use specific instructions to move from cell to cell, open files, and print documentsas long as you're very careful and don't have chatty coworkers nearby.) Almost everybody finds text-to-speech awkward and impractical , and it's dramatically slower than the old-fashioned keyboard approach. Even though text-to-speech debuted in Office with much fanfare, and it impressed technogeeks everywhere, you'd be hard pressed to find anyone using it today.
The Shared Workspace task (Tools Shared Workspace) lets you open an existing document workspace or create a new one. A document workspace is a feature of Microsoft SharePoint that lets groups of people collaborate on the same Office documents. In order for this feature to work, you need to have a SharePoint server on your network. For more information about SharePoint, which isn't covered in this book, you can surf to www.microsoft.com/sharepoint or read a dedicated book like Microsoft SharePoint: Building Office 2003 Solutions by Scot P. Hillier (Apress, 2004).
The Share Workbook dialog box (Tools Share Workbook) lets you turn on workbook sharing. When your workbook is shared, Excel automatically keeps a change log that tracks all edits. Excel also lets multiple people open and edit the workbook at the same time, provided the workbook is stored in a location that more than one person can access (typically a network drive). Page Section 21.5 (Chapter 21) describes workbook sharing in more detail.
This menu provides options for using Excel's change tracking feature. When you select Highlight Changes to start change tracking, you'll see the Highlight Changes dialog box, where you can choose what types of changes you want to highlight so you can spot them easily. Once you click OK, Excel prompts you to resave the workbook, and it begins keeping a change log that records all the edits that you make to the workbook. At a later point, you can reject or accept changes using the Accept or Reject Changes tool, which scans through your worksheet and prompts you to review each change. Chapter 21 describes Excel change tracking.
Use this command to merge two versions of the same document. In order for this feature to work, you must turn on both workbook sharing and change tracking. (To do so, select Tools Track Changes Highlight Changes, turn of the "Track changes while editing," and click OK.) You'll be prompted to resave your workbook file. Now, Excel tracks any changes you make to the document and stores them behind the scenes using a workbook change log. If you send a copy of this document to another person, and they make changes and send it back to you, you can merge their changes with the latest version of your edits by selecting the Compare and Merge Workbooks command. Page Section 21.3.6 (Chapter 21) has details on how this procedure works.
This menu provides commands for protecting and unprotecting a worksheet. When a worksheet is protected (select Protect Sheet), locked cells can't be edited. You also have the ability to restrict other types of changes, like formatting, by selecting options in the Protect Sheet dialog box. All new worksheets begin with all of their cells locked. However, this behavior has no effect because Excel ignores the locked setting until you turn on worksheet protection. If you want to allow editing in certain regions , you need to unlock the cells (select Format Cells, choose the Protection tab, and turn off the Locked setting) before you apply worksheet protection. You can also supply a password when protecting a sheet. If you do, you'll need to provide this password when you select Unprotect Sheet to remove protection. For more information about worksheet protection, see Section 15.4.4 (Chapter 15).
This menu provides commands for two little-used Excel features: Web discussions and online meetings. Both these features let groups of people collaborate on their work. However, in order for a Web discussion to take place, you need to be on a network where an administrator has set up a discussion server. In order to hold an online meeting, you need to use a program called NetMeeting. To create a discussion server, you need to have a server computer that's running the Office Server Extensions. (You can get detailed information at www.microsoft.com/office/ork/2000/five/75ct.htm and www.microsoft.com/technet/prodtechnol/office/office2000/tips/osefaq.mspx.) NetMeeting is a program that ships with all versions of Windows. However, Microsoft has deemphasized it recently in favor of Microsoft Messenger. Although modern versions of Windows, such as Windows XP, still provide NetMeeting, you won't find a shortcut for this application in the Start menu. Instead, you'll have to go hunting for the conf.exe NetMeeting file. (For more information about NetMeeting, see www.microsoft.com/ windows /netmeeting.)
This command shows the Goal Seek dialog box. (Goal seeking lets you determine what data you need to reach a specific result. For example, you could use goal seeking to find the amount of monthly sales you need to meet a specific profit target). In the Goal Seek dialog box, you specify the cell that contains the formula you're trying to optimize ("Set cell"), the result you want this cell to produce ("To value"), and the cell that Excel can modify to try and get the result ("By changing cell"). Page Section 19.2 (Chapter 19) takes you through all the details.
The Scenario Manager dialog box (Tools Scenario) lets you create and apply scenarios, which let you test different possibilities. (Each scenario saves different test values. When you select a scenario, Excel inserts these into designated cells in your worksheet so you can see the result of your assumptions.) To create a scenario, click the Add button and specify the scenario name , and the cells that you want Excel to store as part of the scenario. To apply a previously saved scenario, select it in the list and click Show. Page Section 19.1 (Chapter 19) provides more details about Excel's scenario feature.
The Formula Auditing menu provides commands for troubleshooting erroneous formulas and examining how calculations work. If you use the Evaluate Formula command to analyze, step-by-step, how Excel calculates a formula, you'll be able to see exactly when an error occurs. You can also add arrows to your worksheet to reveal which cells supply values for a formula (the precedents ) and which cells use the results of a formula (the dependents ). To add these arrows, use the Trace Precedents and Trace Dependents commands. You can remove them with the Remove All Arrows command. Finally, the Show Formula Auditing Toolbar displays a Formula Auditing toolbar with many of the same commands, so that they're readily available while digging through a thorny problem. Page Section 12.5 (Chapter 12) describes Excel's formula auditing features in much more detail.
This command appears only if you've turned on the Solver add-in. When you select this command, the Solver Parameters dialog box appears. Using this dialog box, you can set a Solver model to solve data optimization problems. A Solver model is like Excel's goal-seeking feature but more powerfulit attempts to achieve a desired result by changing one or more cells, while respecting the constraints that you define. Solver models, which are covered in Chapter 19, are fairly complex.
This menu lets you create and replay Excel macros. (Macros are tiny programs that automate common tasks .) To record a new macro, select Record New Macro. Once you choose a name and click OK, Excel begins recording your every move, until you click Stop Recording. You can play existing macros using the Macros command, which shows a list of macros in your currently open workbooks and the personal macro workbook. To play one of these macros, select it and click Run. You can also click Edit to edit the macro code in the Visual Basic Editor. For more information about creating basic macros, see Chapter 25. For information about the Visual Basic Editor and macro code, see Chapter 26.
The Add-Ins dialog box (Tools Add-Ins) lets you choose which Excel-add-ins you want to use. (Add-ins are miniature programs that extend the power of Excel. Add-ins typically come with their own menu options or functions.) Add-ins that appear in the list with a checkmark next to them are currently running. You can clear a checkmark to turn off an add-in, or enter a checkmark to switch on a new add-in.
The AutoCorrect dialog box (Tools AutoCorrect) lets you adjust various settings to control the way Excel goes about fixing minor typos and capitalization errors while you type. Page Section 2.2.2 (Chapter 2) describes some of the most useful AutoCorrect settings.
The Customize dialog box (Tools Customize) switches Excel into a special customization mode that lets you modify its menus and toolbars. You can then rearrange and remove menu and toolbar items just by dragging them. You can also rename them (and change their pictures) by right-clicking one and choosing an option from the pop-up menu. Most useful of all, you can drag new items onto a menu or a toolbar from the Commands tab in the Customize dialog box. For more information on customizing Excel, see Appendix B.
The Options dialog box (Tools Options) provides a large collection of tabs where you can configure different Excel settings. Some of the most useful settings include the General tab (which lets you specify the folder where Excel assumes you store all your workbook files and your user name), the Save tab (which stores the AutoRecover settings that determine how often Excel backs up your work, and where it places the backups ), and the Custom Lists tab (where you can set up lists that Excel will automatically use for its AutoFill feature, as described on Section 2.2.3 in Chapter 2).
This command appears only if you've turned on the Analysis ToolPak add-in. If you have, you can use this command to trigger a variety of advanced statistical functions. When you select the Data Analysis command, a dialog box appears with all the types of analysis you can perform ( ranging from the obscure "Anova: Two Factor with Replication" to the cryptic "z-Test: Two Samples for Means"). Once you make a choice and select OK, Excel prompts you for the worksheet references and arguments it needs. Make sure you've got your Ph.D. in math ready before checking out these tools.