Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Exact Formula Copies.
Many formulas in a worksheet don't occur in solitude—they often appear numerous times in a worksheet. For instance, you may copy a formula into a range of cells in a column, each formula operating on data on its own row.
When you copy formulas, Excel automatically adjusts any relative cell references in the formula so that they remain relative in the target cell. For instance, if a formula in cell C4 is =A4+B4, then copying the formula down to cell C5 results in the formula =A5+B5.
There may be times when you want to create an exact copy of a formula, without Excel adjusting the relative cell references during the copy process. Assuming you want to make an exact copy of the formula in cell C4 and copy it to C5, follow these steps:
During this paste process, the relative cell references are not updated—the formula in cell C5 is now an exact duplicate of the one in cell C4.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6159) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Exact Formula Copies.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
US ZIP Codes can be of two varieties: five-digits or nine-digits. Here's how to convert longer ZIP Codes to the shorter ...
Discover MoreExcel is great at manipulating data, but sometimes it is difficult to figure out the best way to do the manipulation. ...
Discover MoreIf you have a range of cells that contain values, you may wonder which combinations of those cells should be used to meet ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2018-05-19 06:27:59
Willy Vanhaelen
If you have to do this very often this macro can be helpfull:
Sub CopyExact()
If TypeName(Selection) <> "Range" Then Exit Sub
Dim X
Set X = New DataObject
X.SetText ActiveCell.Formula
X.PutInClipboard
End Sub
You can assign a shortcut to this macro or create a button on the QAT.
All you have to do is move the cell pointer to the cell whose formula you want to copy and run the macro. The formula text is now on the clipboard and you can paste it anywhere as a clone of the formula, preserving the original cell-references.
2018-05-18 18:19:42
Ruthie A. Ward
I suppose once you get the rhythym of the keystrokes, it might be faster to use the above method. I'll have to try it when I want to copy the entire formula in a cell without Excel automatically changing the references. Thanks!
2018-05-16 10:29:43
Cam Peneff
Mich easier to just copy and paste the cell. Rather than using the full handle.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2018 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: 
Comments