Troubleshoot entering and selecting data

Number formats    Microsoft Excel displays numbers, dates, and times on a worksheet according to the number format applied to the cells. To change the number format, select the cells. On the Format menu, click Cells, click the Number tab, and then click the category and format you want. To use the default date format, click the cell and press CTRL+SHIFT+#. To use the default time format, press CTRL+SHIFT+@.

Regional settings    Options you select in the Regional Settings of Control Panel determine the default format for currency, dates, times, and numbers.

A ##### error value occurs when the cell contains a number, date, or time that is wider than the cell or when the cell contains a date or time formula that produces a negative result. Try increasing the width of the column.

When a date is entered in a cell, the date appears in either a default date format or in a format that was applied to the cell before the date was entered. The default date format is based on settings on the Date tab in the Regional Settings dialog box (Microsoft Windows Control Panel). If these date settings have changed, any existing dates in your workbooks that have not been formatted with the Format Cells command also change.

To display multiple lines of text inside selected cells, click Cells on the Format menu, click the Alignment tab, and then select the Wrap text check box. If the text is a single long word, the characters won't wrap; you can widen the column or shrink the font size to see all the text.

Make sure AutoComplete is turned on    On the Tools menu, click Options, click the Edit tab, and then make sure the Enable AutoComplete for cell values check box is selected.

Check where you last clicked    Click after the last character in the cell. Excel completes an entry only when the insertion point is at the end of the current cell contents.

Make sure entries match    The characters you typed might not match an existing entry in the column, or the characters might match two or more entries. For example, if you type AXY and the possible entries are "AXY1" and "AXY2," Microsoft Excel does not complete the entry.

Select the correct entry    To select the entry you want, press ALT+DOWN ARROW and select an entry from the list, or right-click the cell and then click Pick from list on the shortcut menu.

Entries in a row    Excel bases the list of potential entries on the column that contains the active cell. If your entries are repeated within a row, not a column, the entries are not completed.

Numbers, dates, and times    Excel completes only text entries. Entries that contain only numbers, dates, and times are not completed.

The current data region    Check the location of the active cell; it may be outside the current data region. For example, if the data region is the range of cells A1:E10, Excel completes an entry typed in cell A11, even if the cell A10 above it is blank. However, Excel does not complete an entry in cell A12, because that cell is outside the data region.

Long lists    If you are working in a large list, you may need to pause a moment to allow Excel time to complete an entry.

On the Tools menu, click Options, click the Edit tab, and then make sure the Allow cell drag and drop check box is selected. If the entire row or column is selected, Excel displays the fill handle at the beginning of the row or column.

If you copy types of data that Excel can extend in a series, such as numbers, dates, or items in a custom fill series, the values are incremented instead of copied through the selected range. If this occurs, select the original values again, and then hold down CTRL as you drag the fill handle.

If you drag the fill handle up or to the left of a selection and stop in the selected cells without going past the first column or the top row in the selection, Excel deletes data within the selection. You must drag the fill handle out of the selected area before releasing the mouse button.

If you've already entered the numbers and applied the Text format to the cells, click each cell, press F2, and then press ENTER to reenter the numbers as text. To enter numbers as text, first apply the Text format to empty cells, and then enter the numbers in the formatted cells.

Display or hide all sheet tabs

1. On the Tools menu, click Options, and then click the View tab.
2. Select or clear the Sheet tabs check box. When displaying sheet tabs, you may also need to expand the tab scrolling area.

   

Display more or fewer sheet tabs

1. Point to the tab split bar.

   

2. When the pointer changes to a split pointer , drag the tab split bar to the right or left.

   To return the tab split bar to its original position, double-click the tab split bar.

   Tip

   You can activate a sheet by right-clicking any tab scrolling button and then selecting the sheet you want from the shortcut menu.

If the sheet names are cut off or difficult to read

The keys may be performing the equivalent action in Lotus 1-2-3. On the Tools menu, click Options, and then click the Transition tab. Clear the Transition navigation keys check box, and then try to use the keys again.

If you want the active selection to move as you scroll through a worksheet, make sure scroll lock is off. If scroll lock is on, you can use the navigation keys without affecting the current selection.

Make sure that End mode is off. END appears in the status line when the End mode is on.

When you enter or change data, the changes affect all selected sheets. These changes may replace data on the active sheet and other selected sheets.

To select	Do this
A single sheet	Click the sheet tab. If you don't see the tab you want, click the tab scrolling buttons to display the tab, and then click the tab.
Two or more adjacent sheets	Click the tab for the first sheet, and then hold down SHIFT and click the tab for the last sheet.
Two or more nonadjacent sheets	Click the tab for the first sheet, and then hold down CTRL and click the tabs for the other sheets.
All sheets in a workbook	Right-click a sheet tab, and then click Select All Sheets on the shortcut menu.

Note  If sheet tabs have been color-coded, the sheet tab name will be underlined in a user-specified color when selected. If the sheet tab is displayed with a background color, the sheet has not been selected.

Cancel a selection of multiple sheets

Hold down SHIFT and click the last cell you want to include in the new selection. The rectangular range between the active cell and the cell you click becomes the new selection.

To select additional nonadjacent cells or cell ranges, hold down CTRL and click or drag over the cells.

If "EXT" appears in the status bar, press F8 to turn off the extend-selection option.

If you've recently used the Copy command on the Edit menu or the Copy button , check if a moving border still surrounds the copy area. If so, Microsoft Excel inserted them at the active cell when you pressed ENTER. To remove the moving border, press ESC.