Catalog

How to Copy Paste Exact Formula in Excel Without Changing Cell Reference

August 23, 2023 1.3K views

Excel is a powerful tool that can be used for a variety of tasks. One of its most essential features is its ability to use formulas to perform complex calculations. However, when you copy and paste a formula in Excel, the cell references often change, leading to incorrect results. In this article, we will explore various methods to copy and paste formulas without altering the cell references.

Part 1: 5 Easy Ways to Copy Paste Exact Formulas Without Changing Cell Reference in Excel.

Method 1: By Replace Feature

Step 1: Select the formula cells to be copied.Click Home > Find & Select > Replace, or press CTRL+H.

Step 2: Click Replace, enter "=" in the Find what box, and "#" (or a unique character) in the Replace with box. Click Replace All. This suspends references, like turning "=A2B2" into "#A2B2."

Ctrl + H


Step 3: Close the dialog box showing the replacements made. The selected formulas become text strings.

Close the dialog box


Step 4: Copy and paste the modified formulas to your desired location within the same worksheet.

Copy formulas


Step 5: To restore the formulas, select both the modified and original ranges. Repeat Step 2, but enter "#" in Find what and "=" in Replace with. Click Replace All.

restore the formulas

Method 2: By Exact Copy feature

If you simply have a few formulas to copy and paste without modifying the cell references, doing it manually is more efficient.

Step 1: Choose the cell containing the formula you want to copy.

Step 2: In the formula bar, copy the formula (or press F2 to enter edit mode, then copy the formula).

copy formula


Step 3: Select the target cell where you want to paste the formula, and paste it.

paste formula


Please keep in mind that this approach only works if you simply wish to copy formulae from a few cells.

Method 3: By Converting Formula to Text

Step 1: Use specialized tools like Kutools for Excel to achieve this method.

Step 2: Select the formula cells to copy, and go to Kutools > Content > Convert Formula to Text.

Select formula cell


Step 3: Copy the converted text-formulas and paste them where needed.

Copy converted text-formulas


Step 4: To revert back to formulas, select the text-formulas and click Kutools > Content > Convert Text to Formula.

revert back to formulas

Method 4: By Converting to Absolute References

Step 1: When copying formulas with regular references (like A1), change them to fixed references ($A$1) to keep the same cell. Or use mixed references ($A1 or A$1) to lock either the column or row.

Step 2: Example - Calculate EUR fruit prices based on USD prices in column B and exchange rate in cell C2.

Step 2


Step 3: To copy accurately, modify regular references to mixed ones. For instance, if moving EUR prices from column C to F, change B5 to $B5.

Secure references by adding the dollar sign ($) before the column letter. This ensures references stay consistent when copying or moving formulas.

Result


While Excel references might seem tricky, grasping them is worth it. Achieve efficient table calculations using mixed cell references..

Method 5: Using Notepad to Copy Paste Formulas

Step 1: In Excel, navigate to Formulas -> Show Formulas. This will show all of the formulae in the spreadsheet.

Show formula


Step 2: Copy the cells containing the formulas you want to replicate.

Step 3: Open a Notepad and paste the copied cell contents into it.

Step 4: Copy the content from Notepad and paste it into the cells where you wish to duplicate the exact formulas.

Step 5: Once finished, return to Formulas -> Show Formulas to revert to the normal view.

Part 2: A Comparison of 5 Methods - Which One Is Best for You?

When it comes to copying and pasting formulas without changing cell references in Excel, various methods offer different levels of convenience and ease. Let's compare the five methods discussed earlier to help you find the best fit:

Method 1: Replace Feature

  • Quick and involves temporary changes.

  • Simple for basic tasks.

  • Recommended for beginners due to its simplicity.

Method 2: Exact Copy Feature

  • Manual process but straightforward.

  • Suitable for smaller tasks.

  • Good for those who prefer manual control.

I've personally used this method for quick copying.

Method 3: Convert Formula to Text

  • Requires specific tools like Kutools.

  • Helpful for those comfortable with add-ins.

  • Might be useful if you're experienced with similar tools.

Method 4: Convert to Absolute References

  • Changes relative references to fixed ones.

  • Best for complex sheets and horizontal copying.

  • Recommended for advanced users or intricate spreadsheets.

Method 5: Use Notepad

  • Utilizes Notepad as an intermediary.

  • Offers flexibility but with more manual steps.

  • Useful if you're comfortable with manual processes.

Your choice depends on your familiarity with Excel and your specific needs. As a regular Excel user, I often find the "Replace Feature" to be the quickest and simplest method, especially for routine tasks. But feel free to explore these options and find the one that aligns best with your skills and requirements.

Part 3: Edit Your Spreadsheets for Free — WPS Office

WPS Office


WPS Office is a versatile free office suite that enables users to open, create, edit, and save Microsoft Office files, including Word documents, Excel spreadsheets, and PowerPoint presentations. This software is fully compatible with both Windows and Mac operating systems, providing a seamless experience for users across platforms.

Advantages of WPS Office:

  • Free: Access essential office tools like Word, Excel, and PPT without paid subscriptions.

  • Powerful PDF Toolkit: Edit, convert, and create PDFs alongside regular office features.

  • Rich Templates: Access a variety of templates for professional document creation.

  • Feature-Rich: Perform data analysis, graphical representation, and more.

  • Lightweight: Efficient multitasking and performance on various devices.

  • Online Document Support: Convenient storage and synchronization for collaboration.

  • Safe and Stable: Secure environment for document management.

FAQS

Why do cell references change when I copy and paste a formula?

Excel modifies cell references automatically to make calculations more versatile. This approach, however, might lead to issues when you need fixed references.

What are the cell references that don't change when you copy the formula to other cells called?

There are two sorts of cell references: relative and absolute. Relative and absolute references behave differently when copied and filled into other cells. The relative references alter when a formula is moved to another cell. Absolute references, on the other hand, do not change regardless of where they are duplicated.

Summary

In summary, this guide has provided insights into several methods to copy and paste Excel formulas without altering cell references. Each method offers its advantages, catering to different user preferences and levels of expertise. As you explore these techniques, keep in mind the diverse range of options available to enhance your Excel workflow.

Moreover, we've introduced WPS Office as a versatile office suite that supports various document tasks. With its compatibility across platforms, efficient PDF toolkit, array of templates, and user-friendly features, WPS Office stands as a valuable choice for managing your documents. By considering these methods and incorporating WPS Office, you can elevate your Excel practices and streamline your document handling processes.


15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.