Saturday, November 13, 2010

How to keep a cell reference in Excel from changing when you copy a formula

I like Excel, but I've never tried too hard to develop much skill with it. I don't know the lingo, I don't know the questions to ask, I don't quite know what's possible with it.

Here's something I discovered, that solved a problem of mine, and I really didn't know it was what I was looking for...absolute cell references.

Say you have a range in a formula, and you paste the formula somewhere else, but you really want the range to stay the same. Well, Excel changes all cell references by default. But if you write a reference to cell A3 as $A$3, then it will remain the same when you copy and paste.

I used that to good effect in a formula that added something from the top of the spreadsheet. I didn't want it to spread past the current row...I wanted a value that represented the total up to that row. So I started with a range of $A$1:A2, and copied it down. In the next row, that became $A1:A3, and so on: exactly the behavior I needed.

No comments:

Post a Comment