Excel problems with copying a formula [Closed]

Report
-
Posts
1021
Registration date
Saturday May 23, 2009
Status
Contributor
Last seen
October 20, 2010
-
My excel is behaving funny. When I copy a cell formula across the row or column, it copies the formula correctly, but does not give the corresponding value!!

Example: If Cell B1 has the value 20. Suppose I type =B1+1 inside cell B2, that gives me 21. Now, when i use Ctrl+C to copy cell B2 to cell B3, I should get the value 22. Instead, it still reads 21. However, the formula inside the cell correctly reads =B2+1. Its bizarre.

Autofill gives me the same problem. BUT, if i manually type in the formula into cell B3, it gives me the correct value 22. And that defeats the purpose of Excel, because I cannot manually type a formula into every single cell!!!!

Please HELP!

2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
763
does the value get updated if you press recalc ? i think that is F9. Also look under options. If you are using 2007 then that would be under office button (that round thing at the top). Once in Excel option, look under formula
You have to put dollar signs ($) inbetween the cell reference B1 in the formula.

Eg. =$B$1+1

Then when you use autofill, it keeps the cell reference $B$1 instead of being the cell above.
Posts
1021
Registration date
Saturday May 23, 2009
Status
Contributor
Last seen
October 20, 2010
182
he wants it to be the cell above.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!