14 Essential Microsoft Excel 2010 Tips for Intermediate Users

($389 street, 4 stars) version of the program. The 14 tips and how-to items included in this article reveal a few of the new features while also pointing out how Excel users can make the most of them in the right way. And some of these tips cover essential intermediate-level skills that you should know no matter what version of Excel you use.

This list of tips and tricks is for "intermediate" Microsoft Excel users because they assume some basic understanding of how Excel works. However, it contains a lot of solid information for advanced users who are still adjusting to the changes in Office 2010.

For example, we'll explain the difference between "count" and "numerical count," how to convert the result of a function to plain text, and how to autofill a row or column with a list of names. We also have a bunch of tips for taming "the ribbon"—the new user interface in Microsoft Office 2010 that stands in for the old menus and toolbars.

For more tips and tricks, whether for advanced or beginner users of Excel, or those mucking around in other Microsoft Office 2010 programs, including Word and Outlook, see a list of links at the end of this article. Also see our review of ($99 street, 4.5 stars). You can either read our tips in the below or page through them in the Table of Contents.—

 

 

 

 

 

 

 

Open the Office Window on Two Different Views

Here's a tip that applies to both Word and Excel in slightly different ways. By splitting the document window into two separate panes you can view and edit two widely-separated parts of a document at the same time. That means you can work on the first chapter of your novel in the top pane and the last chapter in the bottom pane, and jump between them simply by clicking the mouse—or by tapping F6 to cycle between the two panes, the ribbon, and the status bar. Word even lets you change the zoom level in the two panes, as shown here, so that you can view multiple pages in one pane while viewing full-size text in the other. You can split the window by dragging the splitter bar at the very top of the vertical scroll bar, or open the View tab on the Ribbon and select Split (or Remove Split to restore a single window). Excel has a similar split-window feature but unfortunately doesn't let you choose different zoom levels in each pane.—

Get More Information from Excel's Status Bar

It's easy to ignore the wealth of data that Office apps provide at a glance on the status bar. By default, Excel's status bar displays, among other things, the Average, Count, and Sum of the selected cells. But if you right-click on the status bar, a menu lets you customize the display. You can add, for example, the Minimum and Maximum of the currently selected items, or the "Numerical Count"—which means "the count of cells with numeric data," as opposed to "Count," which means "the count of cells that contain anything."—

Use a Named Range in a Formula in Excel

One major convenience of a named range is that you can use it a formula instead of a set of cell addresses. Press F2 to open the formula bar, start writing or editing your formula, and place the cursor where you would normally use cell addresses. On the Formula tab in the Ruler, click on Use in Formula and either select one range name from the dropdown menu or select multiple names from the Paste Name dialog box shown here.—

Convert an Excel Formula Result Into Static Text

Here's a simple tip that can save a lot of manual labor. I often want to keep the results of formulas or calculations, and I want to discard the original data that went into the results. For example, I often import columns of text data, then use the Excel's Concatenate function to combine two columns of data (for example, first and last names) into a third column (full names), and then I want to delete the now-redundant material in my original two columns. If I merely deleted the original two columns, the results in the new column would disappear with it, so I need to convert the new column into static text first. To do this for a single cell, select the column or cells that you want to convert to static text, press F2 to open the formula bar, press F9 to evaluate the formula, and the Enter key. To do this for multiple cells, select the cells you want to convert, press Ctrl-C to copy them to the clipboard, press Ctrl-V to paste, and, then, from the dropdown Paste menu, select the first icon under Paste Values to paste the result of the formula in place of the formula itself.—

An Animated Roadmap for the Ribbon Interface

Have you got the Excel 2003 menu system so wired into your fingers that you get lost in the Ribbon interface of Excel 2007 or 2010? Microsoft has a downloadable Flash-based animated guide you can use to find the Ribbon-interface equivalents of all the items on the 2003 menu. Go to http://office.microsoft.com/en-us/training/guides-to-the-ribbon-use-office-2003-menus-to-learn-the-office-2007-user-interface-HA010229584.aspx (that's all one address) and scroll down to the Interactive Guides. You can use the guides online or download them to your system, and they're available for all the major Office apps. Launch the guide, which displays the 2003 interface, and hover over any menu command. A tip tells you how to find the equivalent command on the new Ribbon interface.

Bonus tip: Many of the 2003 keystrokes still work in 2007 and 2010. For example, you can still press Alt-I, C to insert a column, just as in 2003.—

Automatically Fill a Row or Column with a Custom List of Names

Excel automatically fills a row with the names of months or weekdays when you type in the first few items in the row, then select the cells and drag the "fill handle" at the lower right corner of the selection to extend the series. If you have a series of words or names that you frequently enter by hand, create a custom autofill list. Click on File, then Options, and then Advanced. Scroll down almost to the foot of the Advanced menu. Under the General heading, click Edit Custom Lists..., and in the Customs List dialog, click New List and enter your list of words.—

Friends Don't Let Friends Enter Bad Data in a Worksheet

One guaranteed way to mess up a worksheet is to enter the wrong kind of data in a cell—for example, text instead of numbers, or a decimal instead of a whole number. Prevent yourself—or anyone else who uses your worksheets—from entering the wrong type of data by using Excel's Data Validation feature. In a table, select the cells that should only contain one kind of data. On the Table Tools tab, click Data Validation and specify the kind of data that can go into the cells. How do you alert the user who tries to enter the wrong data? Read on.—

...and Tell Your Friends Exactly What Kind of Data to Enter

The same dialog in which you specify the kind of data that can be entered has a tab labeled Error Alert. Fill in the title and message that you want to pop up when the wrong kind gets entered. You can also select the icon for the message by selecting the Style dropdown.—

Line Up Multiple Sheets in the Same Worksheet for Inspection

Here's the same slide as the one in the last tip, but with a box at the upper right illustrating yet another tip. How did I get two sheets from the same worksheet to appear on the same screen? Simple. On the View tab, I clicked New Window, then View Side by Side. By default, the Synchronous Scrolling option is turned on, so that you can scroll through both pages by dragging the slider bar in one of them. But you can turn off Synchronous Scrolling if you prefer to scroll through each sheet separately.—

Conditional Formatting with Negative Numbers in Excel

Excel's built-in conditional formatting gets new powers in Excel 2010. Now you can apply one of the pre-built color-coded conditional formatting options to data that includes negative numbers (not only positive numbers, as in Microsoft Office 2007). This can give you quick graphic clues to the way in which profits and losses, for example, fit into a pattern that's easier to detect graphically than by looking at a column of numbers. You set this up this kind of conditional formatting by clicking Home, Conditional Formatting, Data Bars, and then choosing a color set in the Gradient Fill gallery. The results are visible in the screen shot in column P.—

Display the Actual Cell Values When Creating or Editing a Formula

The previous tip shows how to display formulas in the entire spreadsheet. Here's how to switch between displaying the cell addresses in a formula and the actual values in each cell. Use any method that displays a formula—for example, when the formula of the current cell is visible in the formula bar, or when you're creating a formula for the first time, or after pressing Ctrl-tilde to display formulas throughout the worksheet. In the formula you want to find out about, select the cell addresses, and press F9. The highlighted addresses are replaced by the values of all the cells referenced in the formula. Press Esc to return to normal display. The screenshot above shows a formula that normally displays the address D12:O12, but when I selected that address and pressed F9, the actual values appeared.—

Highlight All Cells Referenced by a Formula

When you're debugging a worksheet, you can easily navigate through all the cells referenced in a formula. Highlight the cell and press Ctrl-[ (that's Ctrl-open-square-bracket). Excel highlights all the cells referenced by the formula, and moves the current selection to the first of the referenced cells. Press Enter, and the selection moves to the next referenced cell, and continue to press Enter to move though the rest of the referenced cells. In the screen shot, I was originally in cell D35 and pressed Ctrl-[. This highlighted D12, D26, and D35; and D12 became the current cell.—

Highlight the Formulas that Reference the Current Cell

The previous tip explained how to use Ctrl-[ (Ctrl-open-square-bracket) to see all the cells referenced by a formula. What if you want to do the reverse, and see the formulas that reference the a cell, select the cell, and press Ctrl-] (Ctrl-close-square-bracket). As in the previous tip, the selection moves to the the first formula that references the cell. Press Enter repeatedly to navigate to the other formulas that reference the cell. In the screen shot, I was originally in cell D3. I pressed Ctrl-]. This highlighted B3, D12, and D35, and B3 became the current cell.—

Tidy Up Your Charts

If you've ever created two or more charts on a worksheet, you know how tricky it can be to align them and make them all the same size. Here's the easy way. Click on the first chart to select it, then hold down the Ctrl key and click on the other charts that you want to align with each other. When all the charts you want to align are selected, right-click on any one of them and choose Size and Properties. This opens the Format Shape dialog, and the measurements that you enter in the dialogue will be applied to all the selected charts. After making the charts the same size, go to the Drawing Tools tab and click on Format. Use the Align dropdown menu on the ribbon to align the selected charts and to distribute them evenly either horizontally or vertically.