EXCEL tutorial for intermediate users


Télécharger EXCEL tutorial for intermediate users

Formation Excel en ligne par vidéo

205 leçons vidéos + 20 Livres PDF + 20 TP + Sous supervision + Certificat de réussite à la fin du cours



★★★★★★★★★★5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

Télécharger aussi :


EXCEL tutorial for intermediate users

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 at right 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

The following keyboard combinations can move you to the last (or first) cell in a range:

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 contains data, from top to bottom, press Ctrl+Shift+Down Arrow.

To select a vertical range of cells that contains data, from bottom to top, press Ctrl+Shift+Up Arrow.

To select a horizontal range of cells that contains 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 enables 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

Press F8 for contiguous selection. 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.

Press F8 again to toggle off the option of contiguous selection.

Press Shift+F8 for non-contiguous selection. 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. Press Shift+F8 again to toggle off the option of non-contiguous selection.

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+8.

Moving the Cellpointer around a selected range

When you select a range of cells, the borders of the selected range are clearly defined. Press Enter to move downward within the selected range. Press Shift+Enter to move upward within the selected range. Press Tab to move to the right. Press Shift+Tab to move to the left. Would you like to move to 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.

Press Ctrl+Home to return to A1 from any cell on the sheet.

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 range 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 range is K37. The address of the last cell in the used range is updated when the file is saved. Why is it important to reduce the address of the last cell in the used range?

‹ Vertical scroll bar – it gets shorter as the used range of a worksheet grows larger and this makes it inconvenient to use.

‹ Print area – the default print area is the used range in the worksheet. If you do not set a specific area as the print area, Excel will automatically print all of the cells from A1 through that last cell in the used range.

‹ View more data in the worksheet – later in this chapter, in the section titled View all data in the worksheet, you will want to reduce the used range 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.

Note: if a selected cell is part of a column that includes merged cells, the columns that the merged cells are part of will also be highlighted.

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 More Data in Worksheet

An Excel worksheet can be 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. Press Ctrl+* to select the current region.
  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 any one of the toolbars and right-click.

From the shortcut menu, choose "Customize…". Uncheck all toolbars and click Close.

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 the tab 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 different ways to select a worksheet, aside from the (annoying) method of searching through the many worksheet tabs of a workbook.

Using keyboard shortcuts to move between sheets

Press Ctrl+Page Down to move to the next sheet in the workbook.

Press Ctrl+Page Up to move to the previous sheet in the workbook.

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 a specific cell/range in a worksheet 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. Jump to a 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 Ctrl+g or selecting Go To… in the Edit menu).



28