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: , , ,