Refresh values in Excel 2003

Question

I have an excel file with three worksheets(ws), the first two have raw data and the third one contains formula to compare the data in other two ws. For example, here is an example of my comparison formula in the comparison ws:

=if(raw1!A1 = raw2!A1, "OK", "Diff")

I have a lots data in raw1 and raw2 ws (4k rows). The problem is that when I update data in raw ws by using copy and paste, my comparison ws does not reflect the change, I mean the value of comparison result. If I go to individual cell to make changes, such as add a space and then remove it, the value will be updated. I just cannot do it for all the cells. Not sure if it is memory issue to cause Excel not updating values if too many formulas there?

Is there any menu option or key available to fresh all the formula values in my compasion ws?

Answer 1

Some more information on the F9 keyboard shortcuts for calculation in Excel

• F9 Recalculates all worksheets in all open workbooks
• Shift+F9 Recalculates the active worksheet
• Ctrl+Alt+F9 Recalculates all worksheets in all open workbooks (Full recalculation)
• Shift+Ctrl+Alt+F9 Rebuilds the dependency tree and does a full recalculation

Taken from this answer.

Answer 2

Two things: 1) Look at Tools->Options->Calculation - it should say "Automatic" 2) You did say "update data in raw ws by using copy paste" - so does that mean when you past over data that is already being compared? Or, are you adding data to the end of your range? If you're added data to the end of your range, are you also extending your formulas on your comparison worksheet?

Answer 3

OK, found the easiest answer in another forum and tried it successfully:

Once you have Auto Calculate turned off, select the cell or group of cell you want to recalculate. Then simply do a find and replace with the = sign in both the "find what" and "replace what" fields.

It will go through your selection and replace each = sign with another = sign, not changing the calculation at all, but Excel will recalculate that cell because Excel thinks there is a new formula.

Done.