Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
    
Does F9 work? It's Excels "calculate" key. I think it works on the active sheet or workbook. – DMA57361 Oct 15 '10 at 17:26
up vote 2 down vote accepted

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.

share|improve this answer
    
shift+ctrl+alt+backspace+f9... pops up with the bill gates mini-me – KronoS Oct 15 '10 at 17:50

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?

share|improve this answer
    
I mean to paste data to the data already being compared. After pasting, I would like to see the comparison updated when its source data being changed. – David.Chu.ca Oct 18 '10 at 15:50

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.

share|improve this answer
    
I am very confused by this answer. – Ramhound Apr 8 at 13:37
    
There is a calculate now option in excel. FYI – Prasanna Apr 11 at 7:54

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .