Search Not Just Numbers

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".

34 comments:

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

    ReplyDelete
  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

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

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

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

      Delete
  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.

    ReplyDelete
  6. 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.

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

    ReplyDelete
  8. how do you do this on a mac??

    ReplyDelete
  9. 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

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

    ReplyDelete
  11. 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

    ReplyDelete
  12. 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

    ReplyDelete
    Replies
    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.

      Delete
  13. 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?

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

      Delete
  14. 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?

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

      Delete
  15. Great tip! Thanks!

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

    ReplyDelete
  17. 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).

    ReplyDelete
  18. 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.

    ReplyDelete
  19. This post save 3second of my life!!

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

    ReplyDelete
  21. 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?

    ReplyDelete
    Replies
    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.

      Delete
  22. HA! THIS IS EXACTLY WHAT I NEED RIGHT NOW! I HAVE BEEN LOOKING EVERYWHERE! THANK YOU SO MUCH!

    ReplyDelete
  23. Thank you. Saved a lot of time

    ReplyDelete
  24. Very helpful and clearly explained. Thank you!

    ReplyDelete
  25. Very helpful and clearly explained. I like that it toggles through the optional formatting.

    ReplyDelete