When you copy a range of formulas and paste them to a new location, Excel adjusts the cell references automatically. Most of the time, this is exactly what you want. Consider this simple formula:
=SUM(A2:A13)
If you copy this formula and paste it to the next column, the references are adjusted and the pasted formula is:
=SUM(B2:B13)
Making an exact copy of a single formula is easy: Press F2, highlight the formula, and press Ctrl+C to copy it as text. Then paste it to another cell. In some situations, however, you might need to make an exact copy of a range of formulas. In an older tip, I described a rather complicated way to do this. See Making An Exact Copy Of A Range Of Formulas.
Matthew D. Healy saw that tip and shared another method, which uses Notepad. Here's how it works:
- 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.
Post a Comment