Tip # 30 Customize defaults

Excel uses template files to control default settings in new workbooks and sheets. For most of us, the settings are adequate. However, if you find yourself resetting the same defaults for each new workbook or sheet, consider changing the defaults permanently.

To change default settings for a workbook, open Book.xlt, make changes, and then save the file. Don't change the file's name; you're just updating it. (It's a good idea to create a copy of the original Book.xlt so you can revert to Excel's original settings if necessary. Name the copy BookOriginalSettings.xlt or something similarly descriptive.) After changing Book.xlt, all new workbooks will use the custom settings you applied. To change a sheet's default settings, open Sheet.xlt, make the necessary changes, and save it.

If you don't have one or both files, simply create your own. Just be sure to save them in Excel's XLStart folder (\Program Files\Microsoft Office\XLStart).

Tip # 29 Quick copy to noncontiguous cells

Copying data or a formula is simple. You just drag the source cell's fill handle and Excel copies the data or formula from the source cell to the cells you select using the fill handle. But copying isn't always a nice, neat, contiguous package. Sometimes you need to copy data or a formula to a series of noncontiguous cells. You could paste the source data into each individual cell, but that's the hard way. Instead, you can copy data into a noncontiguous block.

First, copy the source data. Then, hold down the Ctrl key while you click each cell in the noncontiguous destination range. Once you've highlighted each target cell, press Ctrl+V, and Excel will copy the source data into each of the highlighted cells. Formulas copied this way obey referencing rules, in regard to absolute and relative addresses.

An alternate method is to right-click in the cell that contains data you want to copy and choose Copy from the resulting submenu. Then, right-click a destination cell and choose Paste. At this point, the source cell is still highlighted, which means you can copy the contents again. Right-click another destination cell and choose Paste. Continuing selecting destination cells until you've completed the copy task. Press Esc to clear the selection of the source cell.

Tip # 28 Exploit defined names

Defined names aren't just for ranges. You can use a defined name to define a constant value, such as a discount amount. Use the feature as you normally would, entering the literal value or expression that evaluates to the desired value into the named cell. For instance, select a cell and choose Name from the Insert menu. Then, select Define. Enter the descriptive name Discount and click OK. Now, in the same cell, enter the actual discount amount, say 3 percent (just enter .03). Now, you can use the defined name, Discount, in your formulas instead of entering the literal value .03. For instance, Excel would use .03 for Discount when evaluating the following formula:

=TotalPrice - (TotalPrice * Discount)

This quick tip has two benefits: It makes updating much simpler, as you can quickly change the value in Discount and Excel will automatically update all dependent formulas. And it eliminates data entry errors.

Tip # 27 Customize your autofills.

If you use the same list over and over in different worksheets, you might want to add it to your AutoFill list – this will save you heaps of time in future. Highlight your list, click on Tools, Options… and select the Custom Lists tab. Click on Import, then OK.

Tip # 26 Use the formula browser

Select a cell and click on the Paste Function button on the main tool bar.Pick the function you require from the list box and click on OK. Now highlight the cells on which you want the target to perform the function and click on OK.
The Paste Function button enables you to browse formulas and is the easiest way to enter Functions.