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 REPORT

Keeping client information safe in an age of scams and security threats

A look at the Dirty Dozen tax scams and ways to protect taxpayer information.

TECHNOLOGY Q&A

How to create maps in Excel 2016

Microsoft Excel 2016 has two new mapping capabilities. J. Carlton Collins, CPA, demonstrates how to make masterful 2D and 3D maps in Excel 2016.

QUIZ

News quiz: IRS enforcement, a hot job, and audit value

The IRS’s 2016 Data Book, a “hot job” of particular interest at this time of year, and insight into how executive and audit committees view the insights from financial statement audits received attention recently. See how much you know with this short quiz.