Rexiology::Work

Microsoft, Information Technologies...

Community

News

  • From Taiwan, living and working at Tokyo, Japan.




Recent Posts

Tags

Microsoft Sites

Other Sites

Blog pools

Bloggers

Archives

Site Info



Locations of visitors to this page




Logos & Chicklets


GeoURL


Rex's Certifications
Rex's Certifications


Creative Commons授權條款
本 著作 係採用
Creative Commons 授權條款



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

crosspost from http://blogs.msdn.com/rextang

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

Technology at Hand said:

Thanks mate for the mention of mysite.. It s realy nice to play with excel. and It is very useful too..

Dexter

# June 7, 2007 11:10 PM

satit said:

Thank!!It's very useful to me.

# August 21, 2007 9:53 AM

zlatan24 said:

There is fine tool-<a href="www.recoverytoolbox.com/repair_excel.html">excel fix tool</a>, it is used to extract information from corrupted Microsoft Excel files, can easily and quickly recover information that seems to be lost forever, can recover the contents of all cells, formulas, formatting, etc.,  It is especially important because worksheets are usually used to process statistical, scientific, commercial and other really important data, Even one corrupted file may become a serious problem in all these cases.

# July 10, 2008 8:15 AM

Abida said:

Thanks very much. It was a while when last I used Excel application.

Regards

# July 31, 2008 3:28 PM

Adrian said:

Live Saver been wondering how to do that...hahaha...thanks

# August 31, 2008 3:04 AM

Sami said:

super duper i was looking for it it has solved one of my biggest problem and relieves me of headache

# November 12, 2008 12:16 PM

Gerardo said:

Hola!

Great! we were looking for this trick desperately!

# November 26, 2008 12:55 AM

Excel 2003 recovery said:

For work with excel files i usually use next tool-Excel 2003 recovery,which as how as i know is free,program has many possibilities,it can be easily solved with Excel recovery software for recovery xls,will allow you to Excel files recovery and make sure, that it works and that your work results can now be recovered just in several clicks,allows to save many hours of your work for recovery for Excel download and recover everything, that was done before.

# January 27, 2009 5:44 AM

Will said:

Absolute legend - i have been trying to find this tip out for ages, has just made something i have been trying to work out so much easier!

# March 18, 2009 7:28 AM

Fernando Pérez-Caballero said:

Thanks for the tip and the easy explanation, It really helped

# April 1, 2009 3:19 AM

dheeraj said:

is there any short cut key to fix the value in cell at single go.

# April 14, 2009 1:34 AM