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

8 accounting and auditing issues you can’t afford to ignore

Download this sponsored report for an overview of some of the most important developments accountants and auditors are facing today.

DOWNLOAD

Filing season quick guide — Tax year 2014

Tax season started Jan. 20. Download our “quick guide,” a printable card that contains dollar thresholds, tax tables, standard amounts, credits, and deductions to keep at your fingertips during tax season.

TAX NEWS

Expired tax provisions extended for 2014

President Barack Obama signed legislation that retroactively extended more than 50 expired tax provisions for 2014, allowing taxpayers to take advantage of a host of tax incentives during this filing season.