12

The LibreOffice Calc (version 6.3.4.2) shows the definition of the formula in the cell instead of executing the formula and displaying the result. What affects the behaviour?

I am starting to use LibreOffice (simple things; newbie).

enter image description here

Update: The original file came from Excel (.xlsx extension). The first row formula was typed manually. The formulas below were filled by dragging the bottom-right handle of the cell. I guess that it should behave similarly to Excel; tha is, the relative cell reference (here A3) should be updated when dragging the formulas to the cells below. Also from that I guess that the formula is treated as a plain text, not as a formula. How to fix that?

Update 2021-03-23: I do not know if it was in the old version; however 7.0 implements the hot-key Ctrl+` (Ctrl + backward accent (grave accent)) to switch the modes show formulas / show results. Try it. :)

Canned Man
  • 734
  • 1
  • 7
  • 26
pepr
  • 20,112
  • 15
  • 76
  • 139
  • Hi pepr Does this help? https://superuser.com/a/69355/598224 –  Jan 11 '20 at 14:11
  • Thanks @pnorton. The checkbox was off, but switching it on, restart, and switching it on again made it working. I had also to retype the formula. The restart of the Calc may not be necessary. Please, type a short answer, and I will accept it. – pepr Jan 13 '20 at 07:58

7 Answers7

12

Try pnorton's answer first, but if that still doesn't fix it for you, then it might be because the cell where you entered the formula is formatted as "Text". To fix this, select the cell, then:

  • Format menu > Cells... > Numbers tab > Category: select Number (or any format other than Text).

IMPORTANT: You need to then re-enter the formula. The cell's content is technically still just text, so you need to force LibreOffice to re-interpret it as a formula. It's not enough to "recalculate" – it's not even enough to click in the formula bar and press enter. For example, you could do one of the following:

  • Copy the cell, then Edit > Paste Special > Paste Unformatted Text.

Or...

  • Click to edit the cell's formula, add a space at the end, delete the space, then press enter.

Before:

formatted as text

After:

formatted as number

(Works for me in LibreOffice version 6.3.6.2 for macOS.)

Chris Tollefson
  • 375
  • 1
  • 5
  • 9
  • 3
    But what if **I actually need the type of those cells containing the formula to be the text**? Because my formula is concatenating a couple of other text cells together, so it's producing text again. It's quite common to have formulas producing text, isn't it? There must be some way to have formulas in text cells, I guess. – David Ferenczy Rogožan May 24 '21 at 03:34
  • Good question – it is indeed common to produce text from formulas. In that case, I just leave the cell formatted as the default "General" format (which is under the "Number" category, oddly): for me it seems to correctly format the resulting text anyway. (For my part, the only time I ever deliberately use the "Text" format is when I enter a cell value that _looks_ like a number but I need LibreOffice to interpret it as literal text instead.) – Chris Tollefson May 24 '21 at 08:20
6

How to turn on/ off Show Formulas in Libre Office

1. Select Tools -> Options from the tools menu (See Fig 1)

2. Expand the Libre office Calc tab (See Fig 2) and select View. On the right hand side select/deselect the formulas Check box.

Libre Office Tools Options Menu

Libre office Calc tab with view selected and on the right hand side the formulas Check box

Libre Office view Formulas

  • Thanks for the didactic answer. :) – pepr Jan 15 '20 at 10:39
  • 1
    Hi pepr. Thanks for the dictionary add+ ( didactic ) I do have a soft spot for Libre Office. If you need any further help just let me know. All the best –  Jan 15 '20 at 16:57
  • This checkbox seems to have no effect for my spreadsheet. Tried all combinations of checking/unchecking and restarting. – Nick Apr 07 '20 at 01:52
  • Hi, Nick What version of Libre are you running? What OS are you using? what are you seeing in your sheet? Who has the privileges? As a side note love the mention of "Shadetree Mechanic" Do you see that much difference with code? Apart from "getting your hands dirty" which could be a metaphor :D –  Apr 08 '20 at 06:42
  • this is not working for me on "Version: 6.0.7.3 Build ID: 1:6.0.7-0ubuntu0.18.04.10" using: Ubuntu 18.04.5 LTS does anyone have a hint to correct for this ? – Melissa Sep 25 '20 at 06:04
  • Hi @Melissa. I'm sorry to hear you're having trouble with LibreOffice. What version of LibreOffice Calc are you running? Help->About LibreOffice –  Sep 26 '20 at 17:17
  • Hi @pnorton I did mention the version in comment above "Version: 6.0.7.3 Build ID: 1:6.0.7-0ubuntu0.18.04.10" is there more around version that would help? – Melissa Sep 28 '20 at 18:02
  • Hi, @Melissa Thank you for your reply. It appears I am human. I apologise for my mistake; you did indeed include the version. I'm sorry to say I could only recommend updating to the latest Ubuntu 20.04 LTS (Focal Fossa). I might also be tempted to update LibreOffice to LibreOffice 7.0.1 (Current version) I'm sorry I couldn't be of more help Melissa. All the best –  Sep 30 '20 at 08:44
  • @Melissa, if it still shows the formula despite this setting, check if the cell is formatted as "text" – that was the problem for me. (I've added an answer explaining how to fix that.) – Chris Tollefson Oct 04 '20 at 04:08
  • Is this a bug in Calc? This seems to be very strange behavior. – Lee Blake Feb 04 '21 at 23:40
4

If the spreadsheet file format is correct and the formulas are live and not just text (live formulas lead with the = symbol), then the shortcut

Ctrl + `

will toggle the entire sheet between the values and the formula that generate them. It has the same effect as (in LibreOffice Calc) Tools > Options... > LibreOffice Calc > View > Formulas being ticked, but is clearly significantly faster for toggling to review/debug a spreadsheet.

J Collins
  • 2,106
  • 1
  • 23
  • 30
1

I had the same problem on a newly created spreadsheet. I solved it by copying the data into another new file and re-entering the formulas. In this second spreadsheet, everything was normal. Strange enough... LibreOffice 6.3.5.2 (x64).

pschl
  • 21
  • 4
0

Same problem. I seem to achieved it by copying the formula into a previously unused cell, deleting all where I want it to be, then copying back

user1791983
  • 371
  • 3
  • 2
0

I had the same problem yesterday. Neither of the previously mentioned solutions worked for me (changed cell formats, and verified that the display formula option was not accidentally enabled). It seems like this was an issue with the spelling checker (right-clicking and selecting Ignore All was what ultimately solved my problem!).

I'm not sure why the spellchecker was insisting to suggest a correction for a VLOOKUP formula, but if I find a way to reproduce this weird bug, I'll try reporting it upstream.

rolandog
  • 159
  • 4
  • 9
  • 2
    Your case can be similar to my. The weird behavior (in my case) was related to using Ctrl+; (semicolon) when entering the current date. The problem was that when the keyboard was switched to my native language the physically same combination of keys was actually Ctrl+`. So, I was unknowingly switching the mode for displaying/executing the formulas. That could be related also to your switching the language. – pepr Jan 27 '23 at 15:03
  • Oooh, I hadn't thought about that! You're right a possibly accidental typo on my part due to keyboard layout. For my work computer I have to use a layout with dead keys, and I would've needed to type ```AltGr + Shift + '``` to get double quotes... which cannot rule out the error between chair and keyboard. I'll have to test it next week! – rolandog Jan 28 '23 at 16:55
0

Text import wizard is not evaluating the formulae. You can enable under other options.[enter image description here][1]

Once this option is checked , restart libreoffice calc. Calc should not show formula. This worked for me. [1]: https://i.stack.imgur.com/p1eyt.png

Ratna Kumar Boddu
  • 1
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Aug 01 '23 at 05:37