Automatically add $ symbols to formula?

[thx1139]

I've typed a long formula into a cell in Excel 2010 and realised that all the cell references should have $ symbols in front so I can copy and paste it to different cells without references changing. Is there a quick way to achieve this after having typed in the formula or do I need to do it manually?

 

[archer]

Try this.

In another column (like column B for example), you can enter the following formula where "$" would be combined with the contents cell "A" with a leading "dollar sign". You can then paste this formula down in column B to create this special field based on column A

="$"&A1

 

[TanyaC]

That would work fine if the only reference that needed a $ to lock the reference were the first one. If it is a long formula, there really is no easy way to do it automatically, unless the cell references are the same (in which case you could do a find and replace, looking in formulas).

However, if the cell references are all different you're going to have to do it manually. The good news is that you should only have to do it once per range, because you can, as you noted, just copy the formulas across and down.

Here's an example of a formula I have in one of my spreadsheets... In a case like this, I actually did forget the $ and had to manually add it where required..

=COUNTIFS('Analysis Data'!$R$5:$R$1840,MONTH($C8),'Analysis Data'!$S$5:$S$1840,$B8,Games!$F$5:$F$1840,"O",Games!$I$5:$I$1840,Q$3)
+COUNTIFS('Analysis Data'!$R$5:$R$1840,MONTH($C8),'Analysis Data'!$S$5:$S$1840,$B8,Games!$F$5:$F$1840,"O",Games!$M$5:$M$1840,Q$3)
+COUNTIFS('Analysis Data'!$R$5:$R$1840,MONTH($C8),'Analysis Data'!$S$5:$S$1840,$B8,Games!$F$5:$F$1840,"O",Games!$Q$5:$Q$1840,Q$3)
+COUNTIFS('Analysis Data'!$R$5:$R$1840,MONTH($C8),'Analysis Data'!$S$5:$S$1840,$B8,Games!$F$5:$F$1840,"O",Games!$U$5:$U$1840,Q$3)
+COUNTIFS('Analysis Data'!$R$5:$R$1840,MONTH($C8),'Analysis Data'!$S$5:$S$1840,$B8,Games!$F$5:$F$1840,"O",Games!$Y$5:$Y$1840,Q$3)
+COUNTIFS('Analysis Data'!$R$5:$R$1840,MONTH($C8),'Analysis Data'!$S$5:$S$1840,$B8,Games!$F$5:$F$1840,"O",Games!$AC$5:$AC$1840,Q$3)

EDIT: Actually, the ="$"&A1 wouldn't work.. You'd end up with $=<cell_value>.. It'd have to be ="=$"&A1