Nội dung

Merge Two or More Cells You can create a single large cell by merging two or more cells. For example, it is common to merge some of the top row of cells to use as a worksheet title. Another common reason for merging cells is to create a label that applies to multiple columns of data. This can make your worksheet easier to read because it makes it clear that the label applies to multiple columns instead of just a single column. Merge Two or More Cells 1 Select the cells that you want 1 to merge. 2 Click the Home tab. 3 Click the Merge and Center . 4 Click Merge Cells. 2 3 4 60 05_577639-ch03.indd 60 3/15/10 2:38 PM 3 CHAPTER Working with Excel Ranges • 5 Excel merges the selected cells into a single cell. 5 Type your text in the merged cell. How do I center text across multiple columns? This is a useful technique for your worksheet titles or headings. You can center a title across the entire worksheet, or you can center a heading across the columns that it refers to. Follow Steps 1 to 3 and then click Merge & Center. Excel creates the merged cell and formats the cell with the Center alignment option. Any text you enter into the merged cell appears centered within the cell. 61 05_577639-ch03.indd 61 3/15/10 2:38 PM Transpose Rows and Columns You can use Excel’s Transpose command to easily turn a row of data into a column of data, or a column of data into a row of data. You can also transpose rows and columns together in a single command, which is handy when you need to restructure a worksheet. Transpose Rows and Columns 1 Select the range that includes the data you want to transpose. 2 Click the Home tab. 3 Click Copy ( ). 1 2 3 62 05_577639-ch03.indd 62 3/15/10 2:38 PM 3 CHAPTER Working with Excel Ranges 4 Click where you want the 5 transposed range to appear. 5 Click the Paste . 6 Click Transpose ( ). 6 4 • How do I know which cells to select? The range you select before copying depends on what you want to transpose. If you want to transpose a single horizontal or vertical range of cells, then select just that range. If you want to transpose a horizontal range of cells and a vertical range of cells at the same time, select the range that includes all the cells, as shown in this section’s example. Excel transposes the data and then pastes it to the worksheet. Can I transpose range values as well as range labels? Yes, Excel’s Transpose command works with text, numbers, dates, formulas, and any other data that you can add to a cell. So if you have a rectangular region of data that includes row labels, column labels, and cell values within each row and column, you can select the entire range and transpose it. 63 05_577639-ch03.indd 63 3/15/10 2:38 PM 4 Chapter Working with Range Names You can make it easier to navigate Excel worksheets and build Excel formulas by applying names to your ranges. A range name is a text label that you apply to a single cell or to a range of cells. Once you have defined a name for a range, you can use that name in place of the range coordinates, which has several benefits. For example, range names are more intuitive than range coordinates, particularly in formulas; range names are more accurate than range coordinates; range names are easier to remember than range coordinates; and range names make it easier to navigate a worksheet. This chapter explains range names and shows you how to define, edit, and use range names. 06_577639-ch04.indd 64 3/15/10 2:41 PM Define a Range Name . . . . . . . . . . . . . . . . . . . . . . . . . . 66 Use Worksheet Text to Define a Range Name . . . . . . . . . . 68 Navigate a Workbook Using Range Names . . . . . . . . . . . . 70 Change a Range Name . . . . . . . . . . . . . . . . . . . . . . . . . 72 Delete a Range Name . . . . . . . . . . . . . . . . . . . . . . . . . . 74 06_577639-ch04.indd 65 3/15/10 2:41 PM Define a Range Name Before you can use a range name in your formulas or to navigate a worksheet, you must first define the range name. You can define as many names as you need, and you can even define multiple names for the same range. You can create range names by hand, or you can get Excel to create the names for you automatically based on the existing text labels in a worksheet. For the latter, see “Use Worksheet Text to Define a Range Name.” Define a Range Name 1 Select the range you want to name. 1 2 Click the Formulas tab. 3 Click Define Name. 2 3 66 06_577639-ch04.indd 66 3/15/10 2:41 PM 4 CHAPTER Working with Range Names 4 The New Name dialog box appears. 4 Type the name you want to use into the Name text box. 5 Note: The first character of the name must be a letter or an underscore (_). The name cannot include spaces or cell references, and it cannot be any longer than 255 characters. 5 Click OK. Excel assigns the name to the range. • Is there an easier way to define a range name? Yes, you can follow these steps to bypass the New Name dialog box: 1 Select the range you want to name. The new name appears in the Name box whenever the range is selected. 3 2 1 2 Click inside the Name box. 3 Type the name you want to use. 4 Press . Excel assigns the name to the range. 67 06_577639-ch04.indd 67 3/15/10 2:41 PM Use Worksheet Text to Define a Range Name If you have several ranges to name, you can speed up the process by getting Excel to create the names for you automatically based on the range’s text labels. Text labels make a worksheet easier to read and understand, but in this section you see that they also make it easier to define range names. For example, if you have a column of sales data that has the label “Sales” on top, Excel can automatically apply the name “Sales” to that range. You can create range names from worksheet text when the labels are in the top or bottom row of the range, or the left or right column of the range. Use Worksheet Text to Define a Range Name 1 Select the range or ranges you want to name. • Be sure to include the text labels you want to use for the range names. 1 2 Click the Formulas tab. 3 Click Create from Selection. 2 3 68 06_577639-ch04.indd 68 3/15/10 2:41 PM 4 CHAPTER Working with Range Names 4 The Create Names from Selection dialog box appears. 4 Select the setting or settings 5 that correspond to where the text labels are located in the selected range ( changes to ). If Excel has activated a check box that does not apply to your data, click it ( changes to ). 5 Click OK. Excel assigns the text labels as range names. • When you select one of the ranges, the range name assigned by Excel appears in the Name box. Note: If the label text contains any illegal characters, such as a space, Excel replaces each of those characters with an underscore (_). Is there a faster way to run the Create from Selection command? Given a table with labels in the top row and left column, is there a way to automatically assign a name to the table data? Yes, Excel offers a keyboard shortcut for the command. Select the range or ranges you want to work with and then press + + . Excel displays the Create Names from Selection dialog box. Follow Steps 4 and 5 to create the range names. Yes. The table data refers to the range of cells that does not include the table headings in the top row and left column. To assign a name to the data range, type a label in the top left corner of the table. When you run the Create from Selection command on the entire table, Excel assigns the top left label to the data range, as shown here. 69 06_577639-ch04.indd 69 3/15/10 2:41 PM