Make an Excel Formula Constant

BY STANLEY ZAROWIN

Q. Sometimes an Excel convenience can become a handicap. For example, if you move a formula to a new location, Excel will automatically change the formula to reflect the new location. But that automatic action can be a pain if you want the formula to remain constant—not to adjust to the new location. I know I can manually add $ symbols to the parts of the formula I want to remain constant, but if you have several formulas to move, that’s time-consuming. Any ideas on getting around this problem?

A. What you want is a command to get Excel to apply an absolute reference to the moved formula rather than a relative reference. Yes, you can do that.

But first, let’s back up a bit for readers who are unfamiliar with this technique. For example, if you have the formula below

and you move it to a different location, the references will change automatically to

But if you want it to reference A1 and A2 even after the move, you can change the formula to

by adding the $ symbols as shown.

An easier way to make the formula absolute is to double-click the cell that contains the original formula and then, with your mouse, highlight the formula, press F4 and then Enter. That will add the appropriate $ symbols in the right places to convert the formula into absolute referencing.

Shortcuts

Excel’s AutoSum automatically enters the sum of any column or row of values. Select the cell below or to the right of the values you’re summing and click the AutoSum button. You can even add multiple columns and rows of values at the same time by selecting contiguous cells and clicking AutoSum .

Keyboard’s AutoSum : If you’re fond of the keyboard, you can do the same thing by pressing Alt–= (that’s the Alt and the equal keys).

Find a page in Word: To jump quickly to a particular page in a lengthy Word document, press the F5 key, which launches the Select function, and type the page number you want.

Another way to evoke that screen with the mouse is to double-click on the page box in the lower left corner of the document window (the one that indicates page number).

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.