Loading

Got a version of Excel that uses the
menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)?
**This site is for you!** If you
use a later version of Excel, visit
our *ExcelTips* site focusing on the ribbon interface.

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company.

Learn more about Allen...

ExcelTips FAQ

ExcelTips Resources

Ask an Excel Question

Make a Comment

Free Business Forms

Free Calendars

** Please Note:** This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later),

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:

- Select cell C4.
- Press
**F2**. Excel enters Edit mode, with the insertion point at the end of the formula. - Press
**Shift+Ctrl+Home**. Excel selects everything in the cell, back to the beginning of the formula. - Press
**Ctrl+C**to copy the selected text (the formula's text) to the Clipboard. - Press
**Enter**to move to cell C5. - Press
**Ctrl+V**to paste the Clipboard contents into the cell.

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 (2990) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Exact Formula Copies.

*Related Tips:*

**Comprehensive VBA Guide** Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out *Mastering VBA for Office 2010* today!

Another alternative is to insert an apostrophe at the start of the formula. This converts the contents to text. Then copy the formula to the new location and remove the apostrophes from the original and copy.

Another way to copy large groups of formulas without changing relative references is to: 1) select all of the formulas you wish to copy, 2) use the Replace command to change all the '=' to '#', 3) copy and paste the text of the formulas to the new location, and 4) use the Replace command to change all of the '#' back to '='. If the formula's contain the # character, use some other unique character that can be changed with the Replace command

Another way to copy a formula without changing the cell reference is to use absolute cell reference. Add a $ in front of the cell to remain absolute. For example A1+B1 can be entered as $A$1+$B$1. The formula will remain unchanged when copying. Also if cell A1 is to be absolute and cell B1 is to be relative enter $A$1+B1

A shortcut for entering absolute reference ($) is to highlight the formula and press F4.

A shortcut for entering absolute reference ($) is to highlight the formula and press F4.