Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
1  
Is this something you could address by using absolute references? cpearson.com/excel/relative.aspx =$L$425+$L$441. –  Zoredache Aug 8 '12 at 0:06
    
not really - i'm fine with how the summary row is constructed, i just want to duplicate it. if i was building it from scratch, i could make it absolute, but that has its own issues, and i don't want to have to go back and make those changes –  kolosy Aug 8 '12 at 0:10
    
Aside from copy-pasting the contents of each cell, I'm not sure this is possible –  user3463 Aug 8 '12 at 2:43

5 Answers 5

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.

enter image description here

share|improve this answer
    
this isn't exactly what i'm looking for. it just creates cells that point to the original cell (and they aren't absolute). the effect is similar - a row with the same values, but i need the formulae duplicated, not references to them –  kolosy Aug 9 '12 at 3:40
    
VBA is your only solution –  wbeard52 Aug 9 '12 at 4:12

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
share|improve this answer
  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.
share|improve this answer

I found another answer:

  1. Replace all "=" characters with "#" characters (in your summary row).
  2. Copy and paste the row.
  3. Change the "#" characters back to "=" characters.

Disclosure: I don't entirely understand why this works. I have gotten it to work (in Excel 2007), but I haven't tested it exhaustively.

share|improve this answer

This can be done in a relatively straightforward manner in three steps:

  1. Make sure you have some blank rows to work in
  2. Copy the row [448] up to row [447] ; this sets all the row refs 1 less than they should for row 447
  3. Move the row [447] up to row [446] ; the row refs are still one less than they should be
  4. Copy the row [446] down to row [447]; the row refs are now incremented back up by 1

At this point your row [447] should now be a clone of row [448]

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.