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: microsoft, office, excel, tips

Comments

• Anonymous
  June 10, 2008
  Thanks for the tip--it was exactly what I was looking for--one of the first hits in Google.

• Anonymous
  March 17, 2009
  Fantastic! Many Thanks for the Tip

• Anonymous
  April 27, 2009
  Thanks Man. Been looking for this for a while

• Anonymous
  August 11, 2010
  Excellent tip. Thanks much, been looking for a while.

• Anonymous
  February 02, 2011
  Thanks - been searching for this and exactly what I needed

• Anonymous
  April 12, 2011
  Thanks for the tip!! It was exactly what I needed!

• Anonymous
  April 19, 2011
  Thanks a lot for this info.. This is exaclty what I need.. =D

• Anonymous
  June 23, 2011
  Thanks Dear

• Anonymous
  July 16, 2011
  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...

• Anonymous
  August 19, 2011
  Thanx, It just slipped out of my mind

• Anonymous
  November 13, 2011
  i've been looking for this tip for 3 months now. and this is the exact answer. a million thanx.

• Anonymous
  February 14, 2012
  Thanks.. Please post some more tips :)

• Anonymous
  June 06, 2012
  Thanks man, you just saved the day

• Anonymous
  July 15, 2012
  Thanks, just slipped out of my mind and i got it again!

• Anonymous
  July 17, 2012
  Thanks

• Anonymous
  July 30, 2012
  YOU LEGEND!!! JUST SAVED ME SO MUCH TIME!!

• Anonymous
  October 11, 2012
  Thank you so much, this is extremely helpful!

• Anonymous
  November 14, 2012
  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.

• Anonymous
  December 06, 2012
  thanks very direct and useful

• Anonymous
  December 16, 2012
  Thanks a lot....

• Anonymous
  February 06, 2013
  It is really wonderful.  It helped and helps me to save time. Thank you very much from my heart. Take care.

• Anonymous
  February 25, 2013
  very useful and time saving . . .thanks for this trick :)

• Anonymous
  April 08, 2013
  The comment has been removed

• Anonymous
  April 16, 2013
  Thank you very much! A very useful tips !!!

• Anonymous
  May 21, 2013
  Very useful!!!

• Anonymous
  July 14, 2013
  you are the one... Great. Thanks

• Anonymous
  July 31, 2013
  you have given me which I was seeking

• Anonymous
  August 05, 2013
  Cheers, that was to easy

• Anonymous
  August 26, 2013
  Thank you.It is really work for me.. :)

• Anonymous
  September 06, 2013
  Excellent approach. Really thanks for this.

• Anonymous
  September 30, 2013
  ($A$1)

• Anonymous
  October 17, 2013
  Thanks, this was exact information what I was looking for.

• Anonymous
  October 22, 2013
  Thanx For Ur Valuble Information

• Anonymous
  November 01, 2013
  THank you for doing this!

• Anonymous
  November 19, 2013
  The comment has been removed

• Anonymous
  December 04, 2013
  Thanks that's what I have looked for.

• Anonymous
  January 09, 2014
  Great! THX

• Anonymous
  April 28, 2014
  Champion. Really helpful. Thanks

• Anonymous
  June 24, 2014
  thanks!

• Anonymous
  September 10, 2014
  thanks!

• Anonymous
  December 08, 2014
  Thanks quite helpful.

• Anonymous
  January 08, 2015
  Great Thanks! Exactly what I am looking for.

• Anonymous
  May 22, 2015
  Thank you, couldn't remember the rule to use in the formula, this post was spot on.

• Anonymous
  May 24, 2015
  thanks, very helpful

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

• Anonymous
  August 10, 2015
  thanx, u made it simple to understand and obviously to apply, looking forward

• Anonymous
  August 13, 2015
  Thank you so much. This is Exactly what I needed.

• Anonymous
  August 25, 2015
  Very nice tip

• Anonymous
  August 29, 2015
  VERY VERY BRILLIANT TIPS YOU HAVE GIVEN . I REALLY KOVE YHIS TIPS

• Anonymous
  September 19, 2015
  Thanks! Didn't think I would find a solution to this with my first search result xD

• Anonymous
  October 11, 2015
  thanks

• Anonymous
  October 18, 2015
  couldn't remembre how to do this. Thanks!

• Anonymous
  January 03, 2016
  Ready

• Anonymous
  January 31, 2016
  Thanks. Exactly what I was looking for.

• Anonymous
  February 09, 2016
  thanks.  it helps a lot

• Anonymous
  February 16, 2016
  Thanks. Nicely Explained. Completed a long pending work.

• Anonymous
  February 22, 2016
  First hit in google - Exactly what I needed. Thanks!

• Anonymous
  March 13, 2016
  thank u very much