Article ID: 922069
Article Last Modified on 10/15/2007
APPLIES TO
- Microsoft Office Excel 2007
INTRODUCTION
Microsoft Office Excel 2007 lets you change many of the ways that it displays data in a cell. For example, you can specify the number of digits to the right of a decimal point. You can also add a pattern and a border to the cell. You can access and modify most of these settings in the Format Cells dialog box. To access the Format Cells dialog box, click the Format Cells Dialog Box Launcher in the Font group on the Home tab.
This article contains information about each setting that is available in the Format Cells dialog box. Additionally, this article describes how each setting affects the way that your data is presented in Excel 2007.
MORE INFORMATION
The Format Cells dialog box contains the following tabs:
- Number
- Alignment
- Font
- Border
- Fill
- Protection
The following sections describe the settings that are available on each tab.
The Number tab
Automatic number formatting
By default, all worksheet cells are formatted with the General number format. With the General number format, anything that you type in the cell is usually left as-is. For example, if you type 36526 in a cell and then press ENTER, the cell contents are displayed as 36526. This behavior occurs because the cell remains in the General number format. However, if you first format the cell as a date, for example, d/d/yyyy, and then type 36526, the cell displays 1/1/2000.
Excel 2007 leaves the General number format in other situations. However, the cell contents are not displayed exactly as they are typed. For example, if you have a narrow column, and you type a long string of digits such as 123456789, the cell might instead display something that resembles 1.2E+08. If you check the number format in this situation, the format remains the General number format.
Finally, there are scenarios in which Excel 2007 may automatically change the General number format to another format, based on the characters that you type in the cell. This feature saves you from manually making the easily recognized number format changes. The following table outlines some examples of when this behavior can occur.
| If you type this | Excel 2007 automatically assigns this number format |
|---|---|
| 1 | General |
| 1.123 | General |
| 1.10% | 0.00% |
| 1.10E+02 | 0.00E+00 |
| 1 1/2 | # ?/? |
| $1.11 | Currency, two decimal places |
| 1/1/2001 | Date |
| 1:10 | Time |
Typically, Excel 2007 applies automatic number formatting whenever you type the following types of data in a cell:
- Currency
- Percentage
- Date
- Time
- Fraction
- Scientific
Built-in number formats
Excel 2007 has many built-in number formats from which you can choose. To use one of these formats, click any one of the categories under General. Then, click the option that you want for that format. When you click a format in the list, Excel 2007 automatically displays an example of the output in the Sample box on the Number tab. For example, if you type 1.23 in the cell, and you click Number in the category list and then set Decimal places to 3, 1.230 is displayed in the cell.
These built-in number formats use a predefined combination of the symbols that are listed in the "Custom number formats" section. However, the underlying custom number format is transparent to you.
The following table lists the available built-in number formats.
| Number format | Notes |
|---|---|
| Number | The options include the number of decimal places, whether the thousands separator is used, and the format that will be used for negative numbers. |
| Currency | The options include the number of decimal places, the symbol that is used for the currency, and the format that will be used for negative numbers. This format is used for general monetary values. |
| Accounting | The options include the number of decimal places and the symbol that is used for the currency. This format lines up the currency symbols and decimal points in a column of data. |
| Date | Select the style of the date in the Type list. |
| Time | Select the style of the time in the Type list. |
| Percentage | Multiplies the existing cell value by 100 and then displays the result with a percentage symbol. If you format the cell first and then type the number, only numbers between 0 and 1 are multiplied by 100. The only option is the number of decimal places. |
| Fraction | Select the style of the fraction in the Type list. If you do not format the cell as a fraction before you type the value, you may have to type a zero or a space before the fraction. For example, if the cell is formatted with the General number format, and you type 1/4 in the cell, Excel 2007 treats the data as a date. To type it as a fraction, type 0 1/4 in the cell. |
| Scientific | The only option is the number of decimal places. |
| Text | Cells that are formatted as text will treat anything that is typed in the cell as text. This includes numbers. |
| Special | Select one of the following in the Type list: Zip Code, Zip Code + 4, Phone Number, or Social Security Number. |
Custom number formats
If one of the built-in number formats does not display the data in the format that you require, you can create a custom number format. To do this, modify the built-in formats. Alternatively, combine the formatting symbols into your own combination.
Before you create your own custom number format, be aware of the following rules that govern the syntax for number formats:
Each format that you create can have up to three sections for numbers and a fourth section for text.
<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>
- The first section is the format for positive numbers. The second section is the format for negative numbers. The third section is the format for zero values.
- These sections are separated by semicolons.
- If you have only one section, all numbers (positive, negative, and zero) are formatted by using that format.
- You can prevent any number type (positive, negative, and zero) from being displayed by not typing symbols in the corresponding section. For example, the number format 0.00;; prevents any negative or zero values from being displayed.
- To set the color for any section in the custom format, type the name of the color in brackets in the section. For example, the number format [BLUE]#,##0;[RED]#,##0 formats positive numbers as blue and negative numbers as red.
- Instead of the default positive, negative, and zero sections in the number format, you can specify custom criteria that must be met for each section. The conditional statements that you specify must be enclosed in brackets. For example, the number format [>100][GREEN]#,##0;[<=-100][YELLOW]#,##0;[CYAN]#,##0 formats all numbers that are greater than 100 as green, all numbers that are less than or equal to -100 as yellow, and all other numbers as cyan.
- For each part of the format, type symbols that represent how you want the number to look. See the following table for information about the available symbols.
To create a custom number format, click Custom in the Category list. The Category list is located on the Number tab in the Format Cells dialog box. Then, type your custom number format in the Type box.
The following table describes the different symbols that are available for use in custom number formats.
| Format symbol | Description and result |
|---|---|
| 0 | Digit placeholder. For example, if you type 8.9, and you want it to be displayed as 8.90, use the format #.00 |
| # | Digit placeholder. This symbol follows the same rules as the 0 symbol. However, Excel 2007 does not display extra zeros when the number that you type has fewer digits on either side of the decimal than there are # symbols in the format. For example, if the custom format is #.##, and you type 8.9 in the cell, the number 8.9 is displayed. |
| ? | Digit placeholder. This symbol follows the same rules as the 0 symbol. However, Excel 2007 puts a space for insignificant zeros on either side of the decimal point so that decimal points are aligned in the column. For example, the custom format 0.0? aligns the decimal points for the numbers 8.9 and 88.99 in a column. |
| . (period) | Decimal point. |
| % | Percentage. If you type a number between 0 and 1, and you use the custom format 0%, Excel 2007 multiplies the number by 100 and adds the percentage symbol in the cell. |
| , (comma) | Thousands separator. Excel 2007 separates thousands by commas if the format contains a comma that is enclosed by number signs (#) or by zeros. A comma that follows a placeholder scales the number by one thousand. For example, if the format is #.0,, and you type 12,200,000 in the cell, the number 12.200.0 is displayed. |
| E- E+ e- e+ | Scientific format. Excel 2007 displays a number to the right of the "E" symbol that corresponds to the number of places that the decimal point was moved. For example, if the format is 0.00E+00, and you type 12,200,000 in the cell, the number 1.22E+07 is displayed. If you change the number format to #0.0E+0, the number 12.2E+6 is displayed. |
| $-+/():space | Displays the symbol. If you want to display a character that differs from one of these symbols, precede the character with a backslash (\). Alternatively, enclose the character in quotation marks. For example, if the number format is (000), and you type 12 in the cell, the number (012) is displayed. |
| \ | Display the next character in the format. Excel 2007 does not display the backslash. For example, if the number format is 0\!, and you type 3 in the cell, the value 3! is displayed. |
| * | Repeat the next character in the format enough times to fill the column to its current width. You cannot have more than one asterisk in one section of the format. For example, if the number format is 0*x, and you type 3 in the cell, the value 3xxxxxx is displayed. The number of x characters that are displayed in the cell varies based on the width of the column. |
| _ (underline) | Skip the width of the next character. This is useful for lining up negative and positive values in different cells of the same column. For example, the number format _(0.0_);(0.0) aligns the numbers 2.3 and -4.5 in the column even though the negative number is enclosed by parentheses. |
| "text" | Display whatever text is inside the quotation marks. For example, the format 0.00 "dollars" displays 1.23 dollars when you type 1.23 in the cell. |
| @ | Text placeholder. If text is typed in the cell, the text from the cell is placed in the format where the at symbol (@) appears. For example, if the number format is "Bob "@" Smith" (including quotation marks), and you type John in the cell, the value Bob John Smith is displayed. |
| Date formats | Notes |
| m | Display the month as a number without a leading zero. |
| mm | Display the month as a number with a leading zero when appropriate. |
| mmm | Display the month as an abbreviation (Jan to Dec). |
| mmmm | Display the month as a full name (January to December). |
| mmmmm | Display the month as a single letter (J to D). |
| d | Display the day as a number without a leading zero. |
| dd | Display the day as a number with a leading zero when appropriate. |
| ddd | Display the day as an abbreviation (Sun to Sat). |
| dddd | Display the day as a full name (Sunday to Saturday). |
| yy | Display the year as a two-digit number. |
| yyyy | Display the year as a four-digit number. |
| Time formats | Notes |
| h | Display the hour as a number without a leading zero. |
| [h] | Elapsed time, in hours. If you are working with a formula that returns a time in which the number of hours exceeds 24, use a number format that resembles [h]:mm:ss. |
| hh | Display the hour as a number with a leading zero when appropriate. If the format contains AM or PM, the hour is based on the 12-hour clock. Otherwise, the hour is based on the 24-hour clock. |
| m | Display the minute as a number without a leading zero. |
| [m] | Elapsed time, in minutes. If you are working with a formula that returns a time in which the number of minutes exceeds 60, use a number format that resembles [mm]:ss. |
| mm | Display the minute as a number with a leading zero when appropriate. The m or mm must appear immediately after the h or hh symbol. Otherwise, Excel 2007 displays the month instead of the minute. |
| s | Display the second as a number without a leading zero. |
| [s] | Elapsed time, in seconds. If you are working with a formula that returns a time in which the number of seconds exceeds 60, use a number format that resembles [ss]. |
| ss | Display the second as a number with a leading zero when appropriate. If you want to display fractions of a second, use a number format that resembles h:mm:ss.00. |
| AM/PM, am/pm, A/P, a/p | Display the hour using a 12-hour clock. Excel 2007 displays AM, am, A, or a for times from midnight until noon and PM, pm, P, or p for times from noon until midnight. |
Displayed value versus stored value
Excel 2007 displays a number according to the format of the cell that contains the number. Therefore, the number that you see in the cell may differ from the number that is stored by Excel 2007 and from the number that is used in calculations that refer to the cell. For example, if you type 1.2345 in a cell in which you want only two digits to be displayed to the right of the decimal, the cell displays the value 1.23. However, if you use that cell in a calculation, all four digits to the right of the decimal are used. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
181918 Number formatting affects perceived precision in Excel for Mac
The Alignment tab
You can position text and numbers, change the orientation, and specify text control in cells by using the settings on the Alignment tab in the Format Cells dialog box.
Text alignment
Under Text alignment, you control the horizontal alignment, the vertical alignment, and the indention of the text in a cell. The following table lists the settings that are available for text alignment.
| Group | Setting | Description |
| Horizontal | General | Text data is left-aligned. Numbers, dates, and times are right-aligned. Changing the alignment does not change the type of data. |
| Left (Indent) | Aligns contents at the left edge of the cell. If you specify a number in the Indent box, Excel indents the contents of the cell from the left by the specified number of character spaces. The character spaces are based on the standard font and the font size that is selected on the General tab of the Options dialog box. | |
| Center | Centers the text in the selected cells. | |
| Right | Aligns contents at the right edge of the cell. | |
| Fill | Repeats the contents of the selected cell until the cell is full. If blank cells to the right also have the fill alignment, they are also filled. | |
| Justify | Aligns wrapped text in a cell to the right and left. You must have more than one line of wrapped text to see the justification. | |
| Center Across Selection | Centers a cell entry across the selected cells. | |
| Vertical | Top | Aligns cell contents along the top of the cell. |
| Center | Centers cell contents in the middle of the cell from top to bottom. | |
| Bottom | Aligns cell contents along the bottom of the cell. | |
| Justify | Justifies the cell contents up and down within the width of the cell. |
Text control
There are some additional miscellaneous text alignment controls in the Text Control section of the Alignment tab. These controls are Wrap Text, Shrink to Fit and Merge Cells.
Use Wrap Text to wrap the text in the selected cell. The number of wrapped lines depends on the width of the column and on the length of the cell contents.
Note To start a new line when you use the Wrap Text option, press ALT+ENTER while you type in the formula bar.
Use Shrink to Fit to decrease the font size of the text in a cell until all the contents of the cell can be displayed. This feature helps when you want to avoid changing the column width for the whole column. The applied font size is not changed.
Use Merge Cells to combine two or more selected cells into a single cell. A merged cell is a single cell that is created by combining two or more selected cells. The cell reference for a merged cell is the upper-left cell in the original selected range.
Orientation
You can set the text rotation in the selected cell in the Orientation section. Use a positive number in the Degree box to rotate the selected text from lower-left to upper-right in the selected cell. Use negative degrees to rotate text from upper-left to lower-right in the selected cell.
To display text vertically from top to bottom, click Text under Orientation. This gives a stacked appearance to text, numbers, and formulas in the cell.
Right-to-left
To have text read from right-to-left in a cell, click Right-to-left in the Text direction list.
To have text read from left-to-tight in a cell, click Left-to-right in the Text direction list.
To have the text direction determined contextually, click Context in the Text direction list.
The Font tab
The term "font" refers to a typeface (for example, Arial) together with the attributes of the typeface (point size, font style, underlining, color, and effects). Use the settings on the Font tab in the Format Cells dialog box to control these settings. You can see a preview of your settings by viewing the Preview section of the dialog box.
Note You can use the settings on the Font tab to format individual characters. To do this, select the characters in the formula bar, and then click the Format Cells Dialog Box Lanchaer in the Font group on the Home tab.
Font, font style, and font size
The Font option on the Font tab lets you choose a font. To choose the font for the selected cell, click a name in the Font list. Alternatively, type a name in the Font box. There are three types of fonts that you can use. These types are described in the following table.
| Font type | Icon (left of the name) | Description (bottom of the dialog box) |
|---|---|---|
| TrueType | TT | The same font is used for screen display and for printing. |
| Screen Display | None | This font is installed for screen display only. The closest available font is used for printing. |
| Printer | Printer | This is a printer-resident font. What is printed may not match exactly what is on the screen. |
After you select a font in the Font list, the Size list displays the available point sizes. You can also type the font size that you want to use. Each point is approximately 1/72 of an inch.
Font styles
The choices that are available in the Font Style list vary depending on the font that is selected in the Font list. Most fonts include the following styles:
- Regular
- Italic
- Bold
- Bold italic
Underline
In the Underline list, you can select an underlining option to format the selected text. The following table describes each underlining option.
| Underline type | Description |
|---|---|
| None | No underlining is applied. |
| Single | A single underline is added under each character in the cell. The underline is drawn through the descenders of characters such as g and p. |
| Double | Double underlines are added under each character in the cell. The underlines are drawn through the descenders of characters such as g and p. |
| Single Accounting | A single underline is added across the width of the cell. The underline is drawn under the descenders of characters such as g and p. |
| Double Accounting | Double underlines are added across the width of the cell. The underlines are drawn under the descenders of characters such as g and p. |
Color, effects, and normal font settings
To choose a color for the font, click a color in the Color list. You can rest the mouse pointer over a color to see a ToolTip that contains the name of the color. The Automatic color is always black unless you change the font color on the Appearance tab of the Display Properties dialog box. To open the Display Properties dialog box, double-click the Display icon in Control Panel.
Click to select the Normal font check box to set the font, font style, size, and effects to the normal style. This resets the cell formatting to the default formatting.
Click to select the Strikethrough check box to draw a line through the selected text or numbers. Click to select the Superscript check box to format the selected text or numbers as superscripts. Click to select the Subscript check box to format the selected text or numbers as subscripts. Typically, you may want to use subscripts and superscripts for individual characters in a cell. To do this, select the characters in the formula bar, and then click the Format Cells Dialog Box Lanchaer in the Font group on the Home tab.
The Border tab
In Excel 2007, you can put a border around a single cell or around a range of cells. You can also have a line drawn from the upper-left corner of the cell to the lower-right corner or from the lower-left corner of the cell to the upper-right corner.
To customize the borders of these cells from their default settings, change the line style, the line thickness, or the line color.
The following settings are available on the Border tab of the Format Cells dialog box.
| Group | Setting | Description |
|---|---|---|
| Presets | None | Turns off all borders that are currently applied to the selected cells. |
| Outline | Places a border on all four sides of a single cell or around a selected group of cells. | |
| Inside | Places a border on all interior sides of a group of selected cells. This button is unavailable (dimmed) if a single cell is selected. | |
| Border | Top | Applies a border with the selected style and color to the top of the cells in the selected region. |
| Inside Horizontal | Applies a border with the selected style and color to all horizontal sides in the interior of the selected group of cells. This button is unavailable (dimmed) if a single cell is selected. | |
| Bottom | Applies a border with the selected style and color to the bottom of the cells in the selected region. | |
| Diagonal (lower-left to upper-right) | Applies a border with the selected style and color from the lower-left corner to the upper-right corner for all cells in the selection. | |
| Left | Applies a border with the selected style and color to the top of the cells in the selected region. | |
| Inside Vertical | Applies a border with the selected style and color to all vertical sides in the interior of the selected group of cells. This button is unavailable (dimmed) if a single cell is selected. | |
| Right | Applies a border with the selected style and color to the right side of the cells in the selected region. | |
| Diagonal (upper-left to lower-right) | Applies a border with the selected style and color from the upper-left corner to the lower-right corner for all cells in the selection. | |
| Line | Style | Applies the selected line style to the border. Choose from dotted, dashed, solid, and double border lines. |
| Color | Applies the specified color to the border. |
How to apply borders
To add a border to a single cell or to a range of cells, follow these steps:
- Select the cells that you want to format.
- On the Home tab, click the Format Cells Dialog Box Lanchaer in the Font group.
- In the Format Cells dialog box, click the Border tab.
Note Some buttons on the Border tab are unavailable (dimmed) when only a single cell is selected. This behavior occurs because these settings are only applicable when you apply borders to a range of cells.
- Click any one of the line styles in the Style list.
- Click the Color arrow, and then click any one of the colors.
- Click any one of buttons that are listed under Presets or Border. This displays a line with your settings in the sample region.
- If you want to remove a specific border, click the button for that border again.
- If you want to change the line color or style, click the style or color that you want, and then click the button for the border again.
The Fill tab
Use the settings on the Fill tab to set the background color of the selected cells. You can also use the Pattern Color and the Pattern Style lists to apply two-color patterns or shading for the background of the cell. Fill Effects lets you apply a gradient fill to the background of the cell.
To shade cells with patterns, follow these steps:
- Select the cells that you want to shade.
- Right-click within the range of cells that you have selected, and then click Format Cells.
- On the Fill tab, click a color in the Background Color palette to include a background color for the pattern.
- Click a color in the Pattern Color list, and then click the pattern style that you want from the Pattern Style list.
If you do not select a pattern color, the pattern is black.
To select a custom color, click More Colors, and then select a color from either the Standard tab or the Custom tab.
To return the background color formatting for the selected cells to the default state, click No Color.
The Protection tab
The Protection tab provides the following settings that you can use to protect your worksheet data and formulas:
- Locked
- Hidden
However, neither of these options takes effect unless you also protect your worksheet. To protect a worksheet, on the Review tab, click Protect Sheetin the Changes group.
Locked
By default, all cells in a worksheet have the Locked option enabled. When this option is enabled and the worksheet is protected, you cannot do the following:
- Change the cell data or formulas.
- Type data in an empty cell.
- Move the cell.
- Resize the cell.
- Delete the cell or its contents.
Note If you want to be able to type data in some cells after you protect the worksheet, make sure that you click to clear the Locked check box for those cells.
Hidden
By default, all cells in a worksheet have the Hidden option disabled. If you enable this option when the worksheet is protected, the formula in a cell does not appear in the formula bar. However, you do see the results of the formula in the cell.
Important The Locked and Hidden settings enable specific collaboration scenarios to function correctly in collaboration environments that do not include users who have malicious intent. You cannot enable a strong encryption file by using these settings.
To protect the document or the file from a user who has malicious intent, use Information Rights Management (IRM) to set permissions that will protect the document or the file.
For more information about the Office features that help enable collaboration, click the following article number to view the article in the Microsoft Knowledge Base:
822924 Description of Office features that are intended to enable collaboration and that are not intended to increase security
For more information about how to restrict permissions to content by using IRM, visit the following Microsoft Web site:
Keywords: kbhowto kbinfo kbformat kbexpertisebeginner KB922069
