Microsoft® Excel 97 Quick Reference
You can customize Excel in a number of ways to suit your needs. You can have Excel display certain workbooks when it starts. In addition, you can choose to display formulas instead of the results of the formulas. You also can hide (and unhide) an entire workbook, worksheets within a workbook, and row and column headings.
Excel enables you to move, resize, and customize on-screen menus and toolbars. You also can record macros that duplicate common keystrokes; you can even assign a macro to a toolbar button. If you create workbooks used by others, you can create a template that contains all the necessary data, formulas, macros, and formatting.
In Excel, you can use many different methods to adjust the display of windows, or adjust how you view data in your workbooks. You can freeze the worksheet pane in large worksheets, for example, so that row and column headings always remain on-screen. You also can zoom the display and move or size windows.
In Windows, you can switch among different international character sets, time and date displays, and numeric formats. The international settings you choose show up in the formatting in your Excel worksheets and in other Windows applications. When you choose Format, Cells in Excel, for example, the Number tab shows number and date/time formats for the country or regions you have selected in the Windows Control Panel.
The Regional Settings Properties dialog box enables you to change the country, language, date, currency, and other formats. Changes to these settings aren't permanent--you can change them again at any time.
Excel uses a palette of 56 colors--40 colors for worksheets and 16 colors for charts. You can change the palette colors used in the current workbook. After you define a set of colors, you can then copy those colors to other workbooks.
You can change the colors in a color palette in two ways. You can select new colors from a grid of standard colors, using the Standard tab of the Colors dialog box; or you can create your own custom colors, using the Custom tab in the Colors dialog box.
To fine-tune a color, use the text boxes below the Colors box. Each text box includes a number from 0 to 255. Hue is the actual color itself; Sat (saturation) is the color intensity; Lum (luminosity) is the color brightness; Red, Green, and Blue indicate the level each of these colors contributes to the overall color. Drag the slider beside the Colors box to adjust the luminosity of the selected color. To return the palette to its original set of 56 colors, click the Reset button on the Color tab.
To copy a color palette from one workbook to another, open both workbooks and activate the workbook that will receive the new palette. Choose Tools, Options; then click the Color tab. In the Copy Colors From list box, select the name of the workbook from which you are copying colors; then click OK. Existing colored objects in the workbook receiving the new palette automatically change to reflect the new palette.
(See also "Colors and Patterns" in the "Formatting" section.)
In the normal worksheet view, screen elements such as the title bar, toolbars, the formula bar, and the status bar take up on-screen space. Excel enables you to switch the display to a full-screen view, in which only the worksheet is displayed along with the menu bar and sheet tabs. This enables you to see more data in the worksheet on-screen at one time. You can switch between these views at any time.
You can add one or more toolbars to the full-screen view by choosing the View, Toolbars command and selecting the toolbar you want to display.
TIP: To hide or display screen elements, choose Tools, Options; then click the View tab and make the desired selections.
(See also "Zooming: The Worksheet Display.")
In the default workbook view, you only see one worksheet at a time in the workbook's window. When you click a sheet tab to display a different worksheet, that worksheet then displays using the entire workbook window. Excel enables you to display multiple worksheets in separate windows if you want to see them on-screen at one time.
(See also "Windows: Moving or Sizing.")
You can hide the worksheet gridlines (and other screen elements) to make the display appear less like a spreadsheet and more like a graphic or paper display. Hiding the on-screen gridlines also gives a better appearance to data-entry forms and on-screen reports. You may want to display the gridlines while you build formulas or place text boxes and objects in a worksheet.
TIP: If you want to change the color of the on-screen gridlines, choose Tools, Options; then click the View tab. Ensure that the Gridlines check box is selected and select a color from the Color drop-down list; then click OK.
NOTE: Do not confuse the display of gridlines in the worksheet on-screen with printed gridlines. The Gridlines check box in the Page Setup dialog box controls the printing of gridlines.
(See also "Printing: Worksheet Gridlines" in the "Producing Output" section.)
If you are working with a small number of rows and columns, you may not need to refer to the column and row headings (sometimes referred to as the worksheet frame) as you work. You may also choose to remove these headings from the screen to increase the visible workspace on-screen, or to make the screen appear less cluttered when giving an on-screen presentation.
NOTE: There are other parts of the Excel worksheet that you can hide from view. Some of these options appear on the View tab when you choose Tools, Options. For example, you can hide the Formula Bar, the Status Bar, the Sheet Tabs, and the Horizontal or Vertical scroll bars.
Workbooks can become quite cluttered as you work with them. To simplify your workspace, you can hide an entire workbook window. If only one workbook is open in Excel, when you hide the window, the entire workbook is hidden. (See also "Hiding: Worksheets.")
If you need to modify or view a hidden workbook, you will first need to restore the workbook. Choose Window, Unhide. In the Unhide dialog box, select the name of the workbook you want to display; then click OK.
CAUTION: When you hide a workbook, it remains hidden after you quit Excel and then try to open the workbook again. If you will not be using your workbook for some time, or if others need to access the same workbook, you may forget that you have hidden the workbook. You should therefore remember to unhide workbooks each time you exit Excel.
(See also "Protecting: Workbooks" in the section "Editing Workbooks.")
You may want to hide only selected worksheets in a workbook. Perhaps you have three worksheets and want to work with just two of them, or you may want to hide a worksheet in a shared workbook so that others will not see the worksheet. (See also "Hiding: Workbooks.")
If you need to modify or view a hidden worksheet, you will first need to restore the worksheet. Choose Format, Sheet, Unhide. In the Unhide dialog box, select the name of the worksheet you want to display; then click OK.
TIP: You can hide more than one worksheet in a workbook. Click the first sheet tab, then hold down Ctrl and click the other sheets you want to hide. Then follow the steps above to hide the selected worksheets.
NOTE: If your workbook includes just one worksheet, you cannot hide that worksheet. To get around this problem, insert a new, blank worksheet in your workbook; then hide the other worksheet.
(See also "Protecting: Individual Worksheets" in the section "Editing Workbooks.")
You can attach macros you create in Excel to a button control on a worksheet. This feature enables you to run a macro with the click of a single button. You can attach macros to buttons if you create data-entry forms used by others, for example; then they can just click the button to run a simple to complex macro procedure. Some of Excel's predefined templates include button controls with macros attached to them. (See "Macros: Recording Macros" before you complete this task.)
Hold down Ctrl and then click the button to select it. When a button is selected, handles appear around the object. You can then move, resize, or change the properties of the button. Drag an edge of the button to move it. Resize the button by dragging one of the handles. Delete the button by pressing Delete. Click outside the button to deselect it.
TIP: To change the name displayed on the button, hold down Ctrl and then click the button. Use the normal editing keys to edit the text in the button.
(See also "Forms: Adding Controls" in the section "Data Analysis.")
To help prevent macros from corrupting your system, Excel provides a feature that checks each workbook you open for the existence of macros (some Excel viruses are started from macros). If a workbook contains one or more macros, Excel displays a warning message notifying you of this. If you see this message, you should only continue to open the workbook if you trust the source from which you obtained the workbook. This message is only an indication that the workbook contains macros; Excel is not able to check whether or not the macros actually contain viruses, or if there are any other viruses associated with that workbook (not related to macros).
NOTE: You can purchase antivirus software that scans your workbooks and removes known macro viruses. To obtain information on this software, access Microsoft's Web site (http//:www.microsoft.com) and search on "virus."
If you find yourself performing tedious, repetitive actions over and over again in your worksheets, you can automate the task by creating a macro. A macro is a stored list of commands and keystrokes that are automatically executed by Excel. You use Excel's Record feature to record the actions you perform, and then store these recorded keystrokes as the macro. The next time you need to perform the action, you can run the macro.
After you have created a macro, you then run the macro to execute the commands. You can run a macro from the Tools menu, from an assigned shortcut key combination (if you assigned one to the macro), or from a macro button (if you attached the macro to a button). (See "Macros: Recording Macros" before you complete this task.)
If you did not assign a macro to a shortcut key when you created the macro, you can assign it later. Choose Tools, Macro, Macros; select the macro name and click the Options button. Type a letter in the Shortcut Key text box; then click OK. To run the macro using the shortcut key, press Ctrl+letter. If you pressed Shift when you typed the letter in the text box, you must press Shift+Ctrl+letter to run the macro.
To run a macro you assigned to a button control on the worksheet, click the button.
(See also "Macros: Attaching to Buttons.")
After you record a macro, you can view the macro code in the Visual Basic Editor and modify the macro, as necessary. If you aren't very familiar with Visual Basic, the language Excel uses to record macros, you may want to experiment with creating macros that perform different types of tasks. You then can view the code to see how your actions translate to Visual Basic. This may make it easier for you to understand how to edit the macro, if that becomes necessary. (See "Macros: Recording Macros" before you complete this task.)
Excel includes many more commands than you would ever want to place on a menu at one time. You might want to add the commands you use most often onto an existing or custom menu, however. You can add any command to a menu that you want to have easy access to. As with toolbar buttons, when you add commands to a menu, they are available in any workbook. You can always remove commands you added to a menu, or use a reset command to revert the menu to Excel's default menu.
To remove a new command from a menu, choose Tools, Customize; then click the Commands tab. Display the menu in the workbook that contains the added command; then drag the command off the menu to remove it. Click Close to close the Customize dialog box.
If you added multiple commands to a menu, or you aren't sure exactly what changes you made to a menu, or you think you deleted an Excel menu item by mistake, you can easily reset the menu back to Excel's defaults. Choose Tools, Customize; then click the Commands tab. Display the menu in the workbook that you want to reset; then click the Modify Selection button in the Customize dialog box, and click the Reset option. Click Close to close the Customize dialog box.
In Excel 97, you can move the menu bar to display anywhere on the screen--on any edge of the screen or as a floating menu bar inside the worksheet area. This works in a similar way to moving toolbars. In most cases, however, you will probably want to keep the menu docked to the top of the worksheet, just below the title bar.
To move the menu back to its original location, drag the menu and drop it just below the title bar.
By default, Excel moves the active cell down one cell after you type data in a cell and press Enter. If you type data in cell B2 and press Enter, for example, cell B3 becomes the next active cell; if you then type an entry in cell B3 and press Enter, then cell B4 becomes the active cell, and so on. You can change this option so that the active cell is one cell up, down, or to the left. If you deselect the option, the pointer stays in the same cell when you press Enter.
If you have customized the worksheet view and want to save the view settings so that you can later return to them, you can create a custom view. Custom views save settings such as hidden rows or columns, filter settings, and print settings. (See also "Displaying: A Worksheet Full Screen.")
TIP: To later access the custom view, choose View, Custom Views; then select the name of the view in the Views list box and click Show.
If you use the same Excel workbook every day, such as a sales data workbook, you can place that workbook on the Windows 95 Start menu. Then, when you click the workbook icon on the Start menu, both Excel and the workbook open automatically.
(See also "Startup: Setting Startup Switches.")
You can set a number of switches to control how Excel starts. For instance, you might not want to see the Excel startup screen and a new, blank workbook each time. For information on all the available startup switches in Excel, search on "switches" in the Excel on-line Help.
(See also "Startup: Controlling Excel's Startup.")
In addition to creating new, blank workbook files, you can also create new workbooks from templates. Excel provides a variety of predefined template files, ranging in functionality from business worksheets to personal financial templates. (See also "Template Wizard: Using Excel's Templates" in the section "Data Analysis.")
If one of Excel's predefined templates doesn't meet your needs, however, you can easily create your own custom template. Template files can contain all the data, formulas, macros, and formatting you need. This enables you to quickly get to work, rather than re-creating this information from scratch when you need it.
You can edit existing templates at any time if you need to change text, formatting, or any other elements in the template. You can then save the template using the same name or save it as a different template. In addition to editing your own workbook templates, you also can edit Excel's predefined templates. (See "Templates: Creating and Using a Workbook Template" before you complete this task.)
TIP: To delete a template, select it in the Open dialog box and press the Delete key. Then click Yes to confirm the deletion, and click Cancel to close the Open dialog box.
NOTE: Changes made to templates only affect workbooks and worksheets that are created after the template used to create the new workbooks is modified. Existing workbooks are not changed when you edit a template on which they are based.
If you frequently use a command that is not represented on an Excel toolbar, you can easily add a button that performs the command to the toolbar. If you often format cells with light shading, for example, you can add the Light Shading button to the Formatting toolbar. If there is not room to add a button to an existing toolbar, you can remove an existing button that you don't use on a toolbar, or you can create a new toolbar. (See also "Toolbars: Creating a New Toolbar.")
TIP: To remove a button from a toolbar, display the toolbar that includes the button you want to remove. Choose Tools, Customize; then click the Commands tab. Drag the button off the toolbar; then click Close.
TIP: To reset a toolbar to its original configuration, choose Tools, Customize; then click the Toolbars tab. Select the toolbar you want to reset, and then click the Reset button. Click OK to confirm the procedure; then click Close to close the dialog box.
You can use the Button Editor to change the appearance of an image on a toolbar button, pixel by pixel. Or, you can choose from a pop-up list of over 40 predefined images that Excel provides.
TIP: To use one of the predefined button images that Excel provides, display the toolbar containing the button whose image you want to change. Choose Tools, Customize. Right-click the toolbar button you want to change; then highlight the Change Button Image option. If you see an image you like, click the image. The button changes to display the new image. Click Close to close the Customize dialog box.
Although you can add individual buttons to Excel's existing toolbars, you may find that you want to create your own custom toolbar with multiple buttons used for specific tasks that you frequently perform. If you use a workbook that has unique formatting and printing requirements, for example, you can create a new toolbar with just those buttons related to formatting and printing that workbook. You also may want to create a new toolbar for use with custom templates you create. After you create a new toolbar, you can move or resize it just as you can move or resize Excel's predefined toolbars. (See also "Toolbars: Displaying or Hiding," "Toolbars: Adding Toolbar Buttons," and "Templates: Creating and Using a Workbook Template.")
TIP: If you want a custom toolbar to be available with a specific workbook, you can attach it to that workbook. Open the workbook, display the custom toolbar, and choose Tools, Customize; then click the Toolbars tab. Click the Attach button. In the Custom Toolbars list, select the toolbar you want to attach; then click the Copy button. Click OK and then click Close to return to the workbook.
When you start Excel, the Standard and Formatting toolbars appear on-screen by default. You will use these toolbars most often when you work in Excel. Excel also offers several additional toolbars you can display when you need them. Sometimes, different toolbars will automatically appear on-screen when you are performing certain procedures. When you create a pivot table, for example, the PivotTable toolbar appears on-screen. Toolbars that are currently on-screen display with check marks beside their name in the View, Toolbars menu.
TIP: If the toolbar is a floating toolbar, you can hide the toolbar by clicking the Close button in the toolbar's title bar.
(See also "Toolbars: Moving and Resizing.")
By default, the Standard and Formatting toolbars appear at the top of the screen, just below the title bar. You can move any toolbar so that it is attached to any edge of the screen or floating inside the workbook. Toolbars attached to an edge of the window are sometimes referred to as docked toolbars. If an on-screen toolbar is not docked, it is floating. Floating toolbars can be resized.
(See also "Toolbars: Displaying or Hiding.")
When working in large worksheets, the worksheet headings typically scroll from the screen as you move through the worksheet. Excel enables you to freeze the window pane at selected rows and columns so that they remain on-screen as you scroll through large worksheets. (See also "Windows: Splitting Worksheet Windows.")
To unfreeze the window pane, choose Window, Unfreeze Panes.
To obtain more space on your computer screen if it has become cluttered, you can store open applications or workbook windows by minimizing them so that they become buttons in the Windows taskbar at the bottom of the screen. When you need one of the applications or workbooks that has been minimized, you can restore the icon to its former window location and size. If you want a window to fill the entire available display area, you can maximize it.
To perform these procedures, you use the Minimize and Maximize/Restore icons located on the right end of a window's title bar (just beside the Close icon). When you click a Maximize icon to maximize a window, the icon changes to a Restore icon, and vice versa.
TIP: To switch between open workbook windows, choose Window; then select the window you want to display from the bottom of the Window menu.
With multiple workbooks on-screen, you will occasionally want to move workbook windows out of the way if they are obstructing data or other windows you want to see. You also can resize a window so that it takes up less (or more) space on-screen. You can only move or size a window if it is not maximized. (See also "Windows: Minimizing, Maximizing, and Restoring.")
(See also "Displaying: Worksheets in Separate Windows.")
In addition to freezing worksheet panes, Excel also enables you to split a worksheet in two, allowing you to view two different areas of one worksheet at the same time. When you split a worksheet, additional scroll bars appear, which you can use to scroll areas independently of one another. (See also "Windows: Freezing Worksheet Panes.")
TIP: You can drag the split bar to view a larger portion of one worksheet area.
Excel provides many options for viewing your worksheets. In addition to customizing the view and specifying which on-screen elements are displayed, you can zoom the worksheet to a percentage you specify. The default zoom percentage in a new worksheet is 100%, but you can change the zoom percentage to between 10% and 400%. Choose a percentage larger than 100% to zoom in on a worksheet and display enlarged text, or less than 100% to reduce the size of the displayed text and see more data on-screen.
TIP: You also can use the Zoom control in the Standard toolbar to change the magnification. Click the arrow beside the control and select the option you want; or, to enter a custom percent, click the control, type the zoom percentage, and then press Enter.
(See also "Displaying: A Worksheet Full Screen" in this section and "Microsoft IntelliMouse: Zooming" in the section "Getting Started.")
©Copyright, Macmillan Computer Publishing. All rights reserved.