LibreOffice Calc shows formulas in the cell instead of results
Asked Answered
V

8

13

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. :)

Vallation answered 9/1, 2020 at 14:8 Comment(2)
Hi pepr Does this help? superuser.com/a/69355/598224Acceptable
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.Vallation
A
7

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

Acceptable answered 14/1, 2020 at 9:30 Comment(10)
Thanks for the didactic answer. :)Vallation
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 bestAcceptable
This checkbox seems to have no effect for my spreadsheet. Tried all combinations of checking/unchecking and restarting.Phinney
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 :DAcceptable
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 ?Urogenital
Hi @Melissa. I'm sorry to hear you're having trouble with LibreOffice. What version of LibreOffice Calc are you running? Help->About LibreOfficeAcceptable
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?Urogenital
Hi, @Urogenital 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 bestAcceptable
@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.)Crumby
Is this a bug in Calc? This seems to be very strange behavior.Waldon
C
15

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.)

Crumby answered 3/10, 2020 at 4:22 Comment(2)
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.Milner
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.)Crumby
A
7

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

Acceptable answered 14/1, 2020 at 9:30 Comment(10)
Thanks for the didactic answer. :)Vallation
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 bestAcceptable
This checkbox seems to have no effect for my spreadsheet. Tried all combinations of checking/unchecking and restarting.Phinney
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 :DAcceptable
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 ?Urogenital
Hi @Melissa. I'm sorry to hear you're having trouble with LibreOffice. What version of LibreOffice Calc are you running? Help->About LibreOfficeAcceptable
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?Urogenital
Hi, @Urogenital 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 bestAcceptable
@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.)Crumby
Is this a bug in Calc? This seems to be very strange behavior.Waldon
P
5

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.

Plunder answered 8/7, 2021 at 15:4 Comment(0)
C
0

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).

Cystitis answered 11/5, 2020 at 11:46 Comment(0)
S
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

Snowstorm answered 23/3, 2021 at 0:33 Comment(0)
B
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.

Bewitch answered 26/1, 2023 at 12:18 Comment(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.Vallation
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!Bewitch
D
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.sstatic.net/p1eyt.png

Deviltry answered 21/7, 2023 at 18:38 Comment(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.Credible
F
0

Sometimes, when entering a formula, I get the result; sometimes I just get the formula. Both can happen on the one sheet.

I found that by selecting a cell which displays only the formula, then left-clicking on the '=' immediately to the left of the box displaying the contents of the currently selected cell, the result of the formula is displayed.

Before any questions about my knowledge, yes, I had started the formula with the '='.

Fluoric answered 21/9, 2023 at 13:40 Comment(0)

© 2022 - 2025 — McMap. All rights reserved.