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

Questions to ask before committing to the cloud

Cloud computing has its pros and cons. In this report, we answer common questions CPAs may have as they consider transitioning partially or fully to the cloud.

QUIZ

News quiz: Experts offer guidance on accounting standards

Take this short quiz to see how much you know about the news, including a couple of SEC announcements, and facts cited in the guidance experts have offered on accounting standards.

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.