Excel Keyboard Shortcuts
Love your [Ctrl] key–for three reasons
This three-fer will demonstrate why you should love and use your [Ctrl] key:
Reason 1–Fast navigation. When you press [Ctrl] and any arrow key (north, east, south, or west), you jump to the last populated cell in that direction. Think of using the [Ctrl]-arrow key shortcut as an alternative to pressing [Page Down] to find the bottom row of a data set or pressing [Tab] to find the last column. Bonus tip: Hold down the [Shift] key while you press any [Ctrl]-arrow key shortcut to select all the cells between where you are and where you jump with the [Ctrl] key.
Reason 2–You can make noncontiguous selections. That’s a fancy way of saying you can select any cells you want, regardless of whether they’re contiguous–next to each other in a row or column. Hold down the [Ctrl] key while you click on a cell or click and drag through a range of cells. As long as you hold down the [Ctrl] key, you can click and select to your heart’s content.
Combine this tip with tip #7, and you can use the AutoCalculate tool to analyze any combination of individual cells or blocks of cells.
Reason 3–Fast data entry. Suppose you want to put the same string, number, or formula into two or more cells. Using the old-fashioned approach, you’d type the string, number, or formula into the first cell and then copy and paste that entry into the destination cells. But there’s a little-known time-saving tip that makes short work of placing the same entry in multiple cells. First, select all the cells you want to populate. Type the entry, but don’t press [Enter]. Instead, press [Ctrl][Enter]. When you do, Excel will copy what you typed into all of the selected cells.
Select All with one click
The next time you need to select an entire worksheet, click the little gray box in the top-left corner of the sheet.
- With the entire worksheet selected, you can copy it from one workbook (XLS file) and then paste it into a worksheet in a different workbook. Selecting the whole worksheet ensures you won’t accidentally miss something. Note: If you want to make a copy of a worksheet within the same book, just right-click on the worksheet tab, choose Move or Copy, then select the Create A Copy check box.
- With the entire worksheet selected, you can quickly and easily change the font in all cells or apply formatting to all cells.
- With the entire worksheet selected, you can double-click on any line separating two column letters or the line separating any two rows. Doing so tells Excel to adjust the width of the columns or the height of the rows to accommodate the data in the cells, which is very helpful if you’ve just shrunk (or enlarged) the font size of the text in your cells.
Copy the formatting (attributes) of one or more cells and apply them to another cell or range
Once you learn to use the Format Painter tool (which looks like a little yellow paintbrush on the Standard toolbar), you’ll wonder how you ever got by without it. To format a cell (or cells), select a cell (or cells) that are formatted the way you like and click Format Painter. Then, click and drag to apply that formatting to another cell (or range of cells).
Keep Column Names Visible as You Scroll
Many Worksheets have Headers for each column. As you scroll thru a worksheet it’s helpful to be able to see those names. Here’s how.
• Select the row immediately below the row you want to freeze.
• In the Window menu, click on Freeze Panes. Excel will put a thick line under the row to freeze.
• To unfreeze the row, go to Window menu again and click on Unfreeze Panes.
Drag and drop
Moving Cells with your Mouse
Moving cells with your mouse instead of key strokes can be convenient, here’s how.
•! Select the range of cells you want to move by highlighting the range with the mouse.
•! Position the mouse pointer over the heavy border that surrounds the selected range. The pointer should turn into an arrow(PC) or hand(mac).
•! Click and drag the range to a new location. As you move the mouse, the outline of the range moves.
•! When you are satisfied with the new location, release the mouse pointer. The cells are moved.
Selecting a Range of Cells
•! Selecting a long range of cells can be a pain. Here’s one trick that could help.
•! Move the cell pointer to the starting cell.
•! Press F8 to activate extend.
•! Either use your arrow keys or mouse to click on the ending row/column.
•! All cells will be highlighted.
•! Press Esc to end extend.
9. Name a range
Quickly create a named range of cells that you can return to later by selecting the cells you want to name and then clicking in the cell name bar directly to the left of the formula bar.
Enter the name you want to give to this range of cells and press [Enter]. You can now quickly return to this cell range by clicking the arrow to the right of the cell name bar and choosing the range that you previously saved. You can store several cell ranges.