2. Using the Excel Interface
Using the Ribbon
The mantra of the ribbon is to use pictures and words. Many people noticed the little whisk broom icon in previous versions of Excel but never knew what it did. In Excel 2016, 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
Another element in the ribbon is the gallery control. Galleries are used when there are dozens of options from which you can 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. (For an example, open the Cell Styles gallery on the Excel 2016 Home tab.) 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.
If you click the open control at the bottom-right side of the gallery, the gallery opens to reveal all choices at once.
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 2.1 shows details of the Number group of the Home tab. In the lower-right corner of the group is the dialog launcher. It looks like the top-left corner of a dialog, with an arrow pointing downward and to the right.
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 2.2, you see the Number tab of the Format Cells dialog.
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 many more choices than those available in the ribbon.
Resizing Excel Changes the Ribbon
The ribbon modifies 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 2.3 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 2.4 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.
As you resize the Excel screen to a smaller width, more items collapse. Soon, the three icons for Insert, Delete, and Format are collapsed into a single drop-down called Cells. Eventually, the Excel ribbon gets too small, and Excel hides it completely.
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:
1. Right-click the ribbon and choose Customize the Ribbon. Excel displays the Customize Ribbon category of the Excel Options dialog.
2. 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.
3. 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 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 clip art or pictures, so there’s 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.
The 20 contextual tabs are identified in Figure 2.5.
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 cannot find an obscure command that you know is somewhere in Excel.
Microsoft introduced a new Tell Me What You Want to Do search box on the Office 2016 ribbon. If you remember that the command you need is called Validation, type Validation into the search box. The search results take you directly to the feature, as shown in Figure 2.6. This feature works okay if you know the name of the command that you are searching for. If you try to answer the Tell Me What You Want to Do box with an English language phrase, such as “Choose from a list,” the search box has little chance of returning the command (see Figure 2.7). I’ve tested many phrases and never get the command that I am describing. When I reported this to the Excel team, they explained that I was expecting too much from this first-release feature.
The command also fails if you know the name of the command but the command falls into the dreaded Commands Not in Ribbon category (see Figure 2.8). Note that in this case, I had previously added Speak Cells to the Quick Access Toolbar (QAT). The search box is still clueless.
Here is my strategy for finding those commands that aren’t on the ribbon:
1. Right-click the ribbon and select Customize Quick Access Toolbar. Excel displays the Quick Access Toolbar category of the Excel Options dialog.
2. Open the top-left drop-down and change Popular Commands to All Commands. You now have an alphabetical list of more than 2,000 commands.
3. Scroll through this alphabetical list until you find the command you are trying to locate in the ribbon.
4. Hover over the command in the left list box. A ToolTip appears, showing you where you can find the command. If it says Command Not in Ribbon, click the Add button in the center of the screen to add the command to the Quick Access Toolbar.
Shrinking the Ribbon
The ribbon takes 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.
To shrink the ribbon, you can right-click it 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
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. You can also toggle between minimized and full size by double-clicking any ribbon tab.
Using the Quick Access Toolbar
A problem with the ribbon is that only one-seventh of the commands are visible at any given time. You will find yourself moving from one tab to another. The alternative is to use the Quick Access Toolbar (QAT) to store your favorite commands.
The QAT starts out as a tiny toolbar with Save, Undo, and Redo. It is initially located above the File tab in the ribbon.
If you start using the QAT frequently, you can right-click the toolbar and choose Show Quick Access Toolbar Below the Ribbon to move the QAT closer to the grid.
Adding Icons to the QAT
The drop-down at the right side of the QAT, shown on the right side in Figure 2.9, offers 12 popular commands you might choose to add to the Quick Access Toolbar. Choose a command from this list to add it to the QAT.
When you find a command in the ribbon you are likely to use often, you can easily add the command to the QAT. To do so, right-click any command in the ribbon and select Add to Quick Access Toolbar. Items added to the Quick Access Toolbar using the right-click method are added to the right side of the QAT.
The right-click method works for many commands, but not with individual items within commands. For example, you can put the Font Size drop-down on the QAT, but you cannot specifically put size 16 font in the QAT.
Removing Commands from the QAT
You can remove an icon from the QAT by right-clicking the icon and selecting Remove from Quick Access Toolbar.
Customizing the QAT
You can make minor changes to the QAT by using the context menus, but you can have far more control over the QAT if you use the Customize command. Right-click the QAT and select Customize Quick Access Toolbar to display the Quick Access Toolbar section of the Excel Options dialog, as shown in Figure 2.10.
The Excel Options dialog offers many features for customizing the Quick Access Toolbar:
You can choose to customize the QAT for all documents on your computer or just for the current workbook by using the top-right drop-down menu.
You can add separators between icons to group the icons logically. A separator icon is available at the top of the left menu. Click the separator icon in the left list box and then click the Add icon in the center of the screen.
You can resequence the order of the icons on the toolbar. Select an icon in the right list box, and then click the up/down arrow icons on the right side of the dialog.
You can access 2,000+ commands, including the commands from every tab and commands that are not available in the ribbon. Although the dialog starts with just 53 popular commands in the left list box, use the left drop-down to choose All Command or Commands Not in the Ribbon. When you find a command in the left list box, select the command and then click Add in the center of the dialog to add that command to the QAT.
You can reset the QAT to its original default state using the Reset button in the lower right.
You can export your custom QAT icons from your computer and import on another computer.
You can move the QAT to appear above or below the ribbon using the check box in the lower right.
Using the Full-Screen File Menu
Open the File menu to see the Backstage view introduced in Excel 2010. Here is the logic: When you are working on most ribbon tabs, you are working in your document. When you are about to change the font or something like that, you want to see the results of the change for the “in” commands. However, the Excel team thinks that after you move to the File menu, you are done working in your document and you are about to do something with the whole document, such as send the workbook, print the workbook, post the workbook to Twitter, and so on. Microsoft calls these the “out” commands. The theory is that you don’t need to see the worksheet for the “out” commands, so Microsoft fills the entire screen with the File menu.
To open the Backstage view, click the File menu. The Backstage view fills the screen, as shown in Figure 2.11. Backstage is split into three sections: the narrow left navigation panel and two wider sections that provide information.
The left navigation panel includes these commands:
Info—Provides information about the current workbook. This is discussed later in the “Getting Information About the Current Workbook” section.
New—Used to create a new workbook or start from a template.
Open—Used to access a file stored on your computer or the SkyDrive. See Chapter 1.
Save—Saves the file in the same folder as it was previously stored. Note that Save is a command instead of a panel in Backstage.
Save As—Stores the file on your computer or in OneDrive. See Chapter 1.
Print—Used to choose print settings and print. Includes Print Preview. See Chapter 27, “Printing.”
Share—Used to post your workbook to Facebook, Twitter, or LinkedIn or to send it via email. See Chapter 28, “Excel Online.”
Export—Used to create a PDF or change the file type.
Close—Closes the current workbook. Like Save, this entry is a pure command.
Account—Connects your copy of Excel to various social networking accounts.
Options—Contains pages of Excel settings. See Chapter 3, “Customizing Excel,” for details.
Recent File List—This list appears only if you’ve changed a default setting in Excel Options. Visit File, Options, Advanced Display and choose Quickly Access This Number of Recent Workbooks.
Pressing the Esc Key to Close Backstage View
To get out of Backstage and return to your worksheet, you can either press the Esc key or click the back arrow in the top-left corner of Backstage.
Recovering Unsaved Workbooks
As in previous versions of Excel, the AutoSave feature can create copies of your workbook every n minutes. If you close the workbook without saving, you might be able to get the file back, provided it was open long enough to go through an AutoSave.
If the workbook was new and never saved, scroll to the bottom of the Recent Workbooks List and choose Recover Unsaved Workbooks.
If the workbook had previously been saved, open the last saved version of the workbook. Go to the File menu, and the last AutoSave version from before you closed the file will be available.
Clearing the Recent Workbooks List
If you need to clear out the Recent Workbooks list, you should visit File, Options, Advanced, Display. Set the Show This Number of Recent Documents list to zero. You can then set it back to a positive number, such as 10.
Getting Information About the Current Workbook
When a workbook is open and you go to the File menu, you start in the Info gallery for that workbook. The Info pane lists all sorts of information about the current workbook:
The workbook path is shown at the top of the center panel.
You can see the file size.
You can see when the document was last modified and who modified it.
If any special states exist, these will be reported at the top of the middle pane. Special states might include the following:
Macros not enabled
Links not updated
Checked out from SharePoint
You can see if the file has been AutoSaved and recover those AutoSaved versions.
You can mark the document as final, which will cause others opening the file to initially have a read-only version of the file.
You can edit links to other documents.
You can add tags or categories to the file.
Using the Check for Issues drop-down, you can run a compatibility checker to see if the workbook is compatible with legacy versions of Excel. You can run an accessibility checker to see if any parts of the document will be difficult for people with disabilities. You can run a Document Inspector to see if any private information is hidden in the file.
Marking a Workbook as Final to Prevent Editing
Open the Protect Workbook icon in the Info gallery to access a setting called Mark as Final. This marks the workbook as read-only. It prevents someone else from making changes to your final workbook.
However, if the other person visits the Info gallery, that person can reenable editing. This feature is designed to warn the other people that you’ve marked it as final and no further changes should happen.
If you can convince everyone in your workgroup to sign up for a Windows Live ID, you can use the Restrict Permission by People setting. This layer of security enables you to define who can read, edit, and/or print the document.
Finding Hidden Content Using the Document Inspector
The Document Inspector can find a lot of hidden content, but it is not perfect. Still, finding 95% of the types of hidden content can protect you a lot of the time.
Caution
The Document Inspector is not foolproof. Do you frequently hide settings by changing the font color to white or by using the ;;; custom number format? These types of things won’t be found by the Document Inspector. The Document Inspector also won’t note that you scrolled over outside the print area and jotted your after-work grocery list in column X.
To run the Document Inspector, select File, Info, Check for Issues, Inspect Document, and click OK. The results of the Document Inspector show that the document has personal information stored in the file properties (author’s name) and perhaps a hidden worksheet.
Adding Whitespace Around Icons Using Touch Mode
If you are trying to use Excel on a tablet or a touch screen, you want to try touch mode. Follow these steps:
1. Go to the right side of the QAT and open the drop-down that appears there.
2. The twelfth command is called Touch/Mouse Mode. The icon is a blue dot with a ring of whitespace and then dashed lines around the whitespace. Choose this command to add it to the QAT.
3. Click the icon on the QAT. You see whitespace added around all the icons.
Previewing Paste Using the Paste Options Gallery
Here’s a quick survey: Have you ever opened a Notepad window, pasted your data to Notepad, copied from Notepad, and then pasted to your application? This is a great but tedious way to remove formatting from a selection. If you have discovered this painful workaround, you are going to love this feature that was added starting in Excel 2010: the Paste Options gallery.
Here is another survey: Suppose you have to copy a column of formulas and paste them as values. Do your fingers know how to do Ctrl+C, Alt+E+S+V+Enter? If so, you are going to love the new Ctrl+V, Ctrl, V keystrokes available in the Paste Options gallery. If you’ve ever done Ctrl+C, Alt+E+S+V+Enter, Alt+E+S+T+Enter, you will love the new Context+E keyboard shortcut.
As someone who uses both of those old keyboard shortcuts frequently, I love the Paste Options gallery. You can keep slicers, sparklines, even PowerPivot; the Paste Options gallery is the one feature that makes a difference in my life every single hour of every single workday.
Microsoft discovered that Paste was the number-one command that was immediately followed by Undo. To improve the Paste command, Microsoft added the Paste Options gallery in three places in Excel 2010. These galleries support Live Preview and keyboard shortcuts. They should make mouse-centric as well as keyboard-centric people very happy.
You encounter the gallery when you have something on the Clipboard and one of these three events happens:
You right-click a cell to access the context menu.
You open the Paste drop-down from the Home tab.
After you perform a typical Paste operation, the old Paste Repair menu icon appears with the tip that you can press Ctrl to access the gallery.
Accessing the Gallery After Performing a Paste Operation
Suppose that you copy a range with Ctrl+C and then paste with Ctrl+V. The icon for the old Paste Repair appears next to the paste, but this time it notes that you can open the menu by pressing Ctrl. When you press Ctrl, you are presented with a gallery of paste options.
The options available in the gallery are as follows:
Paste—This is the standard paste that you would get using Ctrl+V.
Formulas—Pastes only formulas, with no formatting. This is common when you are copying down from the first row of a table that has an outline border. To prevent the top border from copying, you can paste formulas. You then find that you have to reapply the number formatting.
Formulas & Number Formatting—Copies formulas as previous formulas, along with the number formatting.
Keep Source Formatting—This is particularly useful when copying from another application such as a web page. The formatting from the other application is pasted along with the values.
No Borders—Pastes everything but the borders.
Column Widths—Includes the column widths from the copied area.
Transpose—Turns the data on its side. A 12-row by 1-column copied range would paste as 1 row by 12 columns.
Values—Converts formulas to values.
Values and Number Formatting—Converts the formulas to values and includes the number formats from the copied data.
Values & Source Formatting—Converts the formulas to values and includes all formatting, such as cell styles, font color, number formatting, and borders.
Formats—Does not bring any values, only the cell formatting. Similar to using the Format Painter.
Paste Link—Creates formulas here that point back to the copied range.
Paste as Picture—Pastes a picture of the original cells in this location.
Paste as Linked Picture—Pastes a live picture of the original cell in this location. This is the elusive Camera tool from Excel 2003.
Open Paste Special—Used to access the old Paste Special dialog. The Paste Special dialog still offers some choices not available in the Paste Options gallery: Comments, Validation, All Using Source Theme, Add, Subtract, Multiply, Divide, and Skip Blanks.
Accessing the Paste Options Gallery from the Right-Click Menu
The Paste Options gallery appears in the right-click context menu and includes Live Preview. The top six options appear directly in the menu. A fly-out menu offers all 14 options.
As you start to hover over the various paste icons, Live Preview takes over. The rest of the context menu disappears so that you can see the worksheet. Hover over Transpose and you get a preview of what Transpose actually does. Hover over Formatting and you see that the Formatting option copies only the cell formats and not the numbers. If you hover over Paste Special and then move out to the full gallery, all the context menu except the full gallery disappears, and Live Preview continues to work.
Why Keyboard-Centric People Like the Context Gallery
P—Normal Paste
V—Paste Values
F—Formulas
T—Transpose
R—Formats
N—Paste Link
The Paste Options gallery also appears when you open the Paste drop-down on the Home tab.
Using the New Sheet Icon to Add Worksheets
The Insert Worksheet icon is a circle with a plus sign that appears to the right of the last sheet tab.
When you click this icon, a new worksheet is added to the right of the active sheet. This is better than Excel 2010, where the new worksheet was added as the last worksheet in the workbook and then had to be dragged to the correct position.
Navigating Through Many Worksheets Using the Controls in the Lower Left
Older versions of Excel had four controls for moving through the list of worksheet tabs. The worksheet navigation icons are now a left and right arrowhead in the lower left.
The controls are active only when you have more tabs than are visible across the bottom of the Excel window. Click the left or right icon to scroll the tabs one at a time. Ctrl+click either arrow to scroll to the last tab. Note that scrolling the tabs does not change the active sheet. It just brings more tabs into view so you can then click the selected tab.
Just as in prior versions of Excel, you can right-click the worksheet navigation arrows to see a complete list of worksheets. Click any item in the list to move to that worksheet.
In certain circumstances, an ellipsis (...) icon appears to the left of the worksheet navigation arrows. This icon selects the worksheet to the left of the active sheet.
Using the Mini Toolbar to Format Selected Text
When you select some text in a chart title or within a cell, the mini toolbar appears above the selected text. If you move away from the mini toolbar, it fades away. However, if you move the mouse toward the mini toolbar, you see several text formatting options.
To use the mini toolbar, follow these steps:
1. Select some text. If you select text in a cell, you must select a portion of the text in the cell by using Cell Edit mode. In a chart, SmartArt diagram, or text box, you can select any text. As soon as you release the mouse button, the mini toolbar appears above and to the right of the selection.
2. Move the mouse pointer toward the mini toolbar. The mini toolbar stays visible if your mouse is above it. If you move the mouse away from the mini toolbar, it fades away.
3. Make changes in the mini toolbar to affect the text you selected in step 1.
4. When you are done formatting the selected text, you can move the mouse away from the mini toolbar to dismiss it.
Expanding the Formula Bar
Formulas range from very simple to very complex. As people started writing longer and longer formulas in Excel, an annoying problem began to appear: If the formula for a selected cell was longer than the formula bar, the formula bar would wrap and extend over the worksheet. In many cases, the formula would obscure the first few rows of the worksheet. This was frustrating, especially if the selected cell was in the top few rows of the spreadsheet.
Excel 2016 includes a formula bar that prevents the formula from obscuring the spreadsheet. For example, in Figure 2.12, cell F1 contains a formula that is longer than the formula bar. Notice the down-arrow icon at the right end of the formula bar. This icon expands the formula bar.
Press Ctrl+Shift+U or click the down-arrow icon at the right side of the formula bar to expand the formula bar. The formula bar expands, but the entire worksheet moves down to accommodate the larger formula bar.
Note
Excel MVP Bob Umlas keeps suggesting that the formula bar should change color when you are not seeing the entire formula. That is a great suggestion that perhaps the Excel team will one day add to Excel.
The formula in this example is too long for the default larger formula bar. You have to hover your mouse near the bottom of the formula bar until you see the up/down white arrow cursor. Click and drag down until you can see the entire formula (see Figure 2.13).
Zooming In and Out on a Worksheet
In the lower-right corner of the Excel window, a zoom slider enables you to zoom from 400% to 10% with lightning speed. You simply drag the slider to the right to zoom in and to the left to zoom out. The Zoom Out and Zoom In buttons on either end of the slider enable you to adjust the zoom in 10% increments.
Clicking the % indicator to the right of the zoom slider opens the legacy Zoom dialog.
Using the Status Bar to Add Numbers
If you select several cells that contain numeric data and then look at the status bar, at the bottom of the Excel window, you can see that the status bar reports the average, count, and sum of the selected cells (see Figure 2.14).
If you need to quickly add the contents of several cells, you can select the cells and look for the total in the status bar. This feature has been in Excel for a decade, yet very few people realized it was there. In legacy versions of Excel, only the sum would appear, but you could right-click the sum to see other values, such as the average, count, minimum, and maximum.
You can customize which statistics are shown in the status bar. Right-click the status bar and choose any or all of Min, Max, Numerical Count, Count, Sum, and Average.
Note that the panel might show values for items that you have recently unselected. These figures will be wrong if the selection has changed.
Switching Between Normal View, Page Break Preview, and Page Layout View Modes
Three shortcut icons to the left of the zoom slider enable you to quickly switch between three view modes:
Normal view—This mode shows worksheet cells as normal.
Page Break preview—This mode draws the page breaks in blue. You can actually drag the page breaks to new locations in Page Break preview. This mode has been available in several versions of Excel.
Page Layout view—This view was introduced in Excel 2007. It combines the best of Page Break preview and Print Preview modes.
In Page Layout view mode, each page is shown, along with the margins, header, and footer. A ruler appears above the pages and to the left of the pages. You can make changes in this mode in the following ways:
To change the margins, drag the gray boxes in the ruler.
To change column widths, drag the borders of the column headers.
To add a header, select Click to Add Header.