Nội dung

Add a Background Color to a Range You can make a range stand out from the rest of the worksheet by applying a background color to the range. For example, many people apply a background color to the labels in a range, which makes it easier to differentiate the labels from the data. Perhaps the easiest way to change the background color is by applying a color from the set of 60 predefined colors that come with the workbook’s theme. You can also choose a color from Excel’s palette of standard colors, or from a custom color that you create yourself. Add a Background Color to a Range Select a Theme or Standard Color 1 Select the range you want to format. 2 3 2 Click the Home tab. 3 Click in the Fill Color list ( 1 ). 4 Click a theme color. • • • Alternatively, click one of Excel’s standard colors. Excel applies the color to the range text. 4 To remove the background color from the range, click No Fill. 90 07_577639-ch05.indd 90 3/15/10 2:42 PM 5 CHAPTER Formatting Excel Ranges 2 Select a Custom Color 3 1 Select the range you want to format. 2 Click the Home tab. 3 Click in the Fill Color 4 list ( 1 ). 4 Click More Colors. The Colors dialog box appears. 6 5 Click the color you want to use. 5 • You can also click the Custom tab and then either click the color you want or enter the values for the Red, Green, and Blue components of the color. 6 Click OK. Excel applies the color to the selected range. Are there any pitfalls to watch out for when I apply background colors? Yes. The biggest pitfall is applying a background color that clashes with the range text. For example, the default text color is black, so if you apply any dark background color, the text will be very difficult to read. Always use either a light background color with darkcolored text, or a dark background color with light-colored text. Can I apply a background that fades from one color to another? Yes. This is called a gradient effect. Select the range, click the Home tab, and then click the Font group’s dialog box launcher ( ). Click the Fill tab and then click Fill Effects. In the Fill Effects dialog box, use the Color 1 and the Color 2 to choose your colors. Click an option in the Shading styles section ( changes to ), and then click OK. 91 07_577639-ch05.indd 91 3/15/10 2:42 PM Apply a Number Format You can make your worksheet easier to read by applying a number format to your data. For example, if your worksheet includes monetary data, you can apply the Currency format to display each value with a dollar sign and two decimal places. Excel offers ten number formats, most of which apply to numeric data. However, you can also apply the Date format to date data, the Time format to time data, and the Text format to text data. Apply a Number Format 1 Select the range you want to format. 1 2 Click the Home tab. 3 Click the Number Format . 4 Click the number format you want to use. 2 3 4 92 07_577639-ch05.indd 92 3/15/10 2:42 PM 5 CHAPTER Formatting Excel Ranges • • Is there a way to get more control over the number formats? Yes. You can use the Format Cells dialog box to control properties such as the display of negative numbers, the currency symbol used, and how dates and times appear. Follow these steps: • For percentages, you can also click Percent Style ( ). • For large numbers, you can also click Comma Style ( ). 5 6 ). The Format Cells dialog box appears with the Number tab displayed. 4 In the Category list, click the type of number format you want to apply. 5 Use the controls that Excel displays to customize the number format. 07_577639-ch05.indd 93 For monetary values, you can also click Accounting Number Format ( ). 4 1 Select the range you want to format. 2 Click the Home tab. 3 Click the Number group’s dialog box launcher ( Excel applies the number format to the selected range. The controls you see vary depending on the number format you chose in Step 4. 6 Click OK. Excel applies the number format. 93 3/15/10 2:42 PM Change the Number of Decimal Places Displayed You can make your numeric values easier to read and interpret by adjusting the number of decimal places that Excel displays. For example, you might want to ensure that all dollar-andcent values show two decimal places, while dollar-only values show no decimal places. Similarly, Excel often displays values with a large number of decimal places. If you do not require the extra decimals — for example, if the values are simple temperatures or interest rates — you can make them easier to read by reducing the number of decimals. You can either decrease or increase the number of decimal places that Excel displays. Change the Number of Decimal Places Displayed Decrease the Number of Decimal Places 1 Select the range you want to 2 3 format. 2 Click the Home tab. 3 Click the Decrease Decimal button ( ). 1 • Excel decreases the number of decimal places by one. 4 Repeat Step 3 until you get the number of decimal places you want. 94 07_577639-ch05.indd 94 3/15/10 2:42 PM 5 CHAPTER Formatting Excel Ranges 2 3 Increase the Number of Decimal Places 1 Select the range you want to format. 2 Click the Home tab. 3 Click the Increase Decimal button ( ). 1 • Excel increases the number of decimal places by one. 4 Repeat Step 3 until you get the number of decimal places you want. My range currently has values that display different numbers of decimal places. What happens when I change the number of decimal places? In this situation, Excel uses the value that has the most displayed decimal places as the basis for formatting all the values. For example, if the selected range has values that display no, one, two, or four decimal places, Excel uses the value with four decimals as the basis. If you click Decrease Decimal, Excel displays every value with three decimal places; if you click Increase Decimal, Excel displays every value with five decimal places. 95 07_577639-ch05.indd 95 3/15/10 2:42 PM Apply an AutoFormat to a Range You can save time when formatting your Excel worksheets by using the AutoFormat feature. This feature offers a number of predefined formatting options that you can apply to a range all at once. The formatting options include the number format, font, cell alignment, borders, patterns, row height, and column width. The AutoFormats are designed for data in a tabular format, particularly where you have headings in the top row and left column, numeric data in the rest of the cells, and a bottom row that shows the totals for each column. Apply an AutoFormat to a Range 1 Select the range you want 1 to format. 2 Click AutoFormat ( ). 2 Note: See Chapter 1 to learn how to add a button to the Quick Access Toolbar. In this case, you must add the QuickFormat button. 96 07_577639-ch05.indd 96 3/15/10 2:42 PM 5 CHAPTER Formatting Excel Ranges The AutoFormat dialog box appears. 3 In the Table format list, click the AutoFormat you want to use. 3 4 Click OK. 4 • Excel applies the AutoFormat to the selected range. Is there a way to apply an AutoFormat without using some of its formatting? How do I remove an AutoFormat? Yes. Excel enables you to control all six formats that are part of each AutoFormat: Number, Font, Alignment, Border, Patterns, and Width/Height. Follow Steps 1 to 3 to choose the AutoFormat you want to apply. Click Options to expand the dialog box and display the Formats to apply group. Deselect the option for each format you do not want to apply ( changes to ), and then click OK. If you do not like or no longer need the AutoFormat you applied to the cells, you can revert to a plain, unformatted state. Select the range and then click to display the AutoFormat dialog box. In the format list, click None, and then click OK. Excel removes the AutoFormat from the selected range. 97 07_577639-ch05.indd 97 3/15/10 2:42 PM Apply a Conditional Format to a Range You can make a worksheet easier to analyze by applying a conditional format to a range. A conditional format is formatting that Excel applies only to cells that meet the condition you specify. For example, you can tell Excel to apply the formatting only if a cell’s value is greater than some specified amount. When you set up your conditional format, you can specify the font, border, and background pattern, which helps to ensure that the cells that meet your criteria stand out from the other cells in the range. Apply a Conditional Format to a Range 2 1 Select the range you want to work with. 3 4 2 Click the Home tab. 3 Click Conditional Formatting. 4 Click Highlight Cells Rules. 5 Click the operator you want to 5 use for your condition. 1 An operator dialog box appears, such as the Greater Than dialog box shown here. 6 Type the value you want to use for your condition. • You can also click the collapse dialog box button ( ) and then click in a worksheet cell. 6 7 Depending on the operator, you may need to specify two values. 7 Click this and then click the formatting you want to use. • To create your own format, click Custom Format. 98 07_577639-ch05.indd 98 3/15/10 2:42 PM 5 CHAPTER Formatting Excel Ranges 8 Click OK. 8 • Excel applies the formatting to cells that meet your condition. Can I set up more than one condition for a single range? How do I remove a conditional format from a range? Yes. Excel enables you to specify multiple conditional formats. For example, you could set up one condition for cells that are greater than some value, and a separate condition for cells that are less than some other value. You can apply unique formats to each condition. Follow Steps 1 to 8 to configure the new condition. If you no longer require a conditional format, you can delete it. Follow Steps 1 to 3 to select the range and display the Conditional Formatting menu, and then click Manage Rules. Excel displays the Conditional Formatting Rules Manager dialog box. Click the conditional format you want to remove and then click Delete Rule. 99 07_577639-ch05.indd 99 3/15/10 2:42 PM