Nội dung

Insert a Cell or Range If you need to add data to an existing range, you can insert a single cell or a range of cells within that range. When you insert a cell or range, Excel shifts the existing data to accommodate the new cells. Excel can either shift the existing data down or to the right. Therefore, you need to decide in advance where you want the new range to be inserted. You then tell Excel where you want the insertion to take place by selecting existing data either below or to the right of where you want the new range to appear. Insert a Cell or Range 1 Select the cell or range where you want the inserted cell or range to appear. 1 2 Click the Home tab. 3 Click the Insert . 4 Click Insert Cells. 2 4 3 Note: You can also press + +=. 50 05_577639-ch03.indd 50 3/15/10 2:38 PM 3 CHAPTER Working with Excel Ranges The Insert dialog box appears. 5 5 Select the option that corresponds to how you want Excel to shift the existing cells to accommodate your new cells ( changes to ). 6 7 Note: In most cases, if you selected a horizontal range, click the Shift cells down option; if you selected a vertical range, click the Shift cells right option. 8 6 Click OK. • • Excel inserts the cell or range. The existing data is shifted down (in this case) or to the right. 7 Click the Format smart tag ( ). 8 Select a formatting option for the new row ( changes to Under what circumstances would I insert a cell or range instead of inserting an entire row or column? How do I know which cells to select to get my inserted cell or range in the correct position? In most cases, it is better to insert a cell or range when you have other data either to the left or right of the existing range, or above or below the range. For example, if you have data to the left or right of the existing range, inserting an entire row would create a gap in the other data. The easiest way to do this is to select the existing cell or range that is exactly where you want the new cell or range to appear. For example, if you want the new range to be A5:B5 as shown in this section’s example, you first select the existing A5:B5 range. When you insert the new range, Excel shifts the existing cells down (in this case) to accommodate it. ). 51 05_577639-ch03.indd 51 3/15/10 2:38 PM Delete Data from a Range If your worksheet has a range that contains data you no longer need, you can delete that data. This helps to reduce worksheet clutter and makes your worksheet easier to read. Note that the technique in this section only applies to deleting the data that exists within each cell in a selected range; it does not apply to deleting the actual range. If you want to delete cells and not just the data, see “Delete a Range” later in this chapter. Delete Data from a Range Delete Range Data 1 Select the range that contains the data you want to delete. 1 2 Click the Home tab. 3 Click Clear ( ). 4 Click Clear Contents. • If you want to delete the range data and its formatting, click Clear All instead. 2 3 4 52 05_577639-ch03.indd 52 3/15/10 2:38 PM 3 CHAPTER Working with Excel Ranges • 1 2 Excel removes the range data. Undo Range Data Deletion 1 Click the Undo 2 Click Clear. . Note: If the data deletion was the most recent action you performed, you can undo it by + or by clicking pressing Undo ( ). • Are there faster ways to delete the data from a range? Yes. Probably the fastest method is to select the range and then press Delete . You can also select the range, right-click any part of the range, and then click Clear Contents. Excel restores the data to the range. Is it possible to delete a cell’s numeric formatting? Yes. Select the range with the formatting that you want to delete, click Home, click , and then click Clear Formats. Excel removes all the formatting from the selected range. If you prefer to delete only the numeric formatting, click Home, click the Number Format , and then click General. 53 05_577639-ch03.indd 53 3/15/10 2:38 PM Delete a Range If your worksheet contains a range that you no longer need, you can delete that range. Note that when you delete a range, Excel deletes not just the data within the range, but the range cells themselves. Excel shifts the remaining worksheet data to replace the deleted range. Note that the technique in this section deletes the actual cells from the selected range. If you want to delete only the data in the range, see “Delete Data from a Range” earlier in this chapter. Delete a Range 1 Select the range that you want to delete. 1 2 Click the Home tab. 3 Click the Delete . 4 Click Delete Cells. 2 4 3 54 05_577639-ch03.indd 54 3/15/10 2:38 PM 3 CHAPTER Working with Excel Ranges The Delete dialog box appears. 5 5 Select the option that 6 corresponds to how you want Excel to shift the remaining cells after it deletes the range ( changes to ). Note: In most cases, if you have data below the selected range, click the Shift cells up option; if you have data to the right of the selected range, click the Shift cells left option. 6 Click OK. • Are there faster ways to delete a range? Yes. Probably the fastest method is to select the range and then press + . You can also select the range, right-click any part of the range, and then click Delete. Both methods display the Delete dialog box. Excel deletes the range and shifts the remaining data. How do I delete a row or column? To delete a row, select any cell in the row, click the Home tab, click the Delete , and then click Delete Sheet Rows. To delete a column, select any cell in the column, click the Home tab, click the Delete , and then click or Delete Sheet Columns. Note, too, that you can delete multiple rows or columns by selecting at least one cell in each row or column. 55 05_577639-ch03.indd 55 3/15/10 2:38 PM Hide a Row or Column If you do not need to see or work with a row or column temporarily, you can make your worksheet easier to read and to navigate by hiding the row or column. Hiding a row or column is also useful if you are showing someone a worksheet that contains private or sensitive data that you do not want the person to see. For example, if a row or column contains salary data, passwords, or Social Security numbers, you should hide the row or column to protect this data from nonauthorized viewers. Hide a Row or Column Hide a Row 2 1 Click in any cell in the row you 3 want to hide. 2 Click the Home tab. 3 Click Format. 4 Click Hide & Unhide. 5 Click Hide Rows. Note: You can also hide a row + . by pressing • • 4 5 1 Excel removes the row from the worksheet display. Excel displays a slightly thicker heading border between the surrounding rows to indicate that a hidden row lies between them. Another way to hide a row is over the bottom to move edge of the row heading ( changes to ) and then click and drag the edge up until the height displays 0. 56 05_577639-ch03.indd 56 3/15/10 2:38 PM 3 CHAPTER Working with Excel Ranges 2 Hide a Column 1 Click in any cell in the column 3 you want to hide. 1 4 5 2 Click the Home tab. 3 Click Format. 4 Click Hide & Unhide. 5 Click Hide Columns. Note: You can also hide a + . column by pressing • • Excel removes the column from the worksheet display. Excel displays a slightly thicker heading border between the surrounding columns to indicate that a hidden column lies between them. Another way to hide a column over the right is to move edge of the column heading ) and then ( changes to click and drag the edge left until the width displays 0. How do I display a hidden row or column? To display a hidden row, select the row above and the row below the hidden row, click Home, click Format, click Hide & Unhide, and then click Unhide Rows. Alternatively, move between the headings of the selected rows ( changes to ) and then double-click. To unhide row 1, right-click the top edge of the row 2 header and then click Unhide. To display a hidden column, select the column to the left and the column to the right of the hidden column, click Home, click Format, click Hide & Unhide, and then click Unhide Columns. Alternatively, move between the headings of the selected columns ( changes to ) and then double-click. To unhide column A, right-click the left edge of the column B header and then click Unhide. 57 05_577639-ch03.indd 57 3/15/10 2:38 PM Freeze Rows or Columns As you vertically scroll a worksheet, you can keep your column labels in view by freezing the row or rows that contain the labels. This makes it easier to review and edit the existing data and to insert new data to the worksheet because you can always see the column labels. If your worksheet also includes row labels, you can keep those labels in view as you horizontally scroll the worksheet by freezing the column or columns that contain the labels. Freeze Rows or Columns 1 Freeze Rows 1 Scroll the worksheet so that the row or rows that you want to freeze are visible. 2 2 Position over the horizontal split bar ( ). changes to . 3 Click and drag and drop it below the row you want to freeze. • Excel splits the worksheet into two horizontal panes. 4 Click the View tab. 5 Click Freeze Panes. 6 Click Freeze Panes. • 4 5 6 If you want to freeze just the first row, you can bypass Steps 1 to 3 and click the Freeze Top Row command. Excel freezes the panes. 58 05_577639-ch03.indd 58 3/15/10 2:38 PM 3 CHAPTER Working with Excel Ranges 1 Freeze Columns 1 Scroll the worksheet so that the column or columns that you want to freeze are visible. 2 Position over the vertical split bar ( ). changes to 2 . 3 Click and drag and drop it on the right edge of the column you want to freeze. 3 • 4 5 6 Excel splits the worksheet into two vertical panes. 4 Click the View tab. 5 Click Freeze Panes. 6 Click Freeze Panes. • If you want to freeze just the first column, you can bypass Steps 1 to 3 and click the Freeze First Column command. Excel freezes the panes. Can I adjust the position of a frozen row or column? How do I unfreeze a row or column? Yes. Begin by unfreezing the panes: Click View, click Freeze Panes, and then click Unfreeze Panes. Excel unfreezes the panes and displays the split bar. Click and drag the split bar to the new location. Click View, click Freeze Panes, and then click Freeze Panes. If you no longer require a row or column to be frozen, you can unfreeze it by clicking View, clicking Freeze Panes, and then clicking Unfreeze Panes. If you no longer want your worksheet split into two panes, click View and then click Split ( ). 59 05_577639-ch03.indd 59 3/15/10 2:38 PM