Five Excel Quick Tips

BY J. CARLTON COLLINS

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.

 

 

More from the JofA:

 

 Find us on Facebook      Follow us on Twitter

 

SPONSORED QUIZ

How well do you know small business?

There are over 30 million small businesses in the U.S., and many of them are optimistic in their outlook. Are you familiar with the obstacles and opportunities they are facing? Test your small business acumen with this quiz sponsored by Chase Ink®.

SPONSORED REPORT

In focus: Payroll

Providing payroll services that comply with ever-changing regulations and meet evolving employee and employer demands is no easy task. Paychex's Tom Hammond discusses common payroll considerations for CPA firms.