Worksheets

The most common type of sheet is a worksheet, which you normally think of when you think of a spreadsheet. Excel 2016 worksheets have 16,384 columns and 1,048,576 rows.

Having access to more cells isn’t the real value of using multiple worksheets in a workbook. Rather, multiple worksheets are valuable because they enable you to organize your work better. Back in the old days, when a spreadsheet file consisted of a single worksheet, developers wasted a lot of time trying to organize the worksheet to hold their information efficiently. Now you can store information on any number of worksheets and still access it instantly.

You have complete control over the column widths and row heights, and you can even hide rows and columns (as well as entire worksheets). You can display the contents of a cell vertically (or at an angle) and even wrap around to occupy multiple lines. In addition, you can merge cells to form a larger cell.

Chart sheets

A chart sheet holds a single chart. Many users ignore chart sheets, preferring to use embedded charts, which are stored on the worksheet’s drawing layer. Using chart sheets is optional, but they make it a bit easier to locate a particular chart, and they prove especially useful for presentations.

Macro sheets and dialog sheets

This section discusses two obsolete Excel features that continue to be supported.

An Excel 4.0 macro sheet, whose purpose is to hold XLM macros, is a worksheet that has some different defaults. XLM is the macro system used in Excel version 4.0 and earlier. This macro system was replaced by VBA in Excel 5.0 and is not discussed in this book.

An Excel 5.0 dialog sheet is a drawing grid that can hold text and controls. In Excel 5.0 and Excel 95, dialog sheets were used to make custom dialog boxes. UserForms were introduced in Excel 97 to replace these sheets.

The Ribbon

The Ribbon is the primary UI component in Excel. The Ribbon provides the user with a single place to conveniently find every commonly used command and dialog box

Backstage View

The File tab is unlike the other tabs. Clicking the File tab doesn’t change the Ribbon but takes you to the Backstage View. This is where you perform most of the document-related activities: creating new workbooks, opening files, saving files, printing, and so on.

The Backstage View also gives you access to the Options dialog button, which opens a dialog box containing dozens of settings for customizing Excel.

Shortcut menus and the mini toolbar

Excel also features dozens of shortcut menus. These menus appear when you right-click after selecting one or more objects. The shortcut menus are context sensitive. In other words, the menu that appears depends on the location of the mouse pointer when you right-click. You can right-click just about anything—a cell, a row or column border, a workbook title bar, and so on.

Right-clicking items often displays the shortcut menu as well as a mini toolbar, which is a floating toolbar that contains a dozen or so of the most popular formatting commands.

Dialog boxes

Some Ribbon commands display a dialog box, from which you can specify options or issue other commands. You’ll find two general classes of dialog boxes in Excel:

• Modal dialog boxes: When a modal dialog box is displayed, it must be closed to execute the commands. An example is the Format Cells dialog box. None of the options you specify is executed until you click OK. Or click the Cancel button to close the dialog box without making any changes.
• Modeless dialog boxes: These are stay-on-top dialog boxes. An example is the Find and Replace dialog box. Modeless dialog boxes usually have a Close button rather than OK and Cancel buttons.

Customizing the UI

The Quick Access toolbar is a set of tools that the user can customize. By default, the Quick Access toolbar contains three tools: Save, Undo, and Redo. If you find that you use a particular Ribbon command frequently, right-click the command and choose Add to Quick Access Toolbar. You can make other changes to the Quick Access toolbar from the Quick Access Toolbar tab of the Excel Options dialog box. To access this dialog box, right-click the Quick Access toolbar and choose Customize Quick Access Toolbar.

You can also customize the Ribbon by using the Customize Ribbon tab of the Excel Options dialog box. Choose File ➜ Options to display the Excel Options dialog box.

You can customize the Ribbon in these ways:

• Add a new tab.
• Add a new group to a tab.
• Add commands to a group.
• Remove groups from a tab.
• Remove commands from custom groups.
• Change the order of the tabs.
• Change the order of the groups within a tab.
• Change the name of a tab.
• Change the name of a group.
• Move a group to a different tab.
• Reset the Ribbon to remove all customizations.

That’s a fairly comprehensive list of customization options, but there are some actions that you cannot do:

• You cannot remove built-in tabs, but you can hide them.
• You cannot remove commands from built-in groups.
• You cannot change the order of commands in a built-in group.

Task panes

Yet another user interface element is the task pane. Task panes appear automatically in response to several commands. For example, when working with a picture, you can right-click the image and choose Format Picture. Excel responds by displaying the Format Picture task pane. A task pane is similar to a dialog box except that you can keep it visible as long as it’s needed.

By default, the task panes are docked on the right side of the Excel window, but you can move them anywhere you like by clicking the title text and dragging. Excel remembers the last position, so the next time you use a particular task pane, it will be where you left it. There’s no OK button in a task pane. When you’re finished using a task pane, click the Close button (X) in the upper-right corner.

Customizing onscreen display

Excel offers some flexibility regarding onscreen display (status bar, Formula bar, the Ribbon, and so on). For example, click the Ribbon Display Options control (in the title bar), and you can choose how to display the Ribbon. You can hide everything except the title bar, thereby maximizing the amount of visible information.

You can customize the status bar at the bottom of the screen. Right-click the status bar, and you see lots of options that allow you to control what information is displayed.

Many other customizations can be made by choosing File ➜ Options and clicking the Advanced tab. On this tab are several sections that deal with what displays onscreen.

Numeric formatting

Numeric formatting refers to how a value appears in the cell. In addition to choosing from an extensive list of predefined formats, you can create your own custom number formats in the Number tab of the Format Cells dialog box. (Choose the dialog box launcher at the bottom of the Home ➜ Number group.)

Excel applies some numeric formatting automatically, based on the entry. For example, if you precede a value with your local currency symbol (such as a dollar sign), Excel applies Currency number formatting. If you append a percent symbol, Excel applies Percent formatting.

The number format doesn’t affect the actual value stored in the cell. For example, suppose that a cell contains the value 3.14159. If you apply a format to display two decimal places, the number appears as 3.14. When you use the cell in a formula, however, the actual value (3.14159)—not the displayed value—is used.

Stylistic formatting

Stylistic formatting refers to the cosmetic formatting (colors, shading, fonts, borders, and so on) that you apply to make your work look good. The Home ➜ Font and Home ➜ Styles groups contain commands to format your cells and ranges.

Document themes allow you to set many formatting options at once, such as font, colors, and cell styles. The formatting options contained in a theme are designed to work well together. If you’re not feeling particularly artistic, you can apply a theme and know the colors won’t clash. All the commands for themes are in the Themes group of the Page Layout tab.

Don’t overlook Excel’s conditional formatting feature. This handy tool enables you to specify formatting that appears only when certain conditions are met. For example, you can make the cell’s interior red if the cell contains a negative number.

Securing access to the entire workbook

Perhaps the best way to protect your Excel file is to use Excel’s protection options for file sharing. These options enable you to apply security at the workbook level, requiring a password to view or make changes to the file. This method is by far the easiest to apply and manage because there’s no need to protect each worksheet one at a time. You can apply a blanket protection to guard against unauthorized access and edits. Take a moment to review the file-sharing options, which are as follows:

• Forcing read-only access to a file until a password is given
• Requiring a password to open an Excel file
• Removing workbook-level protection

The next few sections discuss these options in detail.

Permitting read-only access unless a password is given

You can force your workbook to go into read-only mode until the user types the password. This way, you can keep your file safe from unauthorized changes yet still allow authorized users to edit the file.

Here are the steps to force read-only mode:

1. With your file open, click the File tab.
2. To open the Save As dialog box, select Save As and then double-click the This PC icon.
3. In the Save As dialog box, click the Tools button and select General Options (see Figure 1.1). The General Options dialog box appears.
4. Type an appropriate password in the Password to Modify input box (see Figure 1.2), and click OK.
5. Excel asks you to reenter your password, so reenter your chosen password.
6. Save your file to a new name.

At this point, your file is password protected from unauthorized changes. If you were to open your file, you’d see something similar to Figure 1.3. Failing to type the correct password causes the file to go into read-only mode.

Requiring a password to open an Excel file

You may have instances in which the data in your Excel files is so sensitive that only certain users are authorized to see it. In these cases, you can require your workbook to receive a password to open it. Here are the steps to set up a password for the file:

1. With your file open, click the File tab.
2. To open the Save As dialog box, select Save As and then double-click the Computer icon.
3. In the Save As dialog box, click the Tools button and select General Options (refer to Figure 1.1). The General Options dialog box opens.
4. Type an appropriate password in the Password to Open text box (as shown in Figure 1.4), and click OK.
5. Excel asks you to reenter your password.
6. Save your file to a new name.

At this point, your file is password protected from unauthorized viewing.

Limiting access to specific worksheet ranges

You may find that you need to lock specific worksheet ranges, preventing users from taking certain actions. For example, you may not want users to break your formulas inserting or deleting columns and rows. You can prevent this by locking those columns and rows.

Unlocking editable ranges

By default, all cells in a worksheet are set to be locked when you apply worksheet-level protection. You can’t alter the cells on that worksheet in any way. That being said, you may find that you need certain cells or ranges to be editable even in a locked state, as in the example shown in Figure 1.5.

Before you protect your worksheet, you can unlock the cell or range of cells that you want users to be able to edit. (The next section shows you how to protect your entire worksheet.) Here’s how to do it:

1. Select the cells you need to unlock.
2. Right-click and select Format Cells.
3. On the Protection tab, as shown in Figure 1.6, deselect the Locked check box.
4. Click OK to apply the change.

Applying worksheet protection

After you’ve selectively unlocked the necessary cells, you can begin to apply worksheet protection. Just follow these steps:

1. To open the Protect Sheet dialog box, click the Protect Sheet icon on the Review tab of the Ribbon (see Figure 1.7).
2. Type a password into the text box (see Figure 1.8) and then click OK. This is the password that removes worksheet protection. Note that because you can apply and remove worksheet protection without a password, specifying one is optional.
3. In the list box (see Figure 1.8), select which elements users can change after you protect the worksheet. When a check box is checked for a particular action, Excel prevents users from taking that action.
4. If you provided a password, reenter it.
5. Click OK to apply the worksheet protection.

Removing worksheet protection

Just follow these steps to remove any worksheet protection you may have applied:

1. Click the Unprotect Sheet icon on the Review tab.
2. If you specified a password while protecting the worksheet, Excel asks you for that password (see Figure 1.9). Type the password and click OK to immediately remove protection.

Protecting the workbook structure

If you look under the Review tab in the Ribbon, you see the Protect Workbook icon next to the Protect Sheet icon. Protecting the workbook enables you to prevent users from taking any action that affects the structure of your workbook, such as adding/deleting worksheets, hiding/unhiding worksheets, and naming or moving worksheets. Just follow these steps to protect a workbook:

1. Click the Protect Workbook icon on the Review tab of the Ribbon, which opens the Protect Structure and Windows dialog box as shown in Figure 1.10.
2. Choose which elements you want to protect: workbook structure, windows, or both. When a check box is cleared for a particular action, Excel prevents users from taking that action.
3. If you provided a password, reenter it.
4. Click OK to apply the worksheet protection.

Selecting Structure prevents users from doing the following:

• Viewing worksheets that you’ve hidden
• Moving, deleting, hiding, or changing the names of worksheets
• Inserting new worksheets or chart sheets
• Moving or copying worksheets to another workbook
• Displaying the source data for a cell in a pivot table Values area or displaying pivot table Filter pages on separate worksheets
• Creating a scenario summary report
• Using an Analysis ToolPak utility that requires results to be placed on a new worksheet
• Recording new macros

Choosing Windows prevents users from changing, moving, or sizing the workbook windows while the workbook is opened.