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

Click-through nexus: Pushing the boundaries of sales tax compliance

Sales and use tax compliance has been complicated by nexus expansion. In this report, we provide an overview of this issue and include a handy state-by-state summary of click-through nexus or notification requirements.

QUIZ

News quiz: Making allowances for the kids and the economy

Recent news gives CPAs insight into Americans’ attitudes about children and money and gauges outlook on the economy. See how much you know about recent news and reports with this quiz.

CHECKLIST

Auditing risks in culture

Cultural flaws can seriously damage an organization. Here’s how internal auditors can reduce risks by embedding culture audits into existing audit programs.