Five Excel Quick Tips

1. Sticky format painter. Double-clicking on the Format Painter icon will cause it to stick, so that you can continue to apply the desired format to multiple cells, ranges, columns or worksheets. To turn off this effect, either press the Esc key or click the Format Painter icon.

2. Absolute/relative referencing. Pressing the F4 key while in edit mode will insert absolute dollar sign references, which is an easier method than manually typing in the absolute dollar sign references. To do this, select a cell that contains a formula, and press the F2 key to enter edit mode. Within the formula, position the cursor over a cell reference and press the F4 key to insert absolute references. Before and after examples are shown below.

(Note: Pressing the F4 key repeatedly will toggle you through the available absolute reference options — column only; row only; both column and row; or none.)

3. Hide zero values. You can hide all of the zero values in a worksheet by adjusting Excel’s options as follows:

a. In Excel 2003, select Tools, Options. On the View tab, uncheck the Zero values box and click OK.

b. In Excel 2007, select the Office Button, Excel Options, Advanced. Under the Display options for this worksheet section, uncheck the box labeled Show a zero in cells that have zero value and click OK.

c. In Excel 2010, select File, Options, Advanced. Under the Display options for this worksheet section, uncheck the box labeled Show a zero in cells that have zero value and click OK.

4. Display zero values as a dash. You can display zero values in your worksheet as a dash by changing the cell’s format to either the Accounting or Comma Style format.

5. Black parenthesis. When building a complex formula that contains nested functions or multiple sets of parenthesis, Excel uses colors to help you identify parenthesis pairing, and the outside parenthesis are always black. This tip can help you determine whether you have inserted the proper number of parenthesis pairs in your formula.