Welcome toExcel.Tips.Net
Helpful Links
Tips.Net Home
ExcelTips Home
Ask an Excel Question
Make a Comment
ExcelTips FAQ
ExcelTips Premium
Beauty Tips
Car Tips
Cleaning Tips
College Tips
Cooking Tips
Excel2007 Tips
ExcelTips
Family Tips
Gardening Tips
Health Tips
Home Tips
Money Tips
Organizing Tips
Pest Tips
Pet Tips
Wedding Tips
Word2007 Tips
WordTips
Advertise on the
ExcelTips Site
Newest Tips
Hiding Rows Based on a Cell Value
Subscribe
RSS Feeds
Daily Nuggets (an ExcelTip every day)
Allen Wyatt's ExcelTips (the weekly classic)
Exact Formula Copies
Summary: When you copy a formula from one cell to another, Excel normally adjusts the cell references within the formula so they are relative to where the formula is copied. This tip shows a variation on copying that allows you to avoid the formula adjustments. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007.)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 versions: 97 2000 2002 2003 2007
Remove Some Stress at Tax Time! Doing your personal income taxes can be a royal pain. Why not make the process just a bit less stressful with our 101-question checklist. You can prepare for filing your taxes with confidence, knowing you've covered all your bases.Check out Filing Your Income Taxes Checklist today!