Troubleshoot formatting worksheets

On the Tools menu, click Options, click the Edit tab, and then clear the Fixed Decimal check box.

To remove decimal points from numbers you've already entered, you can multiply the numbers by a power of 10. In an empty cell, enter a number such as 10, 100, or 1,000, depending upon the number of decimal places you want to remove. For example, enter 100 in the cell if the numbers contain two decimal places and you want whole numbers. Copy the cell to the Clipboard and select a range of adjacent cells that contain numbers with decimal places. On the Edit menu, click Paste Special, and then click Multiply.

If the numbers are aligned to the left of the cell and if you have not changed the default alignment (General), the numbers are formatted or entered as text. To change them to numbers, do the following:

1. Select a blank cell that you know has the General number format.

   If you aren't sure of the cell format, click Cells on the Format menu, and then click the Number tab. In the Category box, click General, and then click OK.

2. In the cell, type 1 and then press ENTER.
3. Click the cell, and then click Copy on the Standard toolbar.
4. Select the range of cells that contain the "text" numbers.
5. On the Edit menu, click Paste Special, click Multiply, and then click OK.

The number format applied to a cell determines the way Microsoft Excel displays a number in that cell on the worksheet. The format does not affect the cell value used in calculations, which is displayed in the formula bar when the cell is active.

To remove number formats that may affect the displayed value, select the cells.

1. On the Format menu, click Cells, and then click the Number tab.

2. In the Category box, click General.

The number of custom number formats has been exceeded. You must delete one or more of the existing custom number formats in order to add new ones.

1. On the Format menu, click Cells, and then click the Number tab.

2. In the Category list, click Custom.

3. At the bottom of the Type box, click the custom format you want to delete. Click Delete.

If conditional formats have been applied to a cell, Microsoft Excel applies the formats depending on whether the cell value or other evaluated data meets a condition you specified.

• If you base the formatting condition on the value of the selected cells, cells that contain text are evaluated as ASCII strings. Formats such as bold or the font color may change if you change the text.
• Conditional formats have priority over formats that you apply directly to a cell by using the Cells command (Format menu) or a Formatting toolbar button. If a specified condition is true, the formats for that condition override the manual formatting.

If you've saved a workbook in another file format, the rotated text format might be lost. Most file formats do not support rotation within the full 180 degrees (+90 through – 90 degrees), which is possible in the current version of Microsoft Excel. Earlier versions of Excel can rotate text only at angles of +90, 0 (zero), or – 90 degrees. If the specified angle of rotation cannot be maintained in the other file format, the text is not rotated.

Reset the number format    The cells may be formatted so that data in the cells is hidden. To remove specific number formats that may be affecting the displayed value, select the cells. On the Format menu, click Cells, and then click the Number tab. In the Category list, click General.

Check the color of text and the background    If text in a cell is the same color as the background, nothing appears in the cell. You can change the color of the background or change the color of the text.

Microsoft Excel cannot rotate indented cells or cells that are formatted with the Center Across Selection or Fill alignment options in the Horizontal box of the Alignment tab (Cells command, Format menu). If all selected cells have these conflicting alignment formats, the text rotation options under Orientation are not available. If the selection includes cells that are formatted with other, non-conflicting alignment options, the rotation options are available. However, cells formatted with a conflicting alignment format are not rotated.

These characters are formatting codes, which may be displayed in documents created in Lotus 1-2-3 or if you are working with keys that are used for navigation in Lotus 1-2-3. In Lotus 1-2-3 formats, an apostrophe (') indicates left-aligned text, a quotation mark (") indicates right-aligned text, a caret (^) indicates centered text, and a backslash (\) indicates text that is formatted to fill the cell.

To hide formatting codes in the formula bar, click Options on the Tools menu, click the Transition tab, and then clear the Transition navigation keys check box.

Note  You can only hide codes that have been implicitly added, such as formatting codes. You cannot hide codes that have been explicitly added.

• Check for multiple conditions    If you specify multiple conditions and more than one condition is met, Microsoft Excel applies only the formats for the first true condition.
• See whether conditions overlap    If you specify conditions that overlap, Excel applies only the format of the first true condition. For example, if you specify a condition that applies yellow shading to cell values between 100 and 200, inclusive, and then you specify a second condition that applies red shading to cell values below 120, the cell values of 100 through 119.999999999999 will appear with yellow shading. It’s best to avoid overlapping conditions.
• Check cell references    If you used a formula as the formatting criteria, the cell references in the formula might not be correct.

The current selection might include cells that have two or more types of conditional formatting. The dialog box can display only one set of conditional formatting criteria and cell formats at a time.

Select different cells and check the settings again.

Cell formats that can change the row height or column width cannot be used as conditional formats.

Do not use the diagonal borders buttons and in the Border tab (Cells dialog box, Format menu) to apply borders to cells that contain rotated text. Remove the diagonal borders by selecting the cells and clicking None on the Border tab under Presets. Then click the Inside and Outline buttons under Presets. The borders are applied to the edges of the cells, which are rotated to the same degree as the rotated text.

Look at adjacent cells    If you apply borders to a selected cell, the border is also applied to adjacent cells that share a bordered cell boundary. For example, if you apply a box border enclosing the range B1:C5, the cells D1:D5 acquire a left border.

Check which border was last applied    If you apply two different types of borders to a shared cell boundary, the most recently applied border is displayed.

Choose the appropriate border type    A selected range of cells is formatted as a single block of cells. If you apply a right border to the range of cells B1:C5, the border is displayed only on the right edge of the cells C1:C5. To display interior borders, use the button on the Borders palette.

See if borders are separated by a page break    If you want to print the same border on cells that are separated by a page break, but the border appears on only one page, use the Cells command (Format menu) to apply an inside border. For example, suppose you want to print a border at the bottom of the last row of one page and to use the same border at the top of the first row on the next page. Select the rows on both sides of the page break, and then click Cells on the Format menu. Click the Border tab, and then click the Inside button under Presets. Under Border, remove the vertical border by clicking it in the preview diagram.