Using the Excel 2013 Interface
- Using the Ribbon
- Using the Quick Access Toolbar
- Using the Full-Screen File Menu
- Using Other Excel Interface Improvements
- Using the New Sheet Icon to Add Worksheets
- Navigating Through Many Worksheets Using the Controls in the Lower Left
- Using the Mini Toolbar to Format Selected Text
- Expanding the Formula Bar
- Zooming In and Out on a Worksheet
- Using the Status Bar to Add Numbers
- Switching Between Normal View, Page Break Preview, and Page Layout View Modes
This chapter is from the book
This chapter is from the book
This chapter is from the book
The Excel interface has had a major makeover over the last decade. Excel 2007 introduced the ribbon and Quick Access Toolbar to replace the familiar command bar at the top of Excel. Excel 2010 brought the expanded File menu. Excel 2013 brings the Start menu, plus new Open and Save As panes. These were discussed in Chapter 1, “Staying Connected Using Excel 2013.” This chapter reviews all of the remaining Excel interface elements.
Using the Ribbon
The ribbon is composed of seven permanent tabs labeled Home, Insert, Page Layout, Formulas, Data, Review, and View. Other permanent tabs appear if you install certain add-ins. For example, PowerPivot, Inquire, and Easy-XL are tabs that you see if you install certain add-ins. Other contextual ribbon tabs appear when you select a certain type of object, such as a chart, image, or pivot table.
Each tab is broken into rectangular groups of related commands. The group shown in Figure 3.1 is the Clipboard group on the Home tab.
){kind=link}
Figure 3.1. Detail of the Clipboard group of the Home tab of the ribbon.
The mantra of the ribbon is to use pictures and words. Many people have seen the little whisk broom icon in previous versions of Excel but never knew what it did. In Excel 2013, the same icon has the words “Format Painter” next to it. When you hover, the tooltip offers paragraphs explaining what the tool does. The tooltip also offers a little-known trick: You can double-click the Format Painter to copy the formatting to many places.
Using Fly-out Menus and Galleries
The ribbon fits more commands in a smaller space by using new types of controls that were not available with the Excel 2003 menu bar and toolbars.
In Figure 3.1, the Cut icon is a pure command. You click the icon and Excel cuts the selection onto the Clipboard. In contrast, the Paste and Copy icons are a new type of element comprised of a button and a drop-down. If you click the top half of Paste or the left side of Copy, you invoke the command. But if you click the arrow in either icon, you get a fly-out menu with more choices.
Fly-out menus allow many choices from a single icon. For example, the Conditional Formatting icon on the Home tab takes up a 76×76 pixel area on the ribbon. Clicking Conditional Formatting leads to five new fly-out menus and three commands. In all, the fly-out menus lead to a total of 64 distinct commands, all driven from a single 76×76-pixel icon (see Figure 3.2).
)
Figure 3.2. Fly-out menus offer dozens of choices from a single icon.
Another new element in the ribbon is the gallery control. Galleries are used when there are dozens of options from which to choose. The gallery shows you a visual thumbnail of each choice. A gallery starts out showing a row or two of choices in the ribbon. The right side of the gallery offers icons for up, down, and open. If you click up or down, you scroll one row at a time through the choices (see Figure 3.3).
)
Figure 3.3. Use the up and down arrows on the right side of the gallery to move one row at a time through the choices.
If you click the open control at the bottom-right side of the gallery, the gallery opens to reveal all choices at once (see Figure 3.4).
)
Figure 3.4. If you open the gallery control, you can see all of the choices at one time.
Rolling Through the Ribbon Tabs
With Excel as the active application, move the mouse anywhere over the ribbon and roll the scroll wheel on top of the mouse. Excel quickly flips from ribbon tab to ribbon tab. Scroll away from you to roll toward the Home tab on the left. Scroll toward you to move to the right.
Revealing More Commands Using Dialog Launchers, Task Panes, and “More” Commands
The ribbon holds perhaps 20% of the available commands. The set of commands and options available in the ribbon will be enough 80% of the time, but you will sometimes have to go beyond the commands in the ribbon. You can do this with dialog launchers, “More” commands, and the task pane.
A dialog launcher is a special symbol in the lower-right corner of many ribbon groups. Click the dialog launcher to open a related dialog with many more choices than those offered in the ribbon.
Figure 3.5 shows details of the Number group of the Home tab. In the lower-right corner is the dialog launcher. It looks like the top-left corner of a dialog, with an arrow pointing downward and to the right.
)
Figure 3.5. The dialog launcher takes you to additional options.
When you click the dialog launcher, you go to a dialog box that often offers many more choices than those available in the ribbon. In Figure 3.6, you see the Number tab of the Format Cells dialog.
)
Figure 3.6. After clicking the dialog launcher, you get access to far more choices.
Many menus in the ribbon end with an entry for “More blank...” or “Blank Options...”. You will see menu options for More Rules..., Effects Options..., and so on. Look for these menu items as the last entry in many menus. Clicking a More item takes you to a dialog or task pane with far more choices than those available in the ribbon.
Figure 3.7 shows the More Rules menu item, which leads to the New Formatting Rule dialog.
)
Figure 3.7. Choosing More Rules leads to a dialog with more choices.
Charts in Excel 2013 show a plus icon to the right of the selected chart. This icon leads to fly-out menus that eventually lead to a “More...” menu item (see Figure 3.8). When you select More... from a chart, you go to a redesigned task pane. Task panes were popular in Excel 2003. They were nearly banished in Excel 2007 due to the edict that all commands must be at the top. However, they are back with a vengeance in Excel 2013.
)
Figure 3.8. Choosing More... from the chart’s plus icon leads to a task pane.
Task panes typically appear on the right side of the Excel window, although you can undock them and have them float above the worksheet. After a task pane appears, it typically stays visible until you close it by clicking the X button. Figure 3.9 shows a task pane for formatting the chart title. If you click a data label in the chart, the task pane changes to show options for chart labels. If you click a picture, SmartArt, or WordArt, the task pane changes to show options for the newly selected object.
){kind=link}
Figure 3.9. The Format dialog from Excel 2010 is now a task pane.
CAUTION
If you click away from an object and choose only a cell, the task pane stays visible with the choices for the last selected object. However, all of the choices are grayed out. This seems like a bug to me.
TIP
To display the task pane for an object, press Ctrl+1. Although it is easy to display the task pane for a chart, it is not obvious how to display the task pane for an image or WordArt. However, as noted earlier, you can accidentally find your way to the Picture task pane by clicking a picture while the task pane is active. When you want to display a task pane but cannot figure out how to do it, try F1.
For charts, the task pane in Excel 2013 replaces the Format dialog from Excel 2010. It seems more confusing to use the task pane.
In Excel 2010, the Format dialog had a list of categories down the left side. These categories were arranged into groups with a horizontal separator between categories. For example, in Figure 3.10, the first group of categories is Fill, Border Color, and Border Styles. In a glance, you could see other categories such as Glow and Soft Edges, Properties, and Alt Text. I appreciated seeing a long list of words to choose from.
)
Figure 3.10. The Format dialog from Excel 2010 offered easy-to-understand words in the left navigation pane.
The Excel 2013 task pane offers a hierarchy of words, icons, and more words. In Figure 3.11, the equivalent task pane starts with two sets of words at the top: Chart Options and Text Options. It won’t be obvious in the figure, but Chart Options is shown in green and is the currently selected choice. When Chart Options is selected, you have three icons: a paint bucket, a pentagon with a reflection, and a square that someone appears to be measuring. Again, it is not obvious from the figure, but the paint bucket is in green and selected. When the paint bucket is selected, two word choices appear in the task pane: Fill and Border. Click the triangle next to either word to show all of the choices for that word. Figure 3.12 shows the Border category after expanding.
){kind=link}
)
Figure 3.11. The task pane offers a three-level menu of words, icons, and then more words.
Figure 3.12. After choosing Chart Options, the paint bucket, and then Border, you get these options.
The task pane is confusing because you can’t see all of the categories at one time. You will find yourself trying to guess which Level 1 word to choose and then clicking through each of the Level 2 icons trying to find the Level 3 category you want.
Figure 3.13 shows the name of each Level 2 icon and the types of categories you might find there.
)
Figure 3.13. Icons you might find in various task panes.
Resizing Excel Changes the Ribbon
The ribbon appears different as the size of the Excel application window changes. You should be aware of this when you are coaching a co-worker over the phone. You might be looking at your screen and telling him to “look for the big Insert drop-down to the right of the orange word Calculation.” Although this makes perfect sense on your widescreen monitor, it might not make sense on his monitor. Figure 3.14 shows some detail of the Home tab on a widescreen monitor. The Cell Styles gallery shows 10 thumbnails, and Insert, Delete, and Format appear side-by-side.
)
Figure 3.14. On a widescreen monitor, you see 10 choices in the Cell Styles gallery.
Figure 3.15 shows the typical view on a laptop. The Cell Styles gallery is collapsed to a single drop-down. The Insert, Delete, and Format icons are now arranged vertically.
)
Figure 3.15. On a normal monitor, the cell styles gallery is collapsed.
As you resize the Excel screen, more items collapse. In Figure 3.16, the three icons for Insert, Delete, and Format are collapsed into a single drop-down called Cells.
){kind=link}
Figure 3.16. At small sizes, icons collapse into drop-downs.
New in Excel 2013, you can even coax a new right-arrow icon to scroll the ribbon right (see Figure 3.17).
){kind=link}
Figure 3.17. Click the right-arrow icon to scroll the ribbon to the right.
Eventually, the Excel ribbon gets too small, and Excel hides the ribbon completely (see Figure 3.18).
){kind=link}
Figure 3.18. When the Excel windows gets too narrow, the ribbon is hidden.
Activating the Developer Tab
If you regularly record or write macros, you might be looking for the VBA tools in the ribbon. They are all located on the Developer tab, which is hidden by default. However, it is easy to make the Developer tab visible. Follow these steps:
- Right-click the ribbon and choose Customize the Ribbon. Excel displays the Customize Ribbon category of the Excel Options dialog.
- A long list box of ribbon tabs is shown on the right side of the screen. Every one of them is checked except for Developer. Check the box next to Developer.
- Click OK. The Developer tab displays.
Activating Contextual Ribbon Tabs
The ribbon tabs you see all the time are called the main tabs. Another 18 tabs come and go, depending on what is selected in Excel.
For example, Excel offers a whole series of commands for dealing with photographs that you insert into your worksheet. However, 90% of people never bother to dress up their worksheets with clipart or pictures, so there’s really no reason to show all the commands for working with photographs on the ribbon. However, after you insert a picture and the picture is selected, the Picture Tools, Format tab appears in the ribbon (see Figure 3.19).
){kind=link}
Figure 3.19. Anywhere from one to three contextual ribbon tabs display when you activate certain objects.
The 18 contextual tabs are identified in Figure 3.20.
)
Figure 3.20. This table shows which tabs appear and when.
Here is the frustrating thing: As soon as you click outside of the object (that is, the picture), it is no longer selected and the Picture Tools Format tab disappears.
If you need to format an object and you cannot find the icons for formatting it, try clicking the object to see if the contextual tabs appear.
Two other tabs occasionally appear, although Excel classifies them as main tabs instead of contextual tabs. If you add the Print Preview Full Screen icon to the interface, you arrive at a Print Preview tab. Also, from the Picture Tools Design tab, you can click Remove Background to end up at the Background Removal tab.
Finding Lost Commands on the Ribbon
Often, the command you need is front and center on the Home tab and everything is fine. However, there are times when you simply cannot find an obscure command that you know used to be in Excel 2003. Here is my strategy for finding those commands:
- Right-click the ribbon and select Customize Quick Access Toolbar. Excel displays the Quick Access Toolbar category of the Excel Options dialog.
- Open the top-left drop-down and change Popular Commands to All Commands. You now have an alphabetical list of over 2,000 commands.
- Scroll through this alphabetical list until you find the command you are trying to locate in the ribbon.
- Hover over the command in the left list box. A tooltip appears, showing you where you can find the command. In Figure 3.21, the Justify command is located on the Home tab, in the Editing group, under the Fill drop-down.
)
Figure 3.21. This tooltip shows you where to find a command.
Microsoft offers a free interactive ribbon guide that can help you locate an Excel 2003 menu command on the ribbon. Type Interactive Ribbon Guide in any search engine to find the latest incarnation of the ribbon guide. I’ve seen the guide and tried it out once, but given that the tooltips are built into the Customize Quick Access Toolbar panel of Excel Options, it seems pointless to go out to the Web when you can find the answer quickly using the steps just outlined.
NOTE
Sometimes, a command truly is not in the ribbon. If you hover over a command in the Customize dialog and the tooltip indicates that this is a command that is not in the ribbon, you have to use Customize to add this command to the Quick Access Toolbar or to the ribbon.
Shrinking the Ribbon
The ribbon does take up four vertical rows of space. This won’t be an issue on a big monitor, but it could be an issue on a tiny netbook.
Starting in Excel 2013, to shrink the ribbon, you can right-click the ribbon and choose Collapse the Ribbon. The ribbon collapses to show only the ribbon tabs. When you click a tab, the ribbon temporarily expands. To close the ribbon, choose a command or press Esc.
TIP
Starting in Excel 2013, the ribbon often stays open after certain commands. For example, I frequently click the Increase Decimal icon three times in a row. When the ribbon is minimized, you can click Home and then click Increase Decimal three times without having the ribbon close.
To permanently bring the ribbon back to full size, right-click a ribbon tab and uncheck Collapse the Ribbon.
Note that you can also minimize the ribbon using the carat (^) icon at the bottom right of the expanded ribbon. To expand the ribbon, click any tab and then click the pushpin icon in the lower-right corner of the ribbon.