Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is there a way to insert the "$" to make a bunch of cells go from i.e. A4 to $A$4? There must be an easier way than to keep typing out the $'s!!


share|improve this question
What are you trying to achieve? This sounds like a use case where you don't want the $ to be able to drag and update everything? – Mario Jan 11 '13 at 23:55
The current cells I want to copy and paste elsewhere are linked to another tab. Hence, if I want to copy and paste to another part of the new tab, the values will be different. I want to do the $$ing to all the cells I want to copy so the values will be the same as the original tab. – O_O Jan 11 '13 at 23:58
up vote 1 down vote accepted

This will take multiple steps, but you can do "Find and Replace" and replace "A" with "$A", "B" with "$B" and so on. I admit this is only a partial solution though.

share|improve this answer
Better to replace “A” with “$A$”, etc., so you don’t need to do a second pass to insert dollar signs before the numbers. (And of course this will fail if there are any references to columns beyond Z.) – Scott Jan 12 '13 at 0:54

When entering a new formula, press F4 immediately after a cell reference to toggle between, for example, $A$1, A$1 and $A1 and A1.

When editing an existing formula, press F4 when your cursor is at the start, end or inside a cell reference.

share|improve this answer
Hmm not sure why it isn't working. I am using Microsoft Excel 2007... – O_O Jan 12 '13 at 0:03
See my edit. I've tested in Excel 2010 and 2013 Beta. – Mike Fitzpatrick Jan 12 '13 at 0:10
This method only works on a cell at a time, so is not exactly what you asked for. – Mike Fitzpatrick Jan 12 '13 at 0:11
Ah, thank you for your insight regardless. – O_O Jan 12 '13 at 0:28
The devil lives in the details –– in this case, your second sentence (“When editing an existing formula, …”). This requires actually clicking inside the cell (or the formula bar) –– or, the easier way, pressing F2. Still, this only lets you change one cell at a time (but, OTOH, faster than typing all the dollar signs manually). – Scott Jan 12 '13 at 0:51

A little VBA can do it (assuming you want all references set to absolute)

Sub Demo()
    Dim cl As Range
    For Each cl In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
        cl.Formula = Application.ConvertFormula(cl.Formula, xlA1, xlA1, True)
End Sub
share|improve this answer

If you want to change A1 with $A$1 in a particular cell, click on the cell in edit mode, or press F2 to enter edit mode. Then move the pointer at the place of A1 and click button F4.

If you want to change A1 with $A$1 at multiple places at once then press Ctrl + H to replace all A1 with $A$1.

share|improve this answer

I was facing the same problem with a HUGE spreadsheet. Then, reading the posts here, I've found a very easy way to do that! In my specific case, I had an entire row of cells (4000+...) with a formula like =PROCV(A1..., which I wanted to change for =PROCV($A1...:

I stopped looking for formulas or VBA tricks and started thinking on how Excel works. Then, I got a very helpful insight.

What I did was.

  1. Mark the row I wanted to change;
  2. Command CTRL+L (for cells substitution)
  3. I typed onto the "where reads" field: =PROCV( substitute for (or change for - my Excel is in Brazilian Portuguese language, so I'm not pretty sure) and, onto the proper field, =PROCV($, which could also be simply ($.
share|improve this answer

You must log in to answer this question.

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