How do I prevent a cell from displaying a formula instead of the formula’s result? And, how do I set an option so that I never have to deal with this issue again?example.ods
And, how do I set an option so that I never have to deal with this issue again?
There is no such option. Sometimes you want see which formulas you are using. See also my answer.
I just want to say, since this is the top hit for “libre office shows formulas as text”, that I’ve encountered a similar issue with Libre Office Calc for Linux 7.3.7.2. In my case “Show formulas” was switched off and formatting as “Number → General” did not immediately fix it. Here are my steps to reproduce:
- Open a CSV file, and specify particular columns to import as text.
- Create a new column to the right of a column imported as text. It will inherit formatting as text.
- Enter a formula. (I used either arrow keys or mouse to select cells to include in the formula). It will be displayed as the text of the formula, not its result. So far, so reasonable (although one could debate whether formatting should be inherited like this as default).
- Reset the formatting, either using “Clear Direct Formatting” (CTRL+M), or going to Format → Cells… (CTRL+1) and selecting All → General or Number → General. The cell still shows the formula.
- Select the cell again (either F2, double-click cell, or click formula bar). The cell references in the formula will be colour-coded, suggesting Calc thinks this is a formula rather than plain text. Press return. The cell still shows the formula.
- Select the cell and add a space to the end of the formula. Only now will the cell display the formula result. (You can achieve the same result by going to Find and Replace and replacing “=” with “=”, which I think forces the formula results to refresh.)
My expected behaviour would be that the cell content should change at step 4, or at least step 5. Step 6 seems to be a cludge. Is this worthy of raising a bug report?
I would also expect cell references only to be colour-coded when the input is to be interpreted as a formula, and to remain plain if the input is to be interpreted as text. This is probably more of a UI improvement than a bug, but if it’s not in the pipeline, perhaps someone can add it.
2 Likes
if you need a formula … dont preformat as TEXT! thats all!
3 Likes
@karolus
I am describing a scenario where a formula has already been entered into a cell formatted as text. The formatting was silently inherited, not manually set, so your solution “dont preformat as TEXT” does not apply.
Regardless, the issue is that an undocumented workaround is necessary to refresh formula cell contents after fixing the formatting. I am asking the community to determine whether this needs a bug report, since it doesn’t look like users can raise one directly.
I don’t see how this could happen without active intervention!
Already explained above. My question is whether the undocumented workaround should either be documented or its necessity removed via bug report.
what happens if you disable:
→Tools→Options→→Calc→General→→ []expand Formatting
??
This was already disabled in my Calc. Enabling and disabling again didn’t have any effects. From the docs, this may only apply when entering values into adjacent empty cells, not when inserting new cells.
(Disclaimer: →Tools→Options→Calc→General→→expand Format is not enabled. )
I can now reproduce the issue:
- open new Calcdocument
- Select Column A
- →Format Cell→→Numbers →→Text @
- insert a Column before (textformat is NOT inherited in the new Column)
- insert a Column after Column B (which was initial A which Textformat) (this Column inherits Textformat!!!)
I would classify this as bug … @erAck ??
with me:
Version: 7.0.4.2
Build ID: 00(Build:2)
CPU threads: 4; OS: Linux 6.1; UI render: default; VCL: gtk3
Locale: de-DE (de_DE.UTF-8); UI: de-DE
Raspbian package version: 1:7.0.4-4+rpi1+deb11u6
Calc: threaded
and same behavior with a flatpak 7.0.1.2
1 Like
It seems this might be intended behaviour. Check out the animation in the answer from LeroyG:
That’s unrelated. Expand formatting is executed when entering values into empty cells that aren’t formatted yet but are adjacent to a cell where a value was entered and the cell then formatted, i.e. when entering values in a row or column.
Attribution is always copied from the column left / row above to the insertion. Inserting before column A there is no column left to it, hence no attribution. Can it be expected that attribution would be copied from the column right in this case? Maybe.
August 9 2023:
Some people consider automatic formatting, and especially that of displaying formulae instead of results, a feature. I consider it an “anti-feature” and an obstacle to production.
Call it a bug or whatever. But, remove automatic formatting and styles and remove, or allow to be disabled, the function that results in formulae being displayed instead of results.
Note that this problem of displaying formulae instead of results is not limited to Calc. Versions of Microsoft Excel beginning after 97 for Windows and 4.0 for Mac have this problem and it is a continuous help topic issue.
To add to discussion, in too many programs, LibreOffice Calc, Writer, Excel, etc, it takes too much time to determine how to turn off or disable functions. For me and for the writing I do, automatic styles are an obstacle to production. I prefer hard formatting and complete disabling of automatic styles. That’s me. Other’s may have differing opinions and I respect that.
———————————————————————————————————————————————————————
pwrightkcl
August 8
@karolus
I am describing a scenario where a formula has already been entered into a cell formatted as text. The formatting was silently inherited, not manually set, so your solution “dont preformat as TEXT” does not apply.
Regardless, the issue is that an undocumented workaround is necessary to refresh formula cell contents after fixing the formatting. I am asking the community to determine whether this needs a bug report, since it doesn’t look like users can raise one directly.
Visit Topic or reply to this email to respond.
To unsubscribe from these emails, click here.
See How to Report Bugs in LibreOffice - The Document Foundation Wiki
This is the same behaviour as Excel.
2 Likes
It is not “undocumented”: see FAQ.
The thing that user needs to learn to understand what they do, and how to operate the application properly, is: there is a phase of data entry (when they type something into cell/formula bar); and then there is a phase of interpreting of the input into the cell content (it happens after the entry is finished). After that interpretation, the cell content will stay unchanged, until there is another editing action - and simply putting cursor into the formula bar / entering cell edit mode is not an edit, only typing something is.
The FAQ shows other methods to tell the program to re-interpret the cell content: e.g., using Find & Replace; or Text to Columns. Despite talking about numbers, the FAQ actually covers any data that user may enter into a cell, including formulas. This is a universal convention used in the spreadsheet software industry; if it looks unexpected by spreadsheet software users, they need a bit of learning to start using it efficiently. Thinking that a novice user’s problem could be “solved easily” by dumbing the software and making its behavior non-standard is naïve, and would break infinite more complex usage scenarios.
2 Likes
@mikekaganski , other than pointing out a third source of documentation in addition to the in-application help and these forums, your reply offers nothing useful. Bear in mind, neither confused2 nor I are novices.
I have now found the submit a bug feature (which wasn’t linked from the help pages) I’ve submitted a documentation update request with details, so it is over to the devs now. Thanks @EarnestAl for the link.
The usefulness and effectiveness of a computer program is inversely proportional to the amount of training and support required.
by ML Schumann
————————————————————————————
confused2
pwrightkcl
August 10
@mikekaganski , other than pointing out a third source of documentation in addition to the in-application help and these forums, your reply offers nothing useful. Bear in mind, neither confused2 nor I are novices.
I have now found the submit a bug feature (which wasn’t linked from the help pages) I’ve submitted a documentation update request with details, so it is over to the devs now. Thanks @EarnestAl for the link.
Visit Topic or reply to this email to respond.
To unsubscribe from these emails, click here.
Sounds great, until you think of CAD programs, C++, and all the high level programs that support our industries and lifestyles. Generally, the more complex the task, the more complex the program needs to be.
Training is needed for most human activities, even crossing the road safely.
Hello,
there are two different reasons why you don’t see the result of a formula evaluation but the formula itself.
1. Formatting your cell as Text
If you format a cell as Text (Format Code: @) before you enter a formula, you disable any input recognition and you get your formula as text. If you don’t do so, entering = tells LibreOffice that your are about to enter a formula.
Solution: Remove the text format (CTRL+M) and edit the formula.
2.View Option
You may have set option View -> Show Formula.
Solution: Unset the option
Hope that helps.
Update - see my comment below:
to Opaque
Thanks for your answer. However, my attempts using your instructions did not work. Also, CTRL +M closes a work sheet. CTRL +m seems to do what your intentions imply. Just a comment, but, I NEVER use keyboard command - always mouse and menu. This is because I use too many differing programs on my Mac, PC and Linux. Having to lift my hand from the mouse, except for data entry is quite frustrating.
I began using Excel in 1986 and have hundreds of specialty spread sheets on my Mac and never encountered this formula display problem. Surely there must be a way to eliminate it?
to igortius
I’ve attempted to attach a spreadsheet exemplifying the problem.