Gossip Leak

Home / news

Microsoft Excel - Is there a way to insert the $ to a bunch of cells at once? (i.e. A4 to $A$4)

Emma Martinez |

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

6 Answers

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

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

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

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

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

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.

You Might Also Like