Choosing Editing Options

To control how Excel behaves when you're editing your worksheets, display the Edit preferences pane (see Figure 2–4) and choose options like this:

• Double-click allows editing directly in the cell. Select this check box if you want to be able to double-click a cell to open it up for editing. This is usually helpful—but if you clear this check box, double-clicking a cell makes Excel select the first cell referenced by the formula in that cell. This capability can be great when you're checking through formulas, so you need to decide which of the two double-click behaviors will be most helpful to you.
  

  Figure 2–4. The Edit preferences pane contains various important settings you should understand, even if you choose not to change them.

• Enable fill handle and cell drag-and-drop. Select this check box if you want to be able to use the AutoFill feature (explained in Chapter 1) for entering custom lists and text sequences, and to be able to use drag-and-drop to move the contents of one cell to another cell. If you select this check box, you can select the Alert before overwriting cells check box to have Excel display a warning dialog box when an AutoFill or drag-and-drop operation will overwrite the existing contents of cells.
• After pressing RETURN, move selection. Select this check box if you want Excel to automatically select another cell after you press Return to enter the contents of the active cell. This movement is usually helpful. In the Direction pop-up menu, choose the direction: Down, Up, Right, or Left. The default direction is Down.
• Extend data range formats and formulas. Select this check box to make Excel automatically extend formatting and formulas when you get to the end of the current data range and add another row or column. This behavior usually saves time and effort.
• Display this number of decimal places. If you want Excel to automatically insert a decimal point in numbers you enter in cells, select this check box, and use the spin buttons to set the number of decimal points. For example, if your standard format for numbers uses two decimal places, select this check box, and then enter 2 in the box. Then, when you type 123 in a cell, Excel automatically converts it to 1.23 without you having to type in the decimal point. You can set a negative number if you want Excel to multiply the number by that power of 10—for example, if you set -3, Excel multiplies each number by 1000, so if you enter 9, you get 9000. (You can set any value from -300 to 300, but you'll seldom need to go near these limits.)
• Preserve display of dates entered with four-digit years. Select this check box if you want Excel to maintain four-digit years (such as 2011 or 2012) that you type in your worksheets rather than converting the dates to two-digit years (for example, 10/2/11 for the date 10/2/2011 that you entered).
• Provide feedback with animation. Select this check box if you want Excel to animate the insertion or deletion of cells, rows, or columns in the worksheet. The animation makes each change easier to see, but it may make Excel run slowly on an underpowered Mac; if so, clear this check box.
• Ask to update automatic links. Select this check box if you want Excel to display a dialog box asking you for permission to update automatic links. Having this confirmation is good if you need to maintain control of automatic links rather than have Excel update them without consulting you.
• Cut, copy, and sort objects with cells. Select this check box if you want Excel to include objects (such as graphics and buttons) with cells you cut, copy, or sort. This is normal behavior.
• Automatically convert date system. Select this check box if you want Excel to automatically convert dates between the 1900 date system used by Excel for Windows and current versions of Excel:Mac and the 1904 date system used by most versions of Excel:Mac up till recently.

  NOTE: The 1900 date system uses the serial number 1 to represent 1 January 1900; the 1904 date system uses the serial number 1 to represent 1 January 1904. Unless you have a compelling reason to use the 1904 date system, stick with the 1900 date system for clarity. Excel stores dates and times as serial numbers so that it can perform math operations with them easily.

• Show Paste Options smart buttons. Select this check box if you want Excel to display a Paste Options action button after you paste in an item. You can click the Paste Options action button to display a pop-up menu of other ways of pasting in the data. This is usually helpful.
• Show Insert Options smart buttons. Select this check box if you want Excel to display an Insert Options action button when you insert cells, rows, or columns. You can click the Insert Options action button to display choices for formatting the items you've inserted. This too is usually helpful.