I ve got a massive Excel 2003 spreadsheet I m working on There are a lot of very large formulas with a lot of cell references Here s a simple example Sheet AC69 Sheet AC52 Sheet AC53 D 3 Sheet AC49 Most of them are more complicated than that but this gives a good idea of what I m working with Few of these cell references are absolute s I d like to be able to copy these cells to a different location without the cell references changing I know I can simply use f4 to make the references absolute but there is a lot of data and I may need to use Fill later Is there any way to temporarily disable the cell reference changing on copy-paste fill without making the references absolute EDIT I just found out that you can do this with VBA by copying the cell contents as text instead of a formula I d like to not have to do this though because I want to copy whole rows columns at once Is there a simple solution I am missing

From http spreadsheetpage com index php tip making an exact copy of a range of formulas take 2 Put Excel in formula view mode The easiest way to do this is to press Ctrl that character is a backwards apostrophe and is usually on the same key that has the tilde Select the range to copy Press Ctrl C Start Windows Notepad Press Ctrl V to past the copied data into Notepad In Notepad press Ctrl A followed by Ctrl C to copy the text Activate Excel and activate the upper left cell where you want to paste the formulas And make sure that the sheet you are copying to is in formula view mode Press Ctrl V to paste Press Ctrl to toggle out of formula view mode Note If the paste operation back to Excel doesn t work correctly chances are that you ve used Excel s Text-to-Columns feature recently and Excel is trying to be helpful by remembering how you last parsed your data You need to fire up the Convert Text to Columns Wizard Choose the Delimited option and click Next Clear all of the Delimiter option checkmarks except Tab Or from http spreadsheetpage com index php tip making an exact copy of a range of formulas If you re a VBA programmer you can simply execute the following code With Sheets Sheet1 Range A11 D20 Formula Range A1 D10 Formula End With

How to disable Excel's automatic cell reference change after copy/paste?

From http://spreadsheetpage.com/index.php/tip/making_an_exact_copy_of_a_range_of_formulas_take_2:

Put Excel in formula view mode. The easiest way to do this is to press Ctrl+` (that character is a "backwards apostrophe," and is usually on the same key that has the ~ (tilde).
Select the range to copy.
Press Ctrl+C
Start Windows Notepad
Press Ctrl+V to past the copied data into Notepad
In Notepad, press Ctrl+A followed by Ctrl+C to copy the text
Activate Excel and activate the upper left cell where you want to paste the formulas. And, make sure that the sheet you are copying to is in formula view mode.
Press Ctrl+V to paste.
Press Ctrl+` to toggle out of formula view mode.

Note: If the paste operation back to Excel doesn't work correctly, chances are that you've used Excel's Text-to-Columns feature recently, and Excel is trying to be helpful by remembering how you last parsed your data. You need to fire up the Convert Text to Columns Wizard. Choose the Delimited option and click Next. Clear all of the Delimiter option checkmarks except Tab.

Or, from http://spreadsheetpage.com/index.php/tip/making_an_exact_copy_of_a_range_of_formulas/:

If you're a VBA programmer, you can simply execute the following code: 
With Sheets("Sheet1")
 .Range("A11:D20").Formula = .Range("A1:D10").Formula
End With

~ Answered on 2013-04-22 18:54:23

How to disable Excel's automatic cell reference change after copy/paste?

The Solution to How to disable Excel's automatic cell reference change after copy/paste? is

Most Viewed Questions: