Tuesday, 4 June 2013

Excel Tip: A quick way to add dollar signs in a formula

Last week, I posted a really simple tip for copying down a formula to the bottom of your data.

This turned out to be one of my most popular posts, registering over 2,000 page views in under a week.

I said in that post:

I've lost count of the amount of times I've used this in front of an experienced Excel user who has stopped me and said "What did you just do there?".

Given its popularity, I thought I would post another simple tip that has regularly elicited that same response.

Coincidentally, this also relates to my most popular post ever (written two years ago, it still drew over 5,000 page views last month!), The dollar sign (\$) in a formula - Fixing cell references.

If you don't know why you would want to add dollar signs to a formula, then I would recommend reading that post. If you do, read on for a simple, but often missed, tip for adding them quickly.

When entering a reference in a formula, there are four possible ways in which you can apply the dollar signs:

1. Fix both the column and the row, e.g. =\$A\$1
2. Fix just the row, e.g. =A\$1
3. Fix just the columns, e,g. =\$A1
4. Fix neither the column, nor the row, e.g. =A1

This can be quite fiddly, typing the dollars in the right place, particularly if you have entered the reference by clicking the cell and then need to click the cursor in the right place to enter the dollar sign(s).

This is where this simple tip comes in.

As long as the cursor is in the reference, or immediately before or after it, you can use the function key F4, to toggle through the options above (in the order shown).

That's it, pressing F4 once adds both dollars, twice fixes the row, three times fixes the column, four times removes the dollars again. If you got over-excited and missed the right one, you can keep cycling through the options until you hit it again.

Simple, I know - but again, only if you already know it!

Click here for our our exclusive offer on Online Excel Training

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

31 comments:

1. Loving your posts!
Jo (in New Zealand)

2. already know it
Any how i am always reading for your Excel Tip, to get new ideas and benifits
keep it up
Thanks again

3. Thanks I did not know it before !! Sharif

4. Love this tip! I have been using Excel for years and have somehow never come across this one before, will make my life a lot easier!

1. Pleased you like it Roy, That's often the case with features that aren't accessed from the menu - you never really know about them until someone tells you. It was years before I found out about it too!

5. Thanks for that. I know some quick tips and wanted one for adding dollar signs so will try it next time I'm in work.

6. you saved my time

7. ow-some! sir but still i am not getting.
row and column uses kindly send me a example file into my mail id-ravanreturn@gmail.com or reply me on this same site.

1. look at the article
http://www.notjustnumbers.co.uk/2011/05/excel-tip-dollar-sign-in-formula-fixing.html
That explains it very well

8. So I had two rows, one with my cost, then one with my sale price,
I calculated my sale price cost*1.2 then I sometimes got 3 digit Decimals.

I copied the row, then special pasted the row, then highlighted the row, -format then clicked currency

Great Post!

9. how do you do this on a mac??

10. If you don't know why you would want to add dollar signs to a formula, then I would recommend reading that post. If you do, read on for a simple, but often missed, tip for adding them quickly. Get Some Dosh

11. Saved me a ton of time. F4 also works if you highlight the reference cell(s) in an existing formula.

12. I've lost count of the amount of times I've used this in front of an experienced Excel user who has stopped me and said "What did you just do there?". income

13. how can i Add \$ in long range? (I want to Add \$ in any row and column from A1 to ZZZ10000) if I add it one by one then may be I will Done it next week. so please help me to adding \$ (Dollar Sign) in every cell quickly. Thanks

1. I don't see how you could do that, or why you would need to. The dollars only have an effect when they are copied.

14. Is there a way to remove one or both \$ from all cells within a column or within a row - all at once, rather than cell by cell?

1. You could remove all of them by using Find and Replace. Replacing \$ with nothing

15. Good post. I have used this thousands of times while using Excel before and its so convenient. However, this doesnt seem to work now that I am using Windows10 and MS Excel 2013. have you experienced the same?

1. I use it in Windows 10 with Excel 2013 all of the time, with no problems.

16. Great tip! Thanks!

17. I was finding this trick to add \$ sign on excel. Ok i got it today.

18. I have used Excel for 15 years and only just thought to look this up!!!! Thanks so much. Just used it in MS 365 latest update, no problem at all (Win 8).

19. To make it quicker, press F2 while on a cell to get the cursor in the cell, then F4 to toggle to the right option for your situation. No need to click/double click each cell that way.

20. This post save 3second of my life!!

21. I've wondered for years why there was no quick way to do this. But I guess there is! Thanks!

22. F4 doesn't work for me. WIN 7, Excel 2016
F2 works for getting into the cell.
F4 does not toggle dollar signs. Is there some other key you need to press with F4?

1. No. Although some laptops often have the function keys on other keys and another key to select the function.

Also, make sure that you are editing the cell reference when you press it.