The Excel User Interface

A user interface (UI) is the means by which an end user communicates with a computer program. A UI includes elements such as menus, dialog boxes, toolbars, and keystroke combinations, as well as features such as drag and drop.

A New User Interface

Almost every Windows program you use employs the menu and toolbar approach. That is, at the top of the screen is a menu bar that contains virtually every command that's available in the application and below that, one or more toolbars, which provide shortcuts to some of the more frequently used commands. For Excel, and most of the other Microsoft Office applications, the days of menus and toolbars are over.

The new UI for Excel consists of components like the Ribbon, the Office Button, the Mini Toolbar, and the Quick Access toolbar. The Quick Access toolbar is the only UI element that can be customized by end users.

The Ribbon

The Ribbon is the primary UI component in Excel. It replaces the menu and most of the toolbars that were common in previous versions, and is a very significant departure from the interfaces of most Windows-based applications.

ONE-STOP SHOPPING

Microsoft felt that the commands contained in the old menu and toolbar system were becoming so numerous that a new paradigm was necessary. One of the main goals for developing the Ribbon was to provide the user with a single place to look for a particular feature. Every commonly used command available in Excel would be contained in the Ribbon (or in a dialog box accessed via the Ribbon). Although Microsoft succeeded in putting most of the available commands on the Ribbon, it's still a pretty big place. Long- time Excel users will have to endure a certain amount of frustration while they get used to the new layout.

Note 

A few commands failed to make the cut, but they are still available if you know where to look for them. Right-click the Quick Access toolbar and choose Customize Quick Access Toolbar. Excel displays a dialog box with a list of commands that you can add to your Quick Access toolbar (QAT). Some of these commands aren't available elsewhere in the UI.

TABS, GROUPS, AND TOOLS

The Ribbon is a band of tools that stretches across the top of the Excel window. About the vertical size of three of the old style toolbars, the Ribbon sports a number of tabs including Home, Insert, Page Layout, and others. On each tab are groups that contain related tools.

On the Home tab, for example, you find the Clipboard group, the Font group, and the Alignment group.

After you get past the Ribbon, the tabs, and the groups, you get to the tools, which are similar to the tools that existed on the old style toolbars with one major difference: their different sizes. Tools that are used most often are larger than less-frequently used tools. Half of the Clipboard group is consumed by the large Paste tool; the Cut, Copy, and Format Painter tools are much smaller. Microsoft determined that the Paste tool is the most used tool and thus sized it accordingly.

The Ribbon and all its components resize dynamically as the Excel window is resized horizontally. Smaller Excel windows collapse the tools on compressed tabs and groups, and maximized Excel windows on large monitors show everything that's available. Even in a small window, all Ribbon commands remain available. You just may need to click a few extra times to access them.

Figure 1-2 shows three sizes of the Ribbon when the Home tab is displayed using an increasingly smaller horizontal window size.

Figure 1-2: The Ribbon sizes dynamically, depending on the horizontal size of Excel's window.

NAVIGATION

Navigation of the Ribbon is fairly easy with the mouse. You click a tab and then click a tool. If you prefer to use the keyboard, Microsoft has added a feature just for you. Pressing Alt displays tiny squares with shortcut letters in them that hover over their respective tab or tool. Each shortcut letter that you press either executes its command or drills down to another level of shortcut letters. Pressing Esc cancels the letters or moves up to the previous level. For example, a keystroke sequence of Alt+H+B+B adds a double border to the bottom of the selection. The Alt key activates the shortcut letters, the H shortcut activates the Home tab, the B shortcut activates the Borders tool menu, and the second B shortcut executes the Bottom Double Border command. Note that it's not necessary to keep the Alt key depressed while you press the other keys.

CONTEXTUAL

TABS The Ribbon contains tabs that are visible only when they are needed. Generally, when a previously hidden tab appears, it's because you selected an object or a range with special characteristics (like a chart or a pivot table). A typical example is the Drawing Tools contextual tab. When you select a shape or WordArt object, the Drawing Tools tab is made visible and active. It contains many tools that are only applicable to shapes, such as shape- formatting tools.

SCREENTIPS AND DIALOG ICONS

Hovering over a tool on the Ribbon displays a ScreenTip that explains the command the tool will execute. ScreenTips are larger and, in most cases, wordier than the ToolTips from previous versions. They range in helpfulness from one word to long paragraphs and even pictures.

At the bottom of many of the groups is a small box icon (a dialog box launcher) that opens a dialog box related to that group. Users of previous versions of Excel will recognize these dialog boxes, many of which are unchanged. Some of the icons open the same dialog boxes but to different areas. For instance, the Font group icon opens the Format Cells dialog box with the Font tab activated. The Alignment group opens the same dialog box but activates the Alignment tab. The Ribbon makes using dialog boxes a far less-frequent activity than in the past because most of what can be done in a dialog box can be done directly on the Ribbon.

GALLERIES AND LIVE PREVIEW

A gallery is a large collection of tools that look like the choice they represent. If you've used previous versions of Excel, you may have noticed that the font names in the drop- down box on the Formatting toolbar were in their own font. Galleries are an extension of that feature. The Styles gallery, for example, does not just list the name of the style but lists it in the same formatting that will be applied the cell.

Although galleries help to give you an idea of what your object will look like when an option is selected, Live Preview takes it to the next level. Live Preview displays your object or data as it will look right on the worksheet when you hover over the gallery tool. By hovering over the various tools in the Format Table gallery, you can see exactly what your table will look like before you commit to a format.

The Office Button Menu

Although menus are a thing of the past in Excel 2007, one holdout remains: the Office Button menu. In the upper-left corner of the Excel window is a round Microsoft Office logo that is known as the Office Button. Click it, and you see a traditional menu with menu commands (see Figure 1-3). Saving workbooks, opening workbooks, and printing are a few of the commands available.

Figure 1-3: Clicking the Office Button displays a menu, and some of the menu items have submenus.

The Office Button menu also contains the list of recent documents, just as it had in previous versions. The recent documents list has undergone a major overhaul, however; the maximum number of documents on the list is now 50 from the previous of 9, with push-pin icons next to each entry that can be used to hold that document in its place on the list regardless of how many files you open and close.

At the bottom of the Office Button menu is the Excel Options button. This button opens the Excel Options dialog box, which contains 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 many items displays the shortcut menu as well as a Mini Toolbar. The Mini Toolbar is floating toolbar that contains a dozen or so of the most popular formatting commands. Figure 1-4 shows the shortcut menu and Mini Toolbar when a range is selected.

Figure 1-4: The shortcut menu and Mini Toolbar appears when you right-click a range.

The Quick Access Toolbar

The Quick Access toolbar (or QAT) is a set of tools that the user can customize. By default, the QAT 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 QAT from the Customization tab of the Excel Options dialog box. To access this dialog box, right-click the QAT and choose Customize Quick Access Toolbar.

Smart Tags

A Smart Tag is a small icon that appears automatically in your worksheet after you complete certain actions. Clicking a Smart Tag reveals several clickable options.

For example, if you copy and paste a range of cells, Excel generates a Smart Tag that appears below the pasted range (see Figure 1-5). Excel features several other Smart Tags, and additional Smart Tags can be provided by third-party providers.

Figure 1-5: This Smart Tag appears when you paste a copied range.

Task Pane

Excel 2002 introduced the task pane. This is a multipurpose user interface element that is normally docked on the right side of Excel's window (but you can drag it anywhere you like). The task pane is used for a variety of purposes, including displaying the Office Clipboard, providing research assistance, displaying pivot table fields, and mapping XML data. Figure 1-6 shows the task pane that appears when you insert clip art.

Figure 1-6: The Clip Art task pane allows you to search for and insert an image.

Drag and Drop

Excel's drag-and-drop UI feature enables you to freely drag objects that reside on the drawing layer to change their position. Pressing Ctrl while dragging duplicates the selected objects. These objects include shapes, embedded charts, and SmartArt.

Excel also permits drag-and-drop actions on cells and ranges. You can easily drag a cell or range to a different position. And pressing Ctrl while dragging copies the selected range.

Note 

You can disable the ability to drag and drop cells. To change this setting, choose Office Excel Options to display the Excel Options dialog box. Click the Advanced tab and clear the Enable Fill Handle and Cell Drag-And-Drop check box (located in the Editing Options section).

Keyboard Shortcuts

In addition to the keyboard shortcuts for navigating the Ribbon, Excel has many other keyboard shortcuts that execute commands directly. For example, you can press Ctrl+C to copy a selection. If you're a newcomer to Excel or if you just want to improve your efficiency, do yourself a favor and check out the shortcuts listed in Excel's Help system. (Search for Excel Shortcut and Function Keys using the Search box or locate the topic under the Accessibility chapter of Help's table of contents.) The Help system contains tables that summarize useful keyboard commands and shortcuts.

To ease the transition from previous versions to Excel 2007, Microsoft includes the Office 2003 Access Keys feature. Many Excel users are accustomed to navigating the old menu system with their keyboard, and they would become much more inefficient if they had to rely on the new Ribbon. If you type an Alt+letter sequence that isn't a part of the Ribbon but that did exist in Excel 2003, you get a ScreenTip near the top of the Excel window, like the one shown in Figure 1-7.

Figure 1-7: Using a keyboard sequence like Alt+I+R (for Insert Row) can still be used to insert a row and will display this ScreenTip during the process.

Customized Onscreen Display

Excel offers some flexibility regarding onscreen display (status bar, formula bar, the Ribbon, and so on). For example, by choosing View Workbook Views Full Screen, you can get rid of everything except the title bar, thereby maximizing the amount of visible information. To get out of full-screen mode, right-click and choose Exit Fullscreen from the shortcut menu (or press Esc). A little less drastic is pressing the Ctrl+F1 shortcut key to hide (and restore) the Ribbon.

The status bar has also been enhanced in Excel 2007. Right-click the status bar, and you see lots of options that allow you to control what information is displayed in the status bar.

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

Data Entry

Data entry in Excel is quite straightforward. Excel interprets each cell entry as one of the following:

• A value (including a date or a time)

• Text

• A Boolean value (TRUE or FALSE)

• A formula

Note 

Formulas always begin with an equal sign (=).

Object and Cell Selecting

Generally, selecting objects in Excel conforms to standard Windows practices. You can select a range of cells by using the keyboard (using the Shift key, along with the arrow keys) or by clicking and dragging the mouse. To select a large range, click a cell at any corner of the range, scroll to the opposite corner of the range, and press Shift while you click the opposite corner cell.

You can use Ctrl+* (asterisk) to select an entire table. And when a large range is selected, you can use Ctrl+. (period) to move among the four corners of the range.

Data-Entry Tips

The following list of data-entry tips can help those moving up to Excel from another spreadsheet:

• To enter data without pressing the arrow keys, enable the After Pressing Enter, Move Selection option on the Advanced tab of the Excel Options dialog box (which you access from the Office Excel Options command). You can also choose the direction that you want to go.

• You may find it helpful to select a range of cells before entering data. If you do so, you can use the Tab key or Enter key to move only within the selected cells.

• To enter the same data in all cells within a range, select the range, enter the information into the active cell, and then press Ctrl+Enter.

• To copy the contents of the active cell to all other cells in a selected range, press F2 and then Ctrl+Enter.

• To fill a range with increments of a single value, press Ctrl while you drag the fill handle at the lower-right corner of the cell.

• To create a custom AutoFill list, select the Edit Custom Lists button on the Popular tab of the Excel Options dialog box.

• To copy a cell without incrementing, drag the fill handle at the lower-right corner of the selection; or, press Ctrl+D to copy down or Ctrl+R to copy to the right.

• To make text easier to read, you can enter line breaks in a cell. To enter a line break, press Alt+Enter. Line breaks cause a cell's contents to wrap within the cell.

• To enter a fraction, enter 0, a space, and then the fraction (using a slash). Excel formats the cell using the Fraction number format.

• To automatically format a cell with the currency format, type your currency symbol before the value.

• To enter a value in percent format, type a percent sign after the value. You can also include your local thousand separator symbol to separate thousands (for example, 123,434).

• To insert the current date, press Ctrl+; (semicolon). To enter the current time into a cell, press Ctrl+Shift+;.

• To set up a cell or range so that it accepts entries only of a certain type (or within a certain value range), use the Data Data Tools Data Validation command.

If you're working in a table (created with the Insert Tables Table command), you'll find that Ctrl+A works in a new way. Press it once to select the table cells only. Press Ctrl+A a second time, and it select the entire table (including the header and totals row). Press it a third time, and it selects all cells on the worksheet.

Clicking an object placed on the drawing layer selects the object. An exception occurs if the object has a macro assigned to it. In such a case, clicking the object executes the macro. To select multiple objects or noncontiguous cells, press Ctrl while you select the objects or cells.