Duplicating a row with formulas, without changing cell references

Question

i have a large financial spreadsheet with a ton of formulae. i'd like to duplicate a row such that it retains the same formula and coordinates as the source row (basically duplicate a summary row and still have it be the same summary):

start with:

[448]   =J425+J441  =K425+K441  =L425+L441

end with:

[447]   =J425+J441  =K425+K441  =L425+L441
[448]   =J425+J441  =K425+K441  =L425+L441

a simple copy paste automatically advances each of the coordinates, and ends up pointing to the wrong spot.

Solution 1

You can copy the cells in question and then paste link. I know this works in Excel 2010 but not sure about Excel Mac 2011. The paste link will make all the cell references absolute.

Solution 2

1. Click on "Show Formulas".  (In Excel 2007, this is on the "Formulas" tab, in the "Formula Auditing" group.)
2. Save as CSV.
3. Edit the CSV file (e.g., with Notepad) and make a copy of the row.
4. Open the CSV in Excel.
5. Since this will have stripped off formatting and other features, you might want to copy the new row back into your original XLS(X) file.

Solution 3

This is easy if you're open to using VBA. The following procedure copies the formula from each cell in the source range to the destination, maintaining the references without changing to absolute references.

Sub CopyRowPreserveReferences()
    Dim rng As Range
    Dim cell As Range
    Dim iOffset As Integer

    iOffset = 1 ' this will copy the row 1 row down, set it to -1 if you
                ' want to copy to the row above
    Set rng = Range("a5:i5") ' set this to range you want to copy
    For Each cell In rng
        cell.Offset(iOffset).Formula = cell.Formula
    Next
End Sub

Comments

[Zoredache]

Is this something you could address by using absolute references? cpearson.com/excel/relative.aspx =$L$425+$L$441.

[Admin]

Aside from copy-pasting the contents of each cell, I'm not sure this is possible

[Dmitry Grigoryev]

@kolosy why exactly isn't using absolute addresses a solution? It seems that you're trying to work around a problem with your formula, instead of addressing that problem directly.

[kolosy]

Because this is being done to a large, existing spreadsheet. Making the references absolute requires redoing the existing summary row, which is as time-consuming as duplicating it by hand. I was looking for a way to automate the process; it doesn't appear to exist.

[Arjan]

Now imagine the row has hundreds of columns; that's a lot of copy/paste then.

[Ender]

yes, that's annoying isn't it? How about this: assuming that all of your references are relative (eg A1 not $A$1) then you can do as wbeard says i.e. 1) paste link across 100's of cells. 2) But then, go into each formula (F2) 3) then press SHIFT-HOME or SHIFT-END to highlight the forumla, 4) then press F4 3 times to toggle the refernces from $A$1 to A1 again. Still a Little painful, but...