3

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!!

Thanks.

2
  • 1
    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
4

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.

1
  • 5
    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
7

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.

5
  • 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
  • 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
  • And I have to do this for the formulas in hundreds of cells? Nah. – richard1941 Apr 1 '18 at 23:48
1

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.

1
  • I am not eager to do this 10000 times. – richard1941 Apr 1 '18 at 23:50
1

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)
    Next
End Sub
0

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 ($.
0
0

OK, I hacked it out!

I had an array of formulas like =Average(CU4,CV5,CU6,CT5) that was 100x100. The edge and corners were slightly different. This is used to solve a heat flow problem in a certain two dimensional geometry to investigate the use of thermally conductive fillets to mitigate thermal stress in the corners. I wanted to create a template of formulas that could be copies and pasted onto a different worksheet. Without the dollar signs, I could use the fill command to fill most of the 10,000 cells of the array. But copying and pasting the formulas would be disaster because Excel adjusts the row references without the dollar sign.

Solution: highlight the 100x100 block. Click Home/Find and Select. Then I replaced ( with ($. Excel did 10000 replacements. Next, I replaced , with ,$. Excel did 30000 replacements. All this in only a few seconds-- a lot quicker than typing all 10000 cells.

To solve for the steady state temperature distribution by successive averaging is a lot of work that is highly dependent on the initial temperature distribution, but that is another problem to be overcome. Like many solve problems (such as square root), you get an answer much faster if your initial guess is in the ball park.

Thank you Bill Gates! You did it right.

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