How To Delete Cells Without Changing The Reference In Excel

Excel Tips
Excel Functions
Excel Formulas
Excel Charts
Give-and-take Tips
Outlook Tips

How to copy formula without changing its cell references in Excel?

Unremarkably Excel adjusts the cell references if you copy your formulas to some other location in your worksheet. Yous would have to fix all cell references with a dollar sign ($) or press F4 key to toggle the relative to absolute references to prevent adjusting the jail cell references in formula automatically. If you accept a range of formulas demand to exist copied, these methods will be very tedious and fourth dimension-consuming. If you want to copy the formulas without changing prison cell references quickly and easily, try the post-obit methods:

Method ane: by Supercede characteristic
Method 2: by converting formula to text
Method 3: by converting to absolute references
Method iv: by Verbal Copy feature

Copy formula without changing its cell references by Replace characteristic

In Excel, you can copy formula without irresolute its cell references with Supervene upon function as post-obit steps:

1. Select the formula cells y'all volition copy, and click Abode > Discover & Select > Replace, or press shortcuts CTRL+H to open the Discover & Select dialog box.

2. Click Replace push, in the Find what box input " = ", and in the Supervene upon with box input " # " or any other signs that different with your formulas, and click the Replace All button.
Basically, this volition stop the references from being references. For instance, " =A1*B1 " becomes " #A1*B1 ", and you can motion it around without excel automatically changing its cell references in electric current worksheet.

3. And at present all " = " in selected formulas are replaced with " # ". And a dialog box comes out and shows how many replacements have been made. Delight close it. See above screenshot:
And the formulas in the range will be changed to text strings. See screenshots:

4. Copy and paste the formulas to the location that you want of the electric current worksheet.

v. Select the both inverse ranges, and so contrary the step 2. Click Home> Detect & Select >Replace… or printing shortcuts CTRL+H, but this fourth dimension enter " # " in the Notice what box, and " = " in the Replace with box, and click Replace All. And then the formulas have been copied and pasted into another location without irresolute the cell references. See screenshot:

Copy formula without changing its prison cell references by converting formula to text

Higher up method is to change the formula to text with replacing the = to #. Actually, Kutools for Excel provide such utilities of Convert Formula to Text and Convert Text to Formula. And you tin can convert formulas to text and re-create them to other places, and then restore these text to formula easily.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full characteristic costless trial 30-day, no credit menu required! Gratis Trial Now!

ane. Select the formula cells yous will copy, and click Kutools > Content > Convert Formula to Text. Meet screenshot:

two. Now selected formulas are converted to text. Delight re-create them and paste into your destination range.

3. And then you can restore the text strings to formula with selecting the text strings and clicking Kutools > Content > Convert Text to Formula. See screenshot:

Kutools for Excel - Includes more 300 handy tools for Excel. Full characteristic free trial thirty-mean solar day, no credit card required! Get Information technology Now

Copy formula without irresolute its prison cell references by converting to absolute references

The formulas changes later on copying as a result of relative references. Therefore, we tin apply Kutools for Excel's Catechumen Refers utility to change the cell references to absolute to preclude from changing subsequently copying in Excel.

Kutools for Excel - Includes more than 300 handy tools for Excel. Total feature free trial 30-twenty-four hours, no credit carte du jour required! Free Trial Now!

ane. Select the formula cells you will copy, and click Kutools > Convert Refers.

ii. In the opening Convert Formula References dialog box, please check the To absolute pick and click the Ok button. Meet screenshot:

3. Copy the formulas and paste into your destination range.

Notation: If necessary, you can restore the formulas' prison cell references to relative by reusing the Convert Refers utility once again.

Kutools for Excel - Includes more than than 300 handy tools for Excel. Full feature costless trial xxx-twenty-four hours, no credit card required! Get Information technology Now

Re-create formula without irresolute its jail cell references by Kutools for Excel

Is there an easier way to re-create formula without changing its cell references this apace and comfortably? In bodily, Kutools for Excel tin help yous copy formulas without changing its cell references chop-chop.

Kutools for Excel - Includes more than 300 handy tools for Excel. Total feature gratis trial thirty-day, no credit menu required! Free Trial At present!

1. Select the formula cells you will copy, and click Kutools > Verbal copy.

2. In the starting time Exact Formula Copy dialog box, please click OK. And in the second Exact Formula Copy dialog box, please specify the showtime cell of destination range, and click the OK push button. See screenshot:

Tip: Copy formatting pick will keep all cells formatting later on pasting the range, if the pick has been checked.
And all selected formulas accept been pasted into the specified cells without changing the jail cell references. Meet screenshot:

Kutools for Excel - Includes more than 300 handy tools for Excel. Total feature complimentary trial 30-day, no credit card required! Get It Now

Demo: copy formulas without changing cell references in Excel

Kutools for Excel includes more than than 300 handy tools for Excel, costless to try without limitation in 30 days. Download and Costless Trial Now!

The Best Part Productivity Tools

Kutools for Excel Solves Nearly of Your Bug, and Increases Your Productivity by 80%

Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and transport emails...
Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (hands read and edit large numbers of cells); Paste to Filtered Range...
Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Indistinguishable Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Observe and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without irresolute formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove past Position, Remove Space; Create and Print Paging Subtotals; Convert Betwixt Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Avant-garde Sort by month/week/24-hour interval, frequency and more; Special Filter by assuming, italic...
Combine Workbooks and WorkSheets; Merge Tables based on fundamental columns; Split up Information into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Total features thirty-day free trial. 60-day money back guarantee.

kte tab 201905

Role Tab Brings Tabbed interface to Office, and Brand Your Work Much Easier

Enable tabbed editing and reading in Give-and-take, Excel, PowerPoint , Publisher, Admission, Visio and Projection.
Open and create multiple documents in new tabs of the aforementioned window, rather than in new windows.
Increases your productivity by 50%, and reduces hundreds of mouse clicks for you lot every 24-hour interval!

officetab bottom