Excel is, without a doubt, one of the most powerful, complex, and tricky desktop apps ever designed. Mastering Office 2010's spreadsheet app can take years; fortunately, we've got 10 very cool shortcuts to Excel mastery.
Our ongoing series of Tips for Microsoft Office 2010 has given little consideration to Excel 2010; that's because this giant, complex spreadsheet app is worth an entire collection of tips all to itself. With its cool new Sparklines displays and enhanced pivot table tools, Excel is a more powerful part of Office 2010 than ever. But our tips concentrate on the basics—you don't need to write complex formulas to take advantage of them, and, since basic functionality mostly hasn't changed, you can use most of our tips with older versions of the app, as well.
1. Display formulas instead of results
A single keystroke lets you toggle between Excel's normal display, which shows the results of the formulas in the spreadsheet, and a display mode that shows the actual formulas. The keystroke is Ctrl-tilde (tilde is this key: ~); press it once, and Excel displays formulas instead of results. Press it again, and the results appear again. This single keystroke is a lot quicker to use than the alternate method of displaying formulas, which is to open the File menu, go to Options, then Advanced; then scroll down to Display Options for this Worksheet and check the box next to "Show formulas in cells instead of their calculated results." Uncheck the box to display results again.
Bonus tip: when you use this tip to display formulas in cells, select a cell with a formula, and Excel outlines the cells that are referenced in the formula.
2. 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.
3. 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.
4. 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.
5. Add content or formatting to multiple sheets at once
You can add content or apply formatting to two or more of the sheets on a multisheet worksheet by "grouping" the sheets together. When you group multiple sheets, any content or formatting that you add to one sheet also gets added to all the other sheets, so you can add a row of headers to one sheet and have it automatically appear on all the sheets that are grouped together with it. To group all the sheets in a worksheet, right-click on any of the tabs in the lower left of the window, and click Select All Sheets. If you only want to select two or more individual sheets, hold down the Ctrl key and click on the tabs of the sheets that you want to format or edit at the same time. When two or more sheets are grouped, Excel adds the word "[Group]" (in square brackets) after the sheet's name in the title bar.
6. Be careful when working with grouped sheets
Grouping is a powerful but dangerous feature. If you delete the contents of a cell in one grouped sheet, the contents of the cells at the same location in all the other grouped sheets will also be deleted. So before you start editing in a worksheet with grouped sheets, right-click on one of the tabs at the lower-left and choose Ungroup Sheets.
7. The standard "Select All" key doesn't work the way you think it does
Experienced Windows users know that Ctrl-A is the shortcut key that selects everything in a window or document; the A in Ctrl-A stands for All. Ctrl-A works this way in every application you can find—except Excel. When you press Ctrl-A in a worksheet with data in it, you select the current region (that is, all connected cells), not the whole worksheet. But wait—if you immediately press Ctrl-A a second time, you select the entire worksheet—unless the worksheet contains a table, in which case your second press of Ctrl-A will select the current region and the summary rows (typically the headers) of the current table. And if your second Ctrl-A selects the current table and its summary rows, then you'll need to press Ctrl-A a third time to select the entire worksheet. In the screen shot, I pressed Ctrl-A once, and only the table got selected.
Bonus tip: the one-step way to select the entire worksheet is to the click on the gray box at the upper left corner of the worksheet—the one at the corner of the lettered columns and numbered rows.
8. Use the Ctrl-key for quick navigation
When you want to move quickly to the left, right, top, or bottom cell in a data set, just press Ctrl and one of the arrow keys. Let's say you want to select the cells in the current row of the data set, but only the cells with numbers, not the labels—for example, sales figures for January through December. If the current cell is in the middle of the row (for example, the cell with the sales figure for May) press Ctrl-Left to go to the first data cell in this set (the cell with the sales figure for January), then hold down the Shift key and press Ctrl-Right to select all the sales figures for January through December. In the screen shot, I started in cell G12, pressed Ctrl-left, then Shift-Ctrl-right to select twelve months of data.
9. The quickest way to see a sum or average
Type a few numbers in some adjacent cells, or highlight some numbers in existing cells. Now look down at the status bar at the foot of the window. Excel displays the average of the numbers, a count of the cells, and the sum. You can also use this trick with non-adjacent cells. Here's how: click on one cell, then hold down the Ctrl key and click on a cell with a number that you want to add to the number in the first cell. Continue to add numbers by Ctrl-clicking in additional cells. The average, count, and sum in the status bar get updated each time you click another cell. By the way, when you use this tip, Excel ignores any cells you click that contain text or graphics instead of numbers.
10. 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.
PC Mag
No comments:
Post a Comment