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

How to make the most of a negotiation

Negotiators are made, not born. In this sponsored report, we cover strategies and tactics to help you head into 2017 ready to take on business deals, salary discussions and more.

VIDEO

Will the Affordable Care Act be repealed?

The results of the 2016 presidential election are likely to have a big impact on federal tax policy in the coming years. Eddie Adkins, CPA, a partner in the Washington National Tax Office at Grant Thornton, discusses what parts of the ACA might survive the repeal of most of the law.

QUIZ

News quiz: Scam email plagues tax professionals—again

Even as the IRS reported on success in reducing tax return identity theft in the 2016 season, the Service also warned tax professionals about yet another email phishing scam. See how much you know about recent news with this short quiz.