Controlling Other Elements of the Excel 2007 Interface

In several important locations in Excel, you can control the way your worksheets appear on the screen. These include the Popular and Advanced categories in the Excel Options dialog box and the View tab on the Ribbon, as shown in Figure 3-14. Some of these options, such as Gridline Color, are self-explanatory; here we'll talk about options with "issues."

Figure 3-14: The View tab on the Ribbon contains commands you can use to control the appearance of your workbook.

The Show/Hide group on the View tab controls the display of the formula bar as well as the appearance of gridlines, column and row headings, and more. These are the options that are most often used, which is why they appear on the Ribbon. (The Ruler option applies only in Page Layout view, and the Message Bar option becomes active only when a security alert appears.) But you'll discover more ways to tweak your UI when you click the Microsoft Office Button and then the Excel Options button.

For more about Page Layout view, see Chapter 11, "Printing and Presenting." For more about security issues, see Chapter 4, "Security and Privacy."

The Popular category includes the Color Scheme drop-down list, which you can use to choose the overall look of the program. But the Advanced category is where most of the action is; it contains three groups of options, shown in Figure 3-15 (you'll need to scroll down a bit), that control different display behaviors for the program in general and for workbooks and worksheets in particular.

Figure 3-15: The Advanced category in the Excel Options dialog box includes a number of display options.

The options you select in the Display Options For This Workbook area affect only the workbook selected in the drop-down list, which lists all the currently open workbooks; these options do not change the display of any other workbooks, and they do not affect the way the worksheets will look when you print them. Similarly, the options in the Display Options For This Worksheet area apply only to the worksheet you select in the drop-down list.

Displayed vs. Printed Gridlines and Headlines

Typically, Excel displays a grid to mark the boundaries of each cell on the worksheet and also displays row and column headings. Although the grid and headings are usually helpful for selecting and navigating, you might not want them displayed all the time. To hide these items, clear the Gridlines or Headings check box on the View tab on the Ribbon.

Clearing these check boxes on the View tab removes gridlines and headings from your screen but does not affect whether they will be printed. If you want them printed but not displayed (or vice versa), click the Page Layout tab, and then in the Sheet Options group, select or clear the View and Print options under Gridlines or Headings. For convenience, both of these View options on the Page Layout tab are linked to corresponding Gridlines and Headings options on the View tab.

For more about printing a document, see Chapter 11.

Displaying Underlying Formulas

Usually when you enter a formula in a cell, you see the results of that formula, not the formula itself. Similarly, when you format a number, you no longer see the underlying (unformatted) value displayed in the cell. You can see the underlying values and formulas only by selecting individual cells and looking at the formula bar or by double-clicking the cell.

By selecting the Show Formulas In Cells Instead Of Their Calculated Results check box in the Excel Options dialog box (shown in Figure 3-15), you can display underlying values and formulas. Select a worksheet in the Display Options For This Worksheet drop-down list. As you can see in the worksheet view shown at the bottom of Figure 3-16, the underlying contents of each cell appear, as in the sum formulas in rows 6 through 10, and all the cells are left-aligned. (Excel 2007 ignores any alignment formatting when you select the Show Formulas check box.) In addition, the width of each column on the worksheet approximately doubles to accommodate the underlying formulas. (The actual width of the columns remains unchanged; columns appear wider only on the screen.) When you clear the Show Formulas check box, Excel restores all columns to their former widths.

Figure 3-16: Display underlying values and formulas for easier auditing.

Note 

You can quickly display and hide formulas in your worksheet by pressing Ctrl+' (a single opening quote), which is located on the tilde key on most keyboards. To redisplay values, press Ctrl+' again.

Note 

If you click the New Window command on the View tab to create two or more windows in which to view the same workbook, you can use different display options in each window. For example, you can display formulas in one window and see the results of those formulas (the usual view) in another window.

The Show Formulas check box is particularly helpful when you need to edit a large worksheet. You can see your formulas without having to activate each cell and view its contents on the formula bar. You can also use the Show Formulas check box to document your work: After you select the Show Formulas check box, you can print your worksheet with the formulas displayed for archiving purposes.

Hiding Zeros

Usually, zeros entered in cells, or the results of formulas that produce zero values, display on your worksheet. Sometimes, especially for presentation purposes, it is helpful to eliminate the clutter of excessive zero values on a worksheet. Clearing the Show A Zero In Cells That Have Zero Value check box in the Advanced category in the Excel Options dialog box causes any such cells to appear as blank cells on the worksheet. The underlying entries are unaffected, of course. If you edit an entry or if the result of a formula changes so the cell no longer contains a zero value, the value immediately becomes visible. If the Show Formulas check box is also selected, clearing the Show A Zero check box has no effect on the display.

Caution 

If you hide zero values, be careful when editing your worksheet. What appears to be an empty cell might actually contain a formula.

Changing the Standard Display Font

The standard display font is used not only for all text and numbers you enter in a workbook, but it also determines the font used in row and column headings. You can change this in the Popular category in the Excel Options dialog box. The default font is Body Font, which uses the font defined as such in the current theme. The new standard display font will not become active until you exit and restart Excel. The next time you start Excel, all new workbooks you create display using the new font. You can learn a lot more about formatting and themes in Chapter 9.