Excel Tips: Reference a fix cell value while copying cells…

This must be pretty normal for people who are using Excel every day, but it is a tip for me that mainly writing code and seldom using Excel. Since my current performance evaluation work need to prepare many calculation reports, Excel becomes a requied tool for this.

Just a simple question while using Excel, that I want to fix one cell value on another formula cell while copying the formula to another rows.

for example on above pic, I want to let A1 value 3 be fixed while I doing C3 = A3*B3*(A1) and copy C3’s formula to C4 = A4*B4*(A1) and C5 = A5*B5*(A1). if one use the normal copy cell technique, the formula will become C4 = A4*B4*(A2) instead of fixed at A1.

To fix the value A1 in C column’s formula cells, by referring to this post, only have to put cursor on the formula cell, on the target cell name (in this case, A1), and then press F4, the A1 cell name will become $A$1 to indicate it’s fixed, and then it’s done. (one can just put the $ sign to the row or column that want to be fixed while copying, like $A1 or A$1, or to fix both, $A$1).

that’s it, simple but useful trick, FYI.

Technorati tags: , , ,
Comments (58)

  1. TS says:

    Thanks for the tip–it was exactly what I was looking for–one of the first hits in Google.

  2. Vidar says:

    Fantastic! Many Thanks for the Tip

  3. Steve says:

    Thanks Man. Been looking for this for a while

  4. HR says:

    Excellent tip. Thanks much, been looking for a while.

  5. AS says:

    Thanks – been searching for this and exactly what I needed

  6. Angel says:

    Thanks for the tip!! It was exactly what I needed!

  7. marshykura says:

    Thanks a lot for this info.. This is exaclty what I need.. =D

  8. MK says:

    Thanks Dear

  9. Keila says:

    Does anyone know how to do this with a Chromebook? I'm writing a really long formula with a lot of fixed cell references and it's very tedious to go back and type in $ for each one…

  10. Nisha says:

    Thanx, It just slipped out of my mind

  11. al stephen says:

    i've been looking for this tip for 3 months now. and this is the exact answer. a million thanx.

  12. Maha says:

    Thanks.. Please post some more tips 🙂

  13. M says:

    Thanks man, you just saved the day

  14. Nad says:

    Thanks, just slipped out of my mind and i got it again!

  15. Hashim says:


  16. Min says:

    Thank you so much, this is extremely helpful!

  17. Sarah says:

    I want to know how to use the keyboard to press some buttoms so that I can fix the cell, but not put $ again and again before the cell name.

  18. vahid says:

    thanks very direct and useful

  19. Srikanth G says:

    Thanks a lot….

  20. Sanjoy Kumar Saha says:

    It is really wonderful.  It helped and helps me to save time. Thank you very much from my heart. Take care.

  21. rajeev chaudhary says:

    very useful and time saving . . .thanks for this trick 🙂

  22. acidz4 says:

    thanx 4 da tips, good guy

  23. Ola Nilsson says:

    Thank you very much! A very useful tips !!!

  24. niajamann says:

    Very useful!!!

  25. anupeace says:

    you are the one… Great.


  26. nilesh says:

    you have given me which I was seeking

  27. Adrian says:

    Cheers, that was to easy

  28. Wai Zin Hnin says:

    Thank you.It is really work for me.. 🙂

  29. Ankit says:

    Excellent approach. Really thanks for this.

  30. Ramith Nalpadi says:


  31. Rakesh Jotania says:

    Thanks, this was exact information what I was looking for.

  32. Srinivas Kotnala says:

    Thanx For Ur Valuble Information

  33. PJ says:

    THank you for doing this!

  34. Padmanabhan says:

    Thank you that is a very usefull information because after breaking one hour in my excel help you have given me a excellent information. Have a nice day and god bless you

  35. Stefan says:

    Thanks that's what I have looked for.

  36. Ruben says:

    Great! THX

  37. Cass says:

    Champion. Really helpful. Thanks

  38. nitin says:


  39. Uche says:

    Thanks quite helpful.

  40. Gobinath says:

    Great Thanks! Exactly what I am looking for.

  41. Emma says:

    Thank you, couldn't remember the rule to use in the formula, this post was spot on.

  42. volkan says:

    thanks, very helpful

  43. Marcus says:

    Thanks, I was searching for this exact information. (The f4 tip came as a useful bonus)

  44. njk says:

    thanx, u made it simple to understand and obviously to apply, looking forward

  45. Pedro says:

    Thank you so much. This is Exactly what I needed.

  46. Darrel says:

    Very nice tip

  47. ANSH JAIN says:


  48. Markus says:

    Thanks! Didn't think I would find a solution to this with my first search result xD

  49. abeesh says:


  50. fixer says:

    couldn't remembre how to do this. Thanks!

  51. Rice says:

    Thanks. Exactly what I was looking for.

  52. zw says:

    thanks.  it helps a lot

  53. Vincent says:

    Thanks. Nicely Explained. Completed a long pending work.

  54. GS says:

    First hit in google – Exactly what I needed. Thanks!

  55. kiran says:

    thank u very much