Recalculate (Calc)

Occasionally, I have the problem that LibreOffice Calc doesn't update the outcome of formulas, even not after exiting and reopening such files. Thanks to ChatGPT, I finally got a grip on this. The case below is from 2024, but the problem is quite some years older.

The problem

I use LibreOffice Calc. I'm working on a file which is 400kb in size. When I copy-paste a cell that contains a sum() command, it doesn't update the evaluation of the sum() command. How to solve that?

Additional information

• When I open a new Calc file, I cannot reproduce this error. It only seems to happen in the already-opened file
• This existing file has probably been around for years, so it might not get recognised by LibreOffice als an original LibreOffice Calc file
• I checked settings under File » Properties for any clue concerning file-specific settings, but couldn't find any. In hindsight, it seems that not all file-specific settings are enumerated here.

Potential solutions

In LibreOffice Calc, when you copy-paste a cell containing a SUM() formula, the pasted formula should automatically update its references based on the new location. However, if this is not happening, there are a few potential reasons and solutions:

Enable AutoCalculate

Data » Calculate » AutoCalculate: Check

Automatic recalculations

Ensure that automatic recalculation is enabled:

Tools » Options » LibreOffice Calc » Enable automatic recalculation: Check

Unfortunately, I don't have such an option.

Force recalculate (Shift-Ctrl-F9)

Shift+Ctrl+F9 (Force Recalculate): After pasting, you can try forcing a recalculation by selecting the cell or the entire sheet and pressing Shift+Ctrl+F9.

Cell references

Cell References: Ensure that your SUM() formula has relative references that can adapt to the new location. For example, if your formula is =SUM(A1:A5), when you copy and paste it to a new location, it should automatically adjust to the new range.

Paste Special

Use Paste Special: Instead of a regular copy-paste, try using "Paste Special" with the option "Formulas" selected. This can be accessed by right-clicking on the destination cell, choosing "Paste Special," and then selecting "Formulas."

File corruption

File Corruption: If none of the above solutions work, there might be an issue with the file. In some cases, files can become corrupted. You can try saving a copy of the file with a different name and see if the issue persists in the new file.

Solution (2024.01)

Data » Calculate » AutoCalculate was disabled. After enabling, problem was gone.

What is remarkable about this: Appearantly, this setting is document-related, but I couldn't find it at File » Properties.

Evaluation (2024.01)

This is an excellent example of something for which I find ChatGPT really helpful: When I don't know the context of a problem, or how to describe it.