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".
Loving your posts!
ReplyDeleteJo (in New Zealand)
Thanks Jo
DeleteGreat content
Deletealready know it
ReplyDeleteAny how i am always reading for your Excel Tip, to get new ideas and benifits
keep it up
Thanks again
Thanks I did not know it before !! Sharif
ReplyDeleteLove 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!
ReplyDeletePleased 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!
DeleteThanks for that. I know some quick tips and wanted one for adding dollar signs so will try it next time I'm in work.
ReplyDeleteyou saved my time
ReplyDeleteow-some! sir but still i am not getting.
ReplyDeleterow and column uses kindly send me a example file into my mail id-ravanreturn@gmail.com or reply me on this same site.
So I had two rows, one with my cost, then one with my sale price,
ReplyDeleteI 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!
how do you do this on a mac??
ReplyDeleteIf 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
ReplyDeleteSaved me a ton of time. F4 also works if you highlight the reference cell(s) in an existing formula.
ReplyDeleteI'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
ReplyDeletehow 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
ReplyDeleteI don't see how you could do that, or why you would need to. The dollars only have an effect when they are copied.
DeleteIs 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?
ReplyDeleteYou could remove all of them by using Find and Replace. Replacing $ with nothing
DeleteGood 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?
ReplyDeleteI use it in Windows 10 with Excel 2013 all of the time, with no problems.
DeleteGreat tip! Thanks!
ReplyDeleteI was finding this trick to add $ sign on excel. Ok i got it today.
ReplyDeleteI 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).
ReplyDeleteTo 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.
ReplyDeleteThis post save 3second of my life!!
ReplyDeletenice
ReplyDeleteI've wondered for years why there was no quick way to do this. But I guess there is! Thanks!
ReplyDeleteF4 doesn't work for me. WIN 7, Excel 2016
ReplyDeleteF2 works for getting into the cell.
F4 does not toggle dollar signs. Is there some other key you need to press with F4?
No. Although some laptops often have the function keys on other keys and another key to select the function.
DeleteAlso, make sure that you are editing the cell reference when you press it.
HA! THIS IS EXACTLY WHAT I NEED RIGHT NOW! I HAVE BEEN LOOKING EVERYWHERE! THANK YOU SO MUCH!
ReplyDeleteThank you. Saved a lot of time
ReplyDeleteVery helpful and clearly explained. Thank you!
ReplyDeleteVery helpful and clearly explained. I like that it toggles through the optional formatting.
ReplyDelete