Knowledge base KB0214

Excel workbook is recalculated when copy-pasting to a think-cell datasheet although Excel Workbook Calculation option is set to Manual

Problem

My Excels' Workbook Calculation option is set to Manual in FileOptionsFormulasCalculation options. The Recalculate workbook before saving option is checked.

When I copy-paste from the worksheet to a think-cell datasheet, the source Excel workbook is nevertheless automatically recalculated. Recalculation does not happen when I copy-paste the same value within or between Excel workbooks or worksheets.

Solution

  • Paste the value as text by right-clicking the cell in the think-cell datasheet and choosing Paste Options:Paste Special…Paste As: Text.
  • Alternatively, uncheck Recalculate workbook before saving in the Excel options (see Problem above). Remember to enable it if needed.

Explanation

The think-cell datasheet and the datasheets of native PowerPoint charts in Office 2013 or Office 2016 use a separate Excel process. In this scenario, a default paste operation makes Excel treat the source workbook as if it was saved, leading to the recalculation if the Recalculate workbook before saving option is checked. We opened a case with Microsoft and asked for a fix, which has so far not been implemented. 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 112060458780009.

The behavior can easily be reproduced without think-cell with Office 2013 or Office 2016. It did not occur with Office 2003. (It is also reproducible with Office 2007 and Office 2010, using a different approach omitted here in the interest of brevity. Please contact our support team if you require a reproduction for these versions).

  1. Start PowerPoint and create a new PowerPoint presentation.
  2. In PowerPoint, go to Insertthink-cellMore More MenuDeactivate think-cell.
  3. Start Excel and open your affected workbook.
  4. In Excel, configure the calculation mode as described in Problem above.
  5. In PowerPoint, insert a new native PowerPoint chart via InsertIllustrationsChart.
  6. The chart's datasheet will open automatically. Keep it open.
  7. Copy the value from your affected workbook and paste it into the chart's datasheet.

Result: Pasting the value triggers recalculation of the source workbook.