Nội dung

Select a Range To work with a range in Excel, you must select the cells that you want to include in the range. After you select the range, you can fill it with data, move or copy it to another part of the worksheet, format the cells, delete the data, and so on. You can select a range as a rectangular group of cells, as a collection of individual cells, or as an entire row or column. It is almost always easiest to select a range using the mouse, but Excel does offer a few keyboard shortcuts for selecting ranges, as described in the Tip on the next page. Select a Range Select a Rectangular Range 1 Position 1 over the first cell you want to include in the range. 2 Click and drag over the cells that you want to include in the range. • 3 Excel selects the cells. Release the mouse button to end the range selection. 2 Select a Range of Individual Cells 1 Click in the first cell that you 1 want to include in the range. 2 Hold down and click in each of the other cells that you want to include in the range. • 2 2 Each time you click in a cell, Excel adds it to the range. 3 Release selection. to end the range 2 40 05_577639-ch03.indd 40 3/15/10 2:38 PM 3 CHAPTER Working with Excel Ranges 1 Select an Entire Row 1 Position over the header of the row you want to select. 2 changes to . 2 Click the row header. • 2 1 Excel selects the entire row. To select multiple rows, click and drag across the row headers or hold down and click each row header. Select an Entire Column 1 Position over the header of the column you want to select. changes to . 2 Click the column header. • Are there keyboard techniques I can use to select a range? Yes. To select a rectangular range, navigate to the first cell that you want to include in the range, hold down , and then press or to extend the selection. To select an entire row, navigate to any cell in the row and press + . To select an entire column, navigate to any cell in the column and then press + . Excel selects the entire column. To select multiple columns, click and drag across the column headers, or hold down and click each column header. Is there an easy way to select every cell in the worksheet? Yes. Excel offers two methods you can use. Either press + , or click the Select All button ( ) in the upper left corner of the worksheet. 41 05_577639-ch03.indd 41 3/15/10 2:38 PM Fill a Range with the Same Data If you need to fill a range with the same data, you can save time by getting Excel to fill the range for you. The most common method for filling a range in this way is to use Excel’s AutoFill feature, which makes it easy to fill a vertical or horizontal range with the same value. However, Excel also offers an alternative method that enables you to fill any selected range. See “Select a Range,” earlier in this chapter, to learn how to select a range of cells. Fill a Range with the Same Data Fill a Vertical or Horizontal Range 1 In the first cell of the range you want to work with, enter the data you want to fill. 1 2 2 Position over the bottom right corner of the cell. changes to . 3 Click and drag down to fill a vertical range or across to fill a horizontal range. 3 4 Release the mouse button. • Excel fills the range with the initial cell value. 42 05_577639-ch03.indd 42 3/15/10 2:38 PM 3 CHAPTER Working with Excel Ranges Fill a Selected Range 2 1 Select the range you want to fill. 1 2 Type the text, number, or other data. 3 Press • How do I fill a vertical or horizontal range without also copying the formatting of the original cell? Follow these steps: + Excel fills the range with the value you typed. 2 1 Perform Steps 1 to 4 to fill the data. • . 3 Excel displays the AutoFill Options smart tag ( ). 2 Click the AutoFill Options . 3 Click Fill Without Formatting. Excel removes the original cell’s formatting from the copied cells. 43 05_577639-ch03.indd 43 3/15/10 2:38 PM Fill a Range with a Series of Values If you need to fill a range with a series of values, you can save time by using Excel’s AutoFill feature to create the series for you. AutoFill can fill a series of numeric values such as 5, 10, 15, 20, and so on; a series of date values such as January 1, 2011, January 2, 2011, and so on; or a series of alphanumeric values such as Chapter 1, Chapter 2, Chapter 3, and so on. You can also create your own series with a custom step value, which determines the numeric difference between each item in the series. Fill a Range with a Series of Values AutoFill a Series of Numeric, Date, or Alphanumeric Values 1 Click in the first cell and type 2 1 4 the first value in the series. 2 Click in an adjacent cell and 3 type the second value in the series. 3 Select the two cells. 4 Position over the bottom 5 right corner of the second cell. changes to . 5 Click and drag down to fill a vertical range or across to fill a horizontal range. • As you drag through each cell, Excel displays the series value that it will add to the cell. 6 Release the mouse button. • 6 Excel fills the range with a series that continues the pattern of the initial two cell values. 44 05_577639-ch03.indd 44 3/15/10 2:38 PM 3 CHAPTER Working with Excel Ranges 3 Fill a Custom Series of Values 4 1 Click in the first cell and type the first value in the series. 2 Select the range you want to 5 1 2 fill, including the initial value. 3 Click the Home tab. 4 Click Fill ( ). 5 Click Series. The Series dialog box appears. 6 In the Type group, select the type of series you want to fill ( changes to ). 6 9 7 If you selected Date in Step 6, 7 select an option in the Date unit group ( changes to ). 8 8 In the Step value text box, type the value you want to use. 9 Click OK. • Excel fills the range with the series you created. Can I create my own AutoFill series? Yes. You can create a custom list. When you add the first value in your custom list, you can then use AutoFill to fill a range with the rest of the series. Follow these steps: 1 Click the File tab. 6 In the List entries 5 box, type each 2 Click Options. item in your 3 Click Advanced. list, and press after each 4 Click Edit Custom Lists. 5 Click NEW LIST. item. 7 Click Add. 8 Click OK. 7 6 8 45 05_577639-ch03.indd 45 3/15/10 2:38 PM Move or Copy a Range You can restructure or reorganize a worksheet by moving an existing range to a different part of the worksheet. For example, if you have two related ranges that are far apart on the worksheet, you can move one of them so that the ranges appear close to each other. You can also make a copy of a range, which is a useful technique if you require either a duplicate of the range elsewhere, or if you require a range that is similar to an existing range. Move or Copy a Range Move a Range 1 Select the range you want to move. 2 2 Position over any outside border of the range. changes to . 3 Click and drag the range to the new location. 3 1 changes to . • • Excel displays an outline of the range. Excel displays the address of the new location. 4 Release the mouse button. • Excel moves the range to the new location. 4 46 05_577639-ch03.indd 46 3/15/10 2:38 PM 3 CHAPTER Working with Excel Ranges Copy a Range 1 3 4 1 Select the range you want to copy. 2 Press and hold . 3 Position over any outside border of the range. changes to . 4 Click and drag the range to the location where you want the copy to appear. • • Excel displays an outline of the range. Excel displays the address of the new location. 5 Release the mouse button. 6 Release . 5 • Excel creates a copy of the range in the new location. Can I move or copy a range to another worksheet? Can I move or copy a range to another workbook? Yes. Click and drag the range as described in this section. Remember to hold down if you are copying the range. Press and hold and then drag the mouse pointer over the tab of the sheet you want to use as the destination. Excel displays the worksheet. Release and then drop the range on the worksheet. Yes. If you can see the other workbook on-screen, click and drag the range as described in this section, and then drop it on the other workbook. Remember to hold down if you are copying the range. Otherwise, select the range, click the Home tab, click Cut ( ) to move the range or Copy ( ) to copy it, switch to the other workbook, select the cell where you want the range to appear, click Home, and then click Paste ( ). 47 05_577639-ch03.indd 47 3/15/10 2:38 PM Insert a Row or Column You can insert a row or column into your existing worksheet data to accommodate more information. This is particularly useful if the information you need to add fits naturally within the existing data, rather than at the end. When you insert a row, Excel shifts the existing rows down, so you must first determine the row above where you want your new row to appear. Similarly, when you insert a column, Excel shifts the existing columns to the right, so you must first determine the column to the left of where you want your new column to appear. Insert a Row or Column Insert a Row 2 3 4 1 Click in any cell in the row above where you want to insert the new row. 2 Click the Home tab. 3 Click the Insert . 4 Click Insert Sheet Rows. • • 1 Excel inserts the new row. The rows below the new row are shifted down. 5 Click the Format smart tag ( ). 6 Select a formatting option for the new row ( to ). changes 5 6 48 05_577639-ch03.indd 48 3/15/10 2:38 PM 3 CHAPTER Working with Excel Ranges 2 3 Insert a Column 4 1 Click any cell in the row to the 1 2 Click the Home tab. 3 Click the Insert . 4 Click Insert Sheet Columns. left of where you want to insert the new column. • • Excel inserts the new column. The columns to the right of the new column are shifted to the right. 5 Click the Format smart 6 5 tag ( ). 6 Select a formatting option for the new column ( to ). changes Can I insert more than one row or column at a time? Yes. You can insert as many new rows or columns as you need. First, select the same number of rows or columns that you want to insert. (See “Select a Range” earlier in this chapter to learn how to select rows and columns.) For example, if you want to insert four rows, select four existing rows. Follow Steps 2 to 4 in “Insert a Row” to insert rows, or Steps 2 to 4 in “Insert a Column” to insert columns. 49 05_577639-ch03.indd 49 3/15/10 2:38 PM