Excel copies formula correct but displays same value

[ssubhash]

My Excel has been behaving wierd. When I type in a formula in cell 1 and drag the formula all the way down, it copies formula references correctly but displays only value that is in cell 1 all the way down.

For example,
=A2+B2 results in 2 in C2; when I drag it down, next cell shows as
=A3+B3 but results is still 2 and result stays 2 all the way down
If I type in =A3+B3 in C3, then it displays correct value though.

I tried in different Excel files and all show the same issue.

Appreciate any thoughts on how to fix this..

 

[jeffreybrown]

First thing that comes to mind is calculations.

Is it set to manual or automatic? You can select F9 to calculate the sheet.

 

[ssubhash]

That's great! I hit F9 and values got updated to actual cell referenced results.

However, everytime I drag a formula, the same value gets copied over and I have to hit F9 to update values. This was not the case previously. How can I set this to calculate automatically without having to use F9 each time?

 

[jeffreybrown]

What version of Excel do you have?

For 2007
On the ribbon >> Formulas tab >> Calculation Options >> Automatic

I do not have a lower version, but my guess >> Tools >> Options >> Calculation

 

[ssubhash]

Thanks a lot. This was really helpful and worked out great.

 

[jeffreybrown]

You're very welcome...glad you got it all sorted out and thanks for the feedback

 

[danishalirana]

well...my F9 is not working..i mean it works on one sheet, and shows the same value in every other sheet, than i update the next one and the same value is in every other sheet, im using Excel 2013.

i have around 160 sheets in my excel file and 1 summary sheet for them. nothing is updating on real time basis.

 

[antman2988]

Just FYI, I appreciate your response as it also assisted me. However, I do not understand why it would've been changed from automatic to manual in the first place.

 

[tugkan]

Hi,
i had the same problem once. One of the files i downloaded from the internet changed automatic to manuel. Control the files that you recently used.

 

[fizzy]

Genius! I have spent ages trying to work out what was wrong. Thank you.