8

In Excel (2007), when cells that contain absolute references (for example: $A$3) are copied, the absolute reference remains the same. This is by design, and the reason of using absolute references.

Problem -- However, sometimes I want copy a block of cells (that contain absolute and probably also relative references), and paste them with the absolute references shifted correctly for the new block. That is, I want the absolute references to behave like relative references when copying, but still be absolute references in the final copied result.

Example -- In the example screenshot, I want to copy the block A2:B3 downwards. When copied, I basically want to have the formula in B3 (=$A$3) changed so that it refers to the cell to the left of it, for example becoming =$A$11 when copied to B11, as in the bottom part of the screenshot.

example in Excel

Workaround -- I have found a workaround to do this, by:

  1. making a copy of the entire worksheet (ctrl-drag worksheet tab to new location),
  2. then cutting (ctrl-X) the relevant block of cells from the new worksheet
  3. pasting (ctrl-v) into the original worksheet.
  4. finally deleting the new, temporary worksheet (right-click worksheet tab and delete).

Question -- But this is too many actions for my taste. Is there an easier way (perhaps some Paste Special hidden option)?

3
  • Why not just delete the second $ sign? Make cell B3 =$A3 instead of =$A$3. Feb 27, 2013 at 9:40
  • @Kevin: This is just a toy example. In the real case (some months ago) I had something like a 20x20 block, where I had a few 'configuration' cells which were in a fixed position (relative to the block). For constructing the block (ie. expanding from a single row to 20 rows ), it is the most convenient to work with absolute cell references. However, when I make a copy of the block (to see the result with other values for the configuration cells) in any direction, the absolute cells lead to the problem described in my post. Feb 27, 2013 at 12:34
  • My guess is that you have tried the entering =$A3 in the cell then copying it and then Paste the Formula only in the target cell.....
    – Darius
    Apr 3, 2013 at 23:34

9 Answers 9

2

you could always try writing a macro for that. excel has a really nice macro recording tool that you could use too, and then just run it as needed (provided you make some changes first of course to the program)

dim firstLetter as String 
dim secondLetter as String 
dim firstNumber as integer 
dim secondNumber as integer 
dim firstReference as string 
dim secondReference as string 
dim contents as string 
firstLetter = inputbox("Where's the first column? (it's letter)") 
firstNumber = inputbox("And what's the first row? (just the number)") 
secondLetter = inputbox("What column is this going to be moved to? (the letter only.)") 
secondNumber = inputbox("And what row? (the number.)") 
contents = range(firstletter + cstr(firstnumber)).formula 
range(secondletter + cstr(secondnumber)).formula = contents 
4
  • This isn't a recordable process. Obviously you can always write a custom macro to automate something but it would be more helpful to attempt to give some sample code.
    – lori_m
    Mar 13, 2012 at 14:37
  • dim firstLetter as String dim secondLetter as String dim firstNumber as integer dim secondNumber as integer dim firstReference as string dim secondReference as string dim contents as string firstLetter = inputbox("Where's the first column? (it's letter)") firstNumber = inputbox("And what's the first row? (just the number)") secondLetter = inputbox("What column is this going to be moved to? (the letter only.)") secondNumber = inputbox("And what row? (the number.)") contents = range(firstletter + cstr(firstnumber)).formula range(secondletter + cstr(secondnumber)).formula = contents Mar 15, 2012 at 12:21
  • oh good god that didn't carry over well at all from notepad. but yeah, something like that might work -- you MAY have to play around with it a bit but I think 'formula' is the cell's formula where as the r1c1 thing does that but maintains relative reference which is what we don't want to do. Mar 15, 2012 at 12:22
  • I've tried to insert your code into the post, you can edit it from there. Hope this helps
    – lori_m
    Mar 15, 2012 at 12:56
2

I found the workaroud for duplication.

Problem: Duplicate selection from starting cell B2 and place it on starting cell P2.

  1. Create a new sheet.
  2. From original sheet copy all what you want to duplicate and paste it into the new sheet at the same starting cell (if your selected rectangle start from B2, paste it into new sheet B2)
  3. Move that selection in the new sheet on the right new starting cell (move it to starting cell P2).
  4. Copy selection.
  5. Paste selection on the original sheet on the right new starting cell (P2).
1

If I try to follow the steps to cut and paste from a new worksheet I find that all references in formulas stay fixed when copying to the new location including relative references. In fact in Excel 2010 i find that after cutting and pasting formulas, the first row and column contain links to the old sheets but other rows and columns reference the new sheet which looks like a bug??

If you are wanting to copy a block of formulas keeping all references the same, you can press Ctrl+` (backquote) to show formulas and then copy and paste by clicking the icon on the Clipboard task pane (activate using small arrow on clipboard section of Home Tab). If this is not what you are trying to achieve, a simple example would help.

3
  • I've added an example with illustration. Hope that makes it clear. Mar 13, 2012 at 13:56
  • That's clear, but your workaround does not achieve that result for me. I don't think there's an easy way to do this, I'd just do an edit replace for the absolute references i.e. replace $A$3 with $A$11.
    – lori_m
    Mar 13, 2012 at 14:32
  • strange you have that issue. Mine (Excel 2007) is working fine Mar 13, 2012 at 14:52
1

I have been trying this macro (stored in Personal.xlsb and bound to shortcut key) to convert references to absolute before copying.

Sub ToAbsolute()
 Dim c As Variant
    Application.ScreenUpdating = False
    For Each c In Selection
        If (Not IsEmpty(c.Value)) Then
            c.Value = Application.ConvertFormula(c.Formula, xlA1, , xlAbsolute)
        End If
    Next c
    Application.ScreenUpdating = True
End Sub

Sub ToRelative()
 Dim c As Variant
    Application.ScreenUpdating = False
    For Each c In Selection
        If (Not IsEmpty(c.Value)) Then
            c.Value = Application.ConvertFormula(c.Formula, xlA1, , xlRelative, c)
        End If
    Next c
    Application.ScreenUpdating = True
End Sub
1

The following will work with less complexity then writing your own macro and achieves the final result.

Yes, I know I'm not using the Absolute cell reference but as shown in OP's example you don't need it.

Select range that you want to copy

enter image description here

Then from the drop down menu of Paste choose the Formulas option as shown here

enter image description here

This has been tested by me and works in Excel 2007 and 2010. Enjoy :)

3
  • 1
    Doesn't work for me (Excel 2007). I think the reason it appears to work for you is because in the formula =$A7 only the column (A) is absolute, but the row (7) is relative. If you use =$A$7 you'll see it doesn't work. In my original example, it is actually the absolute row reference that IS important. Apr 4, 2013 at 7:22
  • The question is, do you really, really (for really :D ) need the absolute reference. I found myself that the second I want to copy the absolute reference it appears I can get away with $ on either column or the row.
    – Darius
    Apr 4, 2013 at 15:40
  • Regarding the italicized sentence, you DO need both absolute references in some cases. For example, suppose you are wanting to copy the block to the left or right, but you also want the absolute reference as is within the block for quick filling purposes.
    – blakeoft
    Jun 27, 2018 at 21:56
1

A work around I have used is to:

  • create the block of cells that you want to paste, which includes all of your absolute referennces (in your example the block labled 'Original Block')

  • then create the worksheet you would like to see in the end by copying and pasteing in whatever style formation you like. So to coninue with your example, you would use 'Original Block' and copy it say..10 times below that, which would be the final worksheet layout. (You will notice you still have the same problem with the absolute cell referencing the cell(s) from the original block.)

  • Take and highlight that whole worksheet and CUT and copy to a new worksheet

You will then notice all of cells have been linked with their respecitve absolutes that you desired on your new and improved sheet.

Good Luck :)

1

One way to tackle this problem is to use only relative references and follow the procedure below to copy the relative references in a way that treats them like absolutes. Sources for this answer include Stack Overflow and this page.

  1. 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).
  2. Select the range to copy.
  3. Press Ctrl+C
  4. Start Windows Notepad
  5. Press Ctrl+V to past the copied data into Notepad
  6. In Notepad, press Ctrl+A followed by Ctrl+C to copy the text (In some cases, I found I had to go back to Excel and clear the existing selection before doing Ctrl+C in Notepad)
  7. 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.
  8. Press Ctrl+V to paste.
  9. Press Ctrl+` to toggle out of formula view mode.
1

I had a similar problem. I have a very big spreadsheet with thousands of formulas, some with column absolute, and some with both column and row absolute. I wanted to make a side-by-side duplicate on the same worksheet, instead of making a duplicate on a new spreadsheet. I needed to copy all of the formulas and have Excel treat absolute references as relative references.

Excel will change all addresses as if they are relative when you insert a column to the left of the range of cells with the formulas.

  1. Make a new sheet next to the original sheet. This will be the sheet with the final product.

  2. Go to the original sheet. Highlight the columns with the formulas. Copy.

  3. Go to the new sheet and highlight the same columns. Paste.

  4. Go to the original sheet. Insert the number of columns as your range is wide. For example, my range is 20 columns wide. So I inserted 20 columns to the left of my range, moving all of my formulas and changing all of the references as if they were relative.

  5. Highlight the new columns. Copy.

  6. Go to the new sheet. Highlight the exact same columns on the new sheet. Paste.

1

I just found a partial workaround. It's not as general as the copied-worksheet-and-cutting-range workaround in the OP, but it can be much faster if you just need to quickly copy something. What we all really want here tho is a way to temporarily disable the absolute references while we copy. There could be an option to allow you to hold alt when you paste to ignore every $, like they just weren't there, but they actually still are once the paste is completed.

So, with that in mind, to copy a block of cells while allowing the absolute references to update:

  1. Highlight the range of cells you wish to copy
  2. "Replace-all" the $ with a letter that still makes it a valid reference.

Due to the total number of columns, XFD# is the last valid cell reference. Make sure the new references generated don't overlap an actual reference in your range of cells or that reference will be broken in the last step. Choosing a letter from a column you never reference in your block of cells to be copied will ensure you do not break any formulas. As an example, if you replaced $ with 'h', $b$42 would become hbh42, which is still a valid reference that can now be copied and will update automatically!

  1. Finally, convert the temporary character ('h' in the above example) back into $ and you're done!

You can try replacing the $ with 2 letters in some cases, but that has a number of problems of it's own, so it would be easier to find a 1 letter replacement that won't overlap an actual reference.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .