Knowledge base KB0175
  2. Resources
  3. Knowledge base
  4. KB0175

Charts linked with Excel data link do not update on the first manual recalculation after changing data

Problem

If you change data in Excel and then press F9 to start manual recalculation, the chart is not changed. This problems occurs only under the following circumstances:

  • The file was saved in an older Excel version than the one you use to open the file.
  • Excel's calculation mode is set to manual.
  • The linked data is at least partially calculated from other Excel data.
  • All Excel cells containing formulas are locked.

If the think-cell chart is not updated after copy/paste of data please refer to KB0163.

Solution

When opening a workbook for the first time in a newer Excel version, change data and trigger a recalculation twice with F9. Once the workbook is saved with the newer Excel version, a single recalculation suffices.

Explanation

Excel recalculation behaves differently when you first open a workbook saved in an earlier version of Excel than when you open a workbook created in the current version. This is described in Microsoft's Excel Help under the heading, Learn about calculating workbooks that were created in an earlier version of Excel. If your company has a Microsoft Office Support contract and you want to contact Microsoft to ask for a fix, you may refer to Microsoft case number 113070110555536.

The problem can be reproduced without think-cell.

An Excel range embedded in a word document, linked to the source workbook, shows the same behavior:

  1. Open a new workbook in Excel 2007.
  2. Set the calculation mode to manual by clicking on Office button (top left corner of the Excel window) > Excel Options > Formulas and set Workbook Calculation to Manual.
  3. Enter the following data and formulae:
    Sheet1!A1: 1
    Sheet2!A1: 1
    Sheet2!B1: Bananas
    Sheet2!A2: 2
    Sheet2!B2: Oranges
    Sheet2!B3: =VLOOKUP(+Sheet1!A1;$A$1:$B$2;2;0)
  4. Lock cell B3 by right-clicking on the cell > Format Cells > Protection > Locked.
  5. Save the workbook in Excel 2007.
  6. Open the workbook in Excel 2010.
  7. Select Sheet2!A1:B3 and copy it with Ctrl+C
  8. Open Word 2010 with a new document.
  9. Paste the copied Excel cells via: Home > Clipboard > Paste Special > Paste link: Unformatted Text
  10. In Excel 2010, enter the value 2 in Sheet1!A1. Press Enter
  11. Press F9

Result: The data in Sheet2!B3 is updated, but the linked cell range in Word is not updated.

Share