add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba
Time is Money Give Your Mouse
a Break
Is your time valuable? If you are not sure that it is, do not bother reading this chapter – keep using the Excel menus and icons to perform common operations. But if you do value your time, this chapter will attempt to convince you to use the keyboard instead.
This chapter will teach you how to move and maneuver quickly and efficiently between cells in a worksheet and between worksheets and workbooks. You will also learn to select cells and the data they contain quickly. In short, you will learn to save time (if you have decided that it is valuable). The example above illustrates why it is important to work more efficiently.
When you open a workbook in Excel, you are bombarded with information. You see a worksheet that contains data in every cell.
At first glance, you can’t tell what the current region is, if cells contain formulas, how to get to the end of a range of cells containing data, if there are totals beneath the columns, if there are comments in the cells that you ought to read to learn about the data in the worksheet, and more.
Cell, Cells, Move and Select
With scroll bars, you can control the part of the worksheet that appears in the window’s frame. In a worksheet, you can scroll vertically, from top to bottom, or horizontally, from side to side. However, using the scroll bars is slow, inefficient and, most of the time, annoying as well. It is even more annoying to use the mouse to select large ranges for copying or printing because the screen flickers incessantly.
The solution is simple--just put your hands on the keyboard. (Kick the habit of using the mouse and give it a break from time to time.) Learn to use keyboard commands!
Tip – Lock the scroll bars
Press Scroll Lock, and you will see that the letters SCRL appear in the status bar. Locking the scroll bars lets you use the navigation keys (the four arrow keys and/or Enter) the way you would use the wheel on the mouse.
Moving to the last cell in a range
Select cell A1 in your worksheet. See the figure on the next page.
To move vertically from top to bottom, press Ctrl+Down Arrow.
To move vertically from bottom to top, press Ctrl+Up Arrow.
To move horizontally from left to right, press Ctrl+Right Arrow.
To move horizontally from right to left, press Ctrl+Left Arrow.
Example: See figure below.
Move from cell A1 to the last cell in a range of cells that contains data (before an empty cell).
Select cell A1 and press Ctrl+Down Arrow. The result: You moved to cell A14, the last cell in a range that contains data. (Note: You can also use [End], and then Down Arrow, instead of Ctrl+Down Arrow.)
Continue and move to the next range of cells that contain data. Press Ctrl+Down Arrow again to move to cell A17. Press Ctrl+Down Arrow one more time to move to the last cell that contains data in the range that begins with cell A17, and so forth.
Selecting a horizontal or vertical range of adjacent cells
Add the Shift key to the combination of keys used above.
By pressing the Shift key along with Ctrl and one of the four arrow keys, you select a range of adjacent cells.
To select a vertical range of cells that contain data, from top to bottom, press Ctrl+Shift+Down Arrow.
To select a vertical range of cells that contain data, from bottom to top, press Ctrl+Shift+Up Arrow.
To select a horizontal range of cells that contain data, from left to right, press Ctrl+Shift+Right Arrow.
To select a horizontal range of cells that contain data, from right to left, press Ctrl+Shift+Left Arrow.
Example: Select a contiguous range from A1 to the end of the data range. In the example, this is A1 through A14. Select cell A1, and press Ctrl+Shift+Down Arrow.
To select the range A1 through D14, select cell A1, and press Ctrl+Shift+Down Arrow. Continue to hold down the Ctrl+Shift keys, and press Right Arrow.
Note
The cells in the range of A1 through A14 and the cells from A1 through D1 contain data. The continuity of data in the vertical cells in column A and in the horizontal cells in row 1 enable the selection of the contiguous range. Delete the data in cell A5, and try this technique again.
Selecting a range of non-adjacent cells
Select cell A1. Press Ctrl + Shift + Down Arrow. Continue holding down Ctrl, and use the mouse to select another range. Release the mouse button, and select another range while continuing to press Ctrl.
Selecting a contiguous or non-contiguous range of cells without pressing Ctrl or Shift
For contiguous selection, press F8. The letters EXT appear in the status bar. Pressing F8 extends the selected region. Select cell A1, and extend the selected region by pressing one of the arrow keys.
To toggle off the option of contiguous selection, press F8 again.
For non-contiguous selection, press Shift+F8. The letters ADD appear in the status bar. Select a number of non-contiguous ranges by using the mouse to select one range after another.
To toggle off the option of non-contiguous selection, press Shift+F8 again.
Selecting the current region
The current region is a contiguous range of cells that contain data. The current region is enclosed by blank rows and blank columns and/or the edge of the worksheet.
Ctrl+* (the star above the 9 in the numeric pad) is the keyboard shortcut for selecting the current region.
For those of you using laptops, the shortcut is Ctrl+Shift+*.
Moving the Cellpointer around a selected range
When you select a range of cells, the borders of the selected range are clearly defined.
To move vertically downward within the selected range, press Enter. To move vertically upward, press Shift+Enter.
To move horizontally to the right, press Tab. To move horizontally to the left, press Shift+Tab.
Would you like to move among cells at the corners of the selected range? Press Ctrl+. (Ctrl+period).
Selecting the first cell in a worksheet
A1 is the first cell in a worksheet.
To return to the first cell in the sheet from any other cell, press Ctrl+Home.
Selecting the last cell in the used range
In its memory, Excel stores the address of the last cell in the used range of every sheet in the workbook.
In the figure, the last cell in the used area in the active sheet is determined as the result of entering data into any one of the cells in row 40 and any one of the cells in column K.
The used range in the active sheet is the range of cells from A1 to K40. Therefore, the last active cell in the used area in the active sheet is K40.
To discover which cell is the last cell in the used area in the active sheet, press Ctrl+End.
Example: Select a worksheet, and then select cell F1000. Enter data into the cell, and clear the cell. Now press Ctrl+Home to move to the first cell. Move to the last cell in the used area by pressing Ctrl+End. The last cell in the used area is F1000.
Reducing the used area in a worksheet
Delete rows that do not contain data (rows 38-40 in the figure), and then press Ctrl+S to save the file. Press Ctrl+End. The address of the last cell in the new used area is K37. The address of the last cell in the used area is updated when the file is saved.
Why is it important to reduce the address of the last cell in the used area?
© Vertical scroll bar – it gets shorter as the used area of a worksheet grows larger, and this makes it inconvenient to use.
© Print area – the default print area is the used area in the worksheet. If you do not set a specific range as the print area, Excel will automatically print all of the cells from A1 through that last cell in the used area.
© Display of the current region – later in this chapter, in the section titled View all data in the worksheet, you will want to reduce the used area in order to use this option.
Tip – Easily delete data from cells in the worksheet
The shortcuts Ctrl+Shift+End and Ctrl+Shift+Home allow you to quickly select a cell that contains data and extend the selection to the beginning of the worksheet or the last used cell in the worksheet.
Select a cell in the worksheet. Press Ctrl+Shift+End, and your selection will include all of the cells from the selected cell through the last used cell in the worksheet.
Example: In the worksheet there are about 1,000 rows of data. In order to delete the data from row 21 through the last used cell in the worksheet, select cell A21, press Ctrl+Shift+End, and press Del.
Rows and Columns
Selecting a column or columns
Select a cell or several cells in a worksheet, and press Ctrl+Spacebar.
Selecting a row or rows
Select a cell or several cells in a worksheet, and press Shift+Spacebar.
Adding a cell, a row, a column
Press Ctrl++ (Ctrl and the + key)
Deleting a cell, a row, a column
Press Ctrl+-(Ctrl and the - key)
Hiding or unhiding a column or columns
Hide – select a cell or cells and press Ctrl+0.
Unhide – select the cells to the left and right of the hidden column, and press Ctrl+Shift+0.
Hiding or unhiding a row or rows
Hide – select a cell or cells, and press Ctrl+9.
Unhide – select the cells above and below the hidden row, and press Ctrl+Shift+9.
View All Data in Worksheet
An Excel worksheet is packed with hundreds or thousands of cells containing data. You can either view the complete data region in the worksheet or magnify or reduce the selected data region to the size of the window using the following technique.
1. Select the current region, press Ctrl+*.
2. Choose View, Zoom.
3. Select Fit selection.
4. Click OK.
Increase the amount of data that appears in the window by hiding window elements such as the sheet tabs, toolbars, formula bar and status bar.
1. Choose Tools, Options.
2. Select the View tab.
3. Clear the check boxes for Row & column headers, Horizontal scroll bar, Vertical scroll bar, Sheet tabs, Formula bar and Status bar.
4. Click OK.
5. Select View (from the Excel menu), and uncheck the selection of Formula Bar and Status Bar.
6. Select any one of the toolbars and right-click. From the shortcut menu, clear the check boxes beside each of the toolbars displayed.
Result
Only the title row is displayed, and more rows are visible.
Tip – Using a wheel mouse?
Quickly increase or decrease the percentage of the screen magnification. Select cell A1, press Ctrl, and roll the mouse wheel forward or backward.
Moving between Sheets in a Workbook
Each Excel workbook can contain a number of worksheets.
Switching between the sheets is difficult if you use a mouse to select a worksheet by name in the workbook. This is particularly true if the workbook has a large number of sheets and the names of the sheets are long.
There are a number of ways to select a worksheet, aside from the (annoying) method of using the mouse to search for and locate the name of the worksheet among the names of many worksheets.
Using keyboard shortcuts to move between sheets
To move to the next sheet in the workbook, press Ctrl+Page Down.
To move to the previous sheet in the workbook, press Ctrl+Page Up.
Selecting a sheet from the shortcut menu
To the left of the sheet tabs in the horizontal scroll bar row are several small arrow buttons. Place the mouse pointer over one of the arrows and right-click. From the shortcut menu, select a sheet from the list of sheet names.
Jumping Quickly between Cells in a Workbook
Merely selecting a sheet, no matter which method you use, will not bring you to your destination, which is the specific address you want to reach. The best way to move to the cell in a current sheet or to a cell in a different sheet in the workbook is by selecting the name of the cell or the name of the range from the Name box.
The Name box can be found to the left of the formula bar.
Using the Name box
Name box – an address box. Selecting a name is the same as selecting the address of the cell or range of cells in the active workbook.
Move to cell – type the cell address in the Name box. For example, type Z5000, and press Enter. As a result, you will move to cell Z5000 (similar to using F5 or selecting Go To… in the Edit menu).
Selecting a large range of cells for copying, cutting or pasting
Example: Copy text from cell A1 to cells A2 through D1000.
1. Select cell A1.
2. In the Name box, type the cell reference D1000.
3. Press Shift+Enter.
For more information about naming cells and ranges in a workbook, see Chapter 6, Names.
Copying, Cutting and Pasting
When using Excel on a regular basis, you repeatedly perform a large number of common operations. The most widely used of the common operations are Copy, Cut and Paste.
Keyboard shortcuts are the fastest way to perform these common operations.
Keyboard shortcuts
Copy Ctrl+C
Cut Ctrl+X
Paste, with the option of repeating the operation Ctrl+V
Paste, without the option of repeating the operation Enter
Paste copied selections to several locations
In Excel version 2000 and higher, you can paste areas that you saved on the Clipboard to several locations.
In Excel 2000, the Clipboard presents the various copied fields (up to 12). In Excel 2002, use the keyboard shortcut Ctrl+C+C to open the Tasks dialog box, which contains the copied areas that have been saved to memory.
Copying and pasting, using the mouse and keyboard
Press Ctrl, and click a cell at the same time. Now drag the cell to a new location. Release the mouse button and the Ctrl key.
Cutting and pasting, using the mouse and keyboard
Click and drag the cell a new location, and then release the mouse button.
This method of copying and/or cutting using the mouse with or without the Ctrl key is also a good method for copying/cutting rows, columns or an entire worksheet.
Copying a cell with text or a formula to thousands of cells easily
Example: Copy text from cell A1 to cells A2 through A5000.
1. In cell A1, type Excel Book.
2. Copy cell A1.
3. Select cell A2.
4. Click the arrow beside the Name box.
5. Type A5000.
6. Press Shift+Enter (select an adjacent range of cells).
7. Press Enter (paste).
Copying a cell by double-clicking
1. Select cell C2 (see figure at right).
2. Point to the handle on the bottom right-hand corner of the cell pointer.
3. Double-click when the mouse pointer changes its shape to a plus symbol.
Excel copies the text or formula in the cell down the length of column B. The cell is pasted to the bottom of the data in the adjacent column.
Moving between Open Workbooks
From the Window menu, select a workbook from the list of open workbooks.
The keyboard shortcut for moving between open workbooks is Ctrl+Tab or Ctrl+F6.
Copying or Moving a Sheet
There is a difference between copying all the cells in a sheet and copying a complete sheet.
Copying cells from a sheet
Select all of the cells in the sheet by pressing Ctrl+A, or click the button to the left of the column heading for column A. Press Ctrl+C. Select another sheet and select cell A1. Then press Enter.
Copying a sheet
Copying a sheet means copying all of the cells, including the page setup and names.
1. Option 1 – Move the mouse pointer to a sheet tab. Press Ctrl, and use the mouse to drag the worksheet to a different location. Release the mouse button and release the Ctrl key.
2. Option 2 – Right-click the appropriate sheet tab. From the shortcut menu, select Move or Copy. The Move or Copy box lets you copy the worksheet to a different location in the current workbook or move the complete worksheet to a different workbook. Be sure to mark the checkbox beside Create a copy.
3. Option 3 – From the Window menu, select Arrange, and check the first of the four options. When all of the open workbooks are tiled in the window, use Option 1 (dragging the worksheet while pressing Ctrl) to copy or move a worksheet.
Caution
Moving a worksheet from a workbook with cell names or formulas that are linked to a different worksheet and/or a different workbook will create the links in the new workbook.
After you move the worksheet, from the Edit menu, select Links, and update or cancel them before you save the new workbook. For more information, see the sections pertaining to links in Chapter 7, Formulas.