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).

CHECKLIST

Boost your LinkedIn profile

LinkedIn is No. 1 when it comes to business-related social media. Be prepared when an unexpected career opportunity arises by following these tips for keeping your LinkedIn page current.

PRACTICE MANAGEMENT

Millennial women: How to woo a new generation of employees

Ambitious, educated, and tech-savvy, Millennial women make ideal employees. Win them over with flexibility and clear paths to advancement.

PROFESSIONAL ISSUES

Belicia Cespedes: A CPA at 17

Through hard work and determination, Belicia Cespedes earned the credential before she was even eligible to vote.