Windows 7 Forums
Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.

Windows 7: Automatically add $ symbols to formula?

06 Jan 2014   #1

Win 7 Home Premium (OEM) - Install date: 02-2010
Automatically add $ symbols to formula?

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?

My System SpecsSystem Spec
06 Jan 2014   #2

Windows 7 ultimate x64

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

My System SpecsSystem Spec
06 Jan 2014   #3

Linux Mint 17 Cinnamon | Win 7 Ult x64

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
My System SpecsSystem Spec


 Automatically add $ symbols to formula?

Thread Tools

Similar help and support threads
Thread Forum
Excel Formula
Hello Sir, Can you let me know the formula in Excel: I have a table as below: This table, At the column "A,Status" when i Put the word" Completed", it's will automatic put current Date to Column "B, Row 2". But i don't know after that when tomorrow arrived,at the column "B,Row 2",...
Microsoft Office
Formula 1 '97
Hello, i'm a new user. :) I correctly manage to install this old game (F1 '97) in my computer which is running Windows 7 Home Premium x64. I can even play with it but after a race the screen goes black and it crash. I found this message on Event Viewer... I'm from Italy so sorry if the...
Corsair Formula iv
Hello, I have the Asus Crosshair Formula iv Mobo, and I think its the root of my PC issues. I get random freezes and at times cannot start, just blank screens. The reset does not work , i have to hit the reset button on the board itself. Does anyone have any similar issues with this particular...
Hardware & Devices
Excel Formula Help please...
This is probably very simple and I have got it working but it isn't working how I want it to work. I have created an Excel document to show my monthly payments of a bank loan. I have created 3 columns in the first column is the payment number, in the second column is the amount that the loan...
Microsoft Office
Crosshair III Formula RAID0 help
is there ANY compatible driver available for the 750SB ... of course I'm looking for drivers I can use to install 7 on my RAID0 striping array of two 150 gig VelociRaptors. I'm just trying to install the RC, not RTM for now. Please see my setup. Thanks, any help or a simple "no you can't...

Our Sites

Site Links

About Us

Find Us

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd

All times are GMT -5. The time now is 23:08.
Twitter Facebook Google+