Move Formulas Canceling the Location Adjustment

BY STANLEY ZAROWIN


Excel is just too clever sometimes, especially when it second-guesses me—and, of course, it often guesses wrong. For example, when I copy a formula and enter it in a different cell, Excel decides to do me a favor and adjust the formula references so that it’ll now work as it did before—but now in the new cell. That’s not what I want. I want Excel to leave the references as they were. Do I have to go through the trouble of surrounding the references with dollar signs to make them absolute so they’ll stay put?

Excel takes loads of abuse for guessing wrong. And while some of the complaints are justified, quite often it’s the user who is at fault. In most cases when you copy a formula to a new location, you want Excel to automatically adjust the cell references; it’s a great convenience because you don’t have to go through the trouble of adjusting each reference manually. But when you want to keep the references absolute, there’s an easier way than using dollar signs: Copy the formula as text and then paste it to the new location. Here’s how:

Click on the cell with the formula you want to copy and press F2. That will convert the formula into text that will be visible in the cell. Now click and highlight the entire formula, go to Edit on your toolbar, click on Copy and press Enter. Then select the cell where you want to paste the formula, return to Edit and click on Paste . You also can copy just a part of a formula.

If you need to perform this function often, I suggest you use the Office Clipboard to speed up the task. If the Clipboard icon is not in your toolbar, you’ll find it under Tools , Customize (see screenshot below). Just drag the icon up to the toolbar.

Now when you need to copy a formula without absolute references, place your cursor in the cell with the formula and copy the image that appears in the Formula bar ( fx ) —not in the cell (see screenshot below).

That action pastes the formula in the Clipboard (see below).

You then can paste the formula wherever you please.

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.