Nội dung

Navigate a Workbook Using Range Names One of the big advantages of defining range names is that they make it easier to navigate a worksheet. You can choose a range name from a list and Excel automatically selects the associated range. This is much faster than scrolling through a workbook by hand, or by entering a cell or range reference into Excel’s Go To command. Excel offers two methods for navigating a workbook using range names: the Name box and the Go To command. Navigate a Workbook Using Range Names Navigate Using the Name Box 1 1 Open the workbook that contains the range you want to work with. 2 Click the Name box . 3 Click the name of the range 2 3 you want to select. • Excel selects the range. 70 06_577639-ch04.indd 70 3/15/10 2:41 PM 4 CHAPTER Working with Range Names 1 2 3 Navigate Using the Go To Command 1 Open the workbook that 4 contains the range you want to work with. 2 Click the Home tab. 3 Click Find & Select. 4 Click Go To. Note: You can also select the Go To command by pressing + . 5 The Go To dialog box appears. 5 Click the name of the range 6 you want to select. 6 Click OK. • Excel selects the range. Is it possible to navigate to a named range in a different workbook? Yes, but it is not easy or straightforward: 1 Follow Steps 1 to 4 on this page to display the Go To dialog box. 2 In the Reference text box, type the following: 2 3 '[workbook]worksheet’!name Replace workbook with the file name of the workbook, worksheet with the name of the worksheet that contains the range, and name with the range name. 3 Click OK. 71 06_577639-ch04.indd 71 3/15/10 2:41 PM Change a Range Name You can change any range name to a more suitable or accurate name. This is useful if you are no longer satisfied with the original name you applied to a range, if the data changes and the original name is no longer suitable for the new data, or if you do not like the name that Excel generated automatically from the worksheet labels. If you want to change the range coordinates associated with a range name, see the second Tip on the next page. Change a Range Name 1 Open the workbook that 2 contains the range name you want to change. 1 3 2 Click the Formulas tab. 3 Click Name Manager. The Name Manager dialog box appears. 4 Click the name you want to 5 4 change. 5 Click Edit. 72 06_577639-ch04.indd 72 3/15/10 2:41 PM 4 CHAPTER Working with Range Names 6 The Edit Name dialog box appears. 6 Use the Name text box to edit the name. 7 Click OK. 7 • The new name appears in the Name Manager dialog box. 8 Repeat Steps 4 to 7 to rename other ranges as needed. 9 Click Close. 9 Is there a faster method I can use to open the Name Manager dialog box? Yes, Excel offers a shortcut key that enables you to bypass Steps 2 and 3. Open the workbook that contains the range name you want to change, and then press + . Excel opens the Name Manager dialog box. Can I assign a name to a different range? Yes. If you add another range to your workbook and you feel that an existing name would be more suited to that range, you can modify the name to refer to the new range. Follow Steps 1 to 5 to open the Edit Name dialog box. Click inside the Refers to reference box, and then click and drag the mouse on the worksheet to select the new range. Click OK. 73 06_577639-ch04.indd 73 3/15/10 2:41 PM Delete a Range Name If you have a range name that you no longer need, you can use Excel’s Name Manager to delete it. This is a good practice, because you do not want to have unused range names in a workbook. Getting rid of unneeded range names reduces clutter in the Name Manager dialog box, and it also makes the Name box easier to navigate. It is also a good idea to delete unused range names because you may need to reuse the names for different ranges later on. Delete a Range Name 1 Open the workbook that 2 contains the range name you want to delete. 1 3 2 Click the Formulas tab. 3 Click Name Manager. Note: You can also select the Name Manager command by + . pressing The Name Manager dialog box appears. 5 4 Click the name you want to delete. 5 Click Delete. 4 74 06_577639-ch04.indd 74 3/15/10 2:41 PM 4 CHAPTER Working with Range Names Excel asks you to confirm the deletion. 6 6 Click OK. • 7 Excel deletes the range name. Repeat Steps 4 to 6 to delete other range names as needed. 8 Click Close. 8 Is there a faster way to delete multiple range names? Yes, you can delete two or more range names at once. First, follow Steps 1 to 3 to display the Name Manager dialog box. Next, select the range names you want to delete: To select consecutive names, click the first name you want to delete, hold down , and then click the last name you want to delete; to select non-consecutive names, hold down and click each name you want to delete. When you have selected the name you want to remove, click Delete and then click OK when Excel asks you to confirm the deletion. 75 06_577639-ch04.indd 75 3/15/10 2:41 PM 5 Chapter Formatting Excel Ranges Microsoft Excel 2010 offers many commands and options for formatting ranges, and you learn about most of them in this chapter. For text, you learn how to change the font, the font size, the text color, and the text alignment, as well as how to center text across columns and rotate text within a cell. You also learn how to change the background color, apply a number format, apply an AutoFormat and a style, change the column width and row height, add borders, and more. 07_577639-ch05.indd 76 3/15/10 2:41 PM Change the Font and Font Size . . . . . . . . . . . . . . . . . . . . 78 Apply Font Effects . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80 Change the Font Color . . . . . . . . . . . . . . . . . . . . . . . . . . 82 Align Text Within a Cell . . . . . . . . . . . . . . . . . . . . . . . . . 84 Center Text Across Multiple Columns . . . . . . . . . . . . . . . . 86 Rotate Text Within a Cell . . . . . . . . . . . . . . . . . . . . . . . . 88 Add a Background Color to a Range . . . . . . . . . . . . . . . . 90 Apply a Number Format . . . . . . . . . . . . . . . . . . . . . . . . 92 Change the Number of Decimal Places Displayed . . . . . . . . 94 Apply an AutoFormat to a Range . . . . . . . . . . . . . . . . . . 96 Apply a Conditional Format to a Range . . . . . . . . . . . . . . 98 Apply a Style to a Range . . . . . . . . . . . . . . . . . . . . . . . 100 Change the Column Width . . . . . . . . . . . . . . . . . . . . . . 102 Change the Row Height . . . . . . . . . . . . . . . . . . . . . . . 104 Wrap Text Within a Cell . . . . . . . . . . . . . . . . . . . . . . . . 106 Add Borders to a Range . . . . . . . . . . . . . . . . . . . . . . . 108 07_577639-ch05.indd 77 3/15/10 2:41 PM Change the Font and Font Size When you work in an Excel worksheet, you can add visual appeal to a cell or range by changing the font and font size. In this section, the term font is synonymous with typeface, and both refer to the overall look of each character. By default, Excel offers nearly 200 different fonts in a wide variety of styles. Also, the font size is measured in points, where there are roughly 72 points in an inch. In some cases, formatting a range with a larger font size can make the range text easier to read. Change the Font and Font Size 1 Select the range you want to 2 format. 3 2 Click the Home tab. 3 To change the typeface, click in the Font list and then click the typeface you want to apply. • 1 Excel applies the font to the text in the selected range. 78 07_577639-ch05.indd 78 3/15/10 2:41 PM 5 CHAPTER Formatting Excel Ranges 4 4 To change the font size, click in the Font Size list and then click the size you want to apply. In the Theme Fonts section of the Font list, what do the designations Body and Headings mean? When you create a workbook, Excel automatically applies a document theme to the workbook, and that theme includes predefined fonts. The theme’s default font is referred to as Body, and it is the font used for regular worksheet text. Each theme also defines a Headings font, which Excel uses for cells formatted with a heading or title style. • You can also type the size you want in the Size text box. • Excel applies the font size to the text in the selected range. Can I change the default font and font size? Yes. Click the File tab and then click Options to open the Excel Options dialog box. Click the General tab, click the Use this font , and then click the typeface you want to use as the default. Click the Font size and then click the size you prefer to use as the default. Click OK. 79 07_577639-ch05.indd 79 3/15/10 2:41 PM