Spreadsheet Cells Will Only Show Formula Not Results

English
,
#1

I created a spreadsheet in Libre Office a few weeks ago. Today when I opened the spreadsheet instead of the results I just saw hash marks, ###. So I deleted the contents of those cells and entered a formula, instead of the result I got the formula, “=A1*B1”. I even created a new spreadsheet and no matter what I do I only get the formula, in my example, “=A1*B1”. I doubt this matters but the functions are only addition, subtraction, multiplication and division.

EDIT: LeroyG added Preformatted text marks to the formulas.

#9

The marks mean “cells are not wide enough”.

Which may hint that the problem is that you maybe had activated View|Show Formula, and everything became “wider”?

3 Likes
#10

I am used to the hash marks indicating that the cells are not wide enough from Numbers on the Mac or Excel. That being said when I double clicked to widen the cell I got “=PRODUCT(B1, C1)” even though the format is set to number. For example, “=B1*C1”, “=PRODUCT(B1,C1)”. I have tried multiple formats and nothing will calculate. It is so frustrating and confusing. When I right click for format cell, under the numbers tab, the format code is “General” which is not something I typed in. It is also interesting that is appears to be a field where one can input literally anything as there is not drop down box with limited options.

#12

You didn’t mention if you have checked the menu item that I described.

#18

Notice that there is a difference (on defaults) between “Show Formula” and having cell formatting set to “Text”. Show Formula will right justify and Text will left justify.

#2

Check your formula. For example, the formula for the addition of cells A1 and B1 is =SUM(A1:B1)

#3

Judging by the italics, I think Ask picked up the asterisks in his formula =A1*B1 and turned it into italics. I have strong opinions on this implementation of formatting here on the site (and elsewhere that this sort of behavior is used), but it’s not relevant to the discussion, here.

#7

Learning how to use the site usually helps, allowing one to understand that you use backticks to wrap your code to avoid any formatting.

1 Like
#4

For Bruce, as to the problem: Check the formatting on those cells, specifically the Numbers tab. My guess is that they got set on “Text.”

I’ve run into this problem often over the years, usually when I was trying to mix manual-entry text with formulae calculating text.

If there’s a reason you intentionally set the numbering style to “Text,” then we can talk about workarounds. If it wasn’t intentional, then switching it back to “General” or another numbering style should fix the issue, though you’ll have to go to each cell, hit F2 to start editing the cell, and then hit Enter again to have the formulae start acting like formulae again.

#5

A thought, though I’m not a LO expert rather have a history of MSOffice, wouldn’t there be a global way: select the whole sheet or relevant area, and choosing “general”, without having to re-enter each cell? And if that was necessary, would a re-calc work ?

#6

In my experience, once a formula is forced to text, it has to be individually “bonked on the head,” as it were, to go back to an actual formula.

I haven’t figured out any other way (or frankly had much patience to try, most of the time) than just doing it manually. So far, it’s always been a small enough list that it was fast enough, anyway.

1 Like
#8

FAQ

#11

This is so bloody confusing. Later this morning I will boot into LO on Linux to see if this might be an issue with LO for the Mac. I have become a big supporting of Open Source software after experiencing QuickBooks essentially making it impossible or at least very difficult to export my own data to another format. Having said that I love the design aesthetics of the Mac along with the ability to easily share data between my Mac mini, iPhone and Apple Watch.

#13

Well I have something of an answer. It seems to be an issue with the Mac OS version. Dual booted from my Mac Mini into Linux Mint and the same spreadsheet opened without issue. To give a little more background, LO has been buggy on my Mac from the beginning frequently crashing directly on opening especially when Numbers (the Mac spreadsheet program) is open. I could be incorrect on this point but I think that there was a recent LO update after which I started having problems. Guess I need to report this to the developers.

Thank you to all of you who have responded to my post.

#14

Give version of LO and MacOS and post example ODS so we can test on same and different versions–only way to get something fixed is to isolate the problem and, if needed, file a bug report.

#15

Hi Joshua4,

I am running Version: 7.3.2.2 / LibreOffice Community
Build ID: 49f2b1bff42cfccbd8f788c8dc32c1c309559be0
CPU threads: 12; OS: Mac OS X 10.16; UI render: default; VCL: osx
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded

Mac OS Monterey 12.3.1.

To be fair I have had issues with LO on my Mac since last year. It seems to have issues with Numbers. Initially after donating to LO and downloading the most recent versions for both Mac OS and Linux the program never finished loading resulting in “the spinning beach ball of death”. A recent update of Mac OS and/or a LibreOffice update fixed the issue for awhile.

One more thing that is probably not relevant but just in case, the spreadsheets are on a shared petition formatted as ExFAT so that both OS’s can read them. Also I checked and columns B - D are formatted as “Numbers”.

Here is a screenshot of a simple spreadsheet.

To end on a positive note these same LO spreadsheets are beautiful in Linux Mint.

Screen Shot 2022-04-21 at 16.18.18
Screen Shot 2022-04-21 at 16.18.181691×435 162 KB

#16

As mikekaganski hinted already twice, you may have activated View → Show Formula.

3 Likes
#17

Minor correction to @altasilvapuer’s answer: Hit F2 to start editing, then, say, add a space at the end of the formula, then hit Enter. Just pressing Enter won’t trigger any changes. Tested with LO 7.3. The same is true when converting to/from array formula with ctrl+shift+Enter or Enter.

1 Like
#19

I cannot duplicate any difficulties using LO 7.3.2.2 on MacOS Catalina. It could be a Monterey thing (there seem to be several of those). The right justification of the formula shown makes it seem like all newly entered cells are getting flagged as Show Formula, or else formulas are getting entered as text.

To test this, try adding the ConvertTextToFormula macro (and the subfunction UsedRangeStruct) in the ODS below to your ‘broken’ ODS and run ConvertTextToFormula. If the problem is with Show Formulas then this won’t change anything. However, if the problem is that formulas are in fact entered as text, this may fix the broken cells.

FormulaStringValueMacro.ods (27.7 KB)

#20

Vielen Dank erAck and thank you mikekaganski. I feel rather stupid that I missed mikekaganski’s point about the View|Show Formula. That was the problem. Now I can return to using LibreOffice for nearly all of my spreadsheets and documents. I apologize for missing this point.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.