Do you find some fields not adding up in Calc because the formatting was somehow altered?

English
#1

Honestly I find so many illogical things in Calc. Is MS Excel more logical? I could write an encyclopedia on Calc’s deficiencies after using it for a few months. I’d love to make it work properly. Is this typical or am I just dumb with spreadsheets? Like so many conventions we’re used to in Word or text programs don’t work in Calc even though they could. So often I start typing in a field and the formatting is different for no logical reason. Obviously I’ve changed it somehow by copying and pasting something into it. I’m trying to always paste unformatted to minimize this from happening.

Today I noticed one column’s SUM didn’t look right and sure enough 2 of the fields weren’t being included in the =SUM(A1:A5) formula. I subtracted the Calc SUM from what it should be and found them and entered them manually and all was well. There should be a way of pasting things without changing formatting. Isn’t that obvious to any sane person? If the whole spreadsheet is in a set format why would anyone want to change that when pasting info into it? It’s like this program was designed by crazy people with no semblance of logic.

All the time a column’s formatting will change as well, especially in terms of alignment when pasting stuff. Drives me nuts. Obviously a column should retain it’s alignment!!!

Is there really no option to retain formatting when pasting other than always pasting unformatted text which isn’t as fast?

Spreadsheets are a miraculous idea. Now to get them to work well. I admit I’m a newer user and am learning things all the time whereas I’ve been using text based software for decades. They should really teach spreadsheets in high school. Few things could be more useful in life for keeping track of things.

#2

Please start it. We always love to improve. Just make sure there is no lack of knowledge of the tool.

Excel? No idea, here we do who hasn’t used it again in more than a decade.

#3

Judging from the points you mention: No, not at all. A lot of this is exactly the same, wich is useful if you create sheets, wich should also work in Excel.
.

Once upon a time Excel and other spreadsheets were sold as separate program. And nobody cared much to be consistent with something like Word.

#4

Once upon a time Excel and other spreadsheets were sold as separate program. And nobody cared much to be consistent with something like Word.

This explains a lot. And it makes perfect sense. Why alter something if you don’t have to? Human nature. When these programs first emerged they probably didn’t have any idea how integrated they would get in the future. Or they didn’t realize how useful these programs would be to everyone. Calc should really try to follow conventional aspects of text programs. It would streamline a lot for new users. They do somewhat of course. The biggest issue I see is formatting changes within a document. Drives you nuts. You start typing in a new field and it’s all different. There should be a setting that just stops that by default. They do this with many text programs. You copy text from one program at 50 Verdana font and paste it and it becomes 10 Consolas mono-spaced and looks perfect. Because the destination is 10 Consolas so why would you want anything different?

I’m so glad I found this forum. Of course they don’t call it a forum like everyone else! And there’s nothing in the URL indicating anything about it except the title of the post other than “ask”! Everyone else would title it
forums.libreoffice.org
like logical people. :slight_smile:

#5

They don’t call it a forum because it isn’t a forum; it is question and answer site which is a different thing. Q&A software - Wikipedia

#6

Query your own logic.

There are problems with your “central” settings for people (like me) who do a lot of different things. Every time I switch projects I would have to change all necessary settings. (No problem, we call it profiles now and generate the next problem how to sync my stuff between profiles.) The other problem is the number of settings to be managed by new users to, so they are hidden in big menues, Tools>options, “expert configs” …
.
For LibreOffice Writer, Calc etc. you should create templates and style them, before you start. But you are free to start without plan as WYSIWIG was invented years ago and we have now much more space on screens (on desktops) for buttons and toolbars. But then don’t be surprised when some ignored basic setting is still found in seemingly empty cells (or paragraph settings).
.

Maybe databases… And set up properly they even ignore the formatting of stuff you copy in like a text editor.
.
And speaking of logic. Why would a software to “keeping track of things” be named Calc. You already told “It’s like this program was designed by crazy people with no semblance of logic.”, but maybe they just follow other priorities and expressed this with a little hint in the name.

2 Likes
#7

What is obvious to me is that this phrase makes it unclear, which formatting should be kept unchanged: of the destination, or of the source.

It seems that you have a very narrow perception of the needs of people. People want to keep the source formatting slightly less than always (the more expert the user is, the more often they paste unformatted, which means, that most users - who are the basic users - expect formatting of the source to stay; and there are tasks requiring formatting to be kept regardless of the user expertise).

In general, the idea that it’s as simple as making Writer from Calc is naive. Calc is designed to work primarily with numbers. It includes a vast range of tasks not relevant to Writer (and vice versa). It covers insanely different needs of users from different backgrounds and areas - like statisticians, accountants, analytics, mathematicians, programmers, just a casual users wanting to do some basic calculations, or people misusing Calc as a database, and you-just-can’t-imagine-who-else. It has to actively detect numbers in user input, and at the same time, provide means to work with text, and allow formatting - both static and dynamic, both direct and styled. And it must handle millions of cells, and at the same time, still be fast (which is very difficult).

The “discussions” like here are basically useless. If you want to have a fruitful discussion (even if you disagree to learn first, or to accept the possibility of an unimaginable event that people may think differently and have different needs, and still be logical and not crazy), you could provide a specific issue (not a range of “formatting + formula not counting + site name”), with a specific steps for others to see what you are talking about (maybe with example document; maybe with screenshots and screencasts, and with specific mouse click / key press sequence), your expectations, the actual results, and after that, maybe your reasoning why your way should be The Only One.

On the other hand, I see I already reacted to your “I know the truth” style. Some people never learn. I totally agree with @Wanderer here.

#12

30 years ago, office programs used to less sophisticated than today. At the same time, it was just a matter of course that anybody trying to master these programs would buy some “Excel for Dummies” book. These books were offered in supermarkets. You are missing some central concepts of all the spreadsheets released in the past 30 years.
[Tutorial] Ten concepts that every Calc user should know

#13

Be proud that you can call anything whatever you want. However, some terms could unexpectedly evolve their definitions, and some additional terms could be created to disambiguate use cases, since you learned them in Roman Empire time. And people who create a site and design it for a specific kind of operation have no need for your language lessons.

#14

Text to the left, numbers to the right. If do you see numbers to the left, they are text not numbers.

#15

Actually for a long time “forums” were expected to be basically “peer to peer”, and next to everybody visiting them was expected to not only have come to find solutions or cast opinions, but also to contribute help for others.
Visitors to Q&A sites often are acting as pure “consumers” and tend to treat contributors of well considered answers as if they are hired for the job by those visitors - or possibly by anybody else, They aren’t.
I’m really getting tired of reading posts by visitors not contributing anything of value, but teaching experienced users and even experts their know-it-all stuff.
Please find a “forum” that welcomes that type of visitors.
See also this note about freeware. by Paul Lutus, specifically the section about rights, respnsibilities, and remedies. It is now more than 20 years old, but probably more relevant than ever.

1 Like
#16

They don’t call it a forum because it isn’t a forum; it is question and answer site which is a different thing. Q&A software - Wikipedia

That link doesn’t discuss forums so there’s no comparison. Not sure why you would post it.
Look, this is a help site to guide new users. That’s it. It can be used by anyone, either through questions or answers. The rest of the world calls that a forum. If you want to be different, so be it. But it doesn’t make any sense to label something different for no logical reason. Because you don’t want people giving up on your product for the wrong reasons and you don’t want to use up company resources assigning one or more people to answer all their questions. So you let the public contribute and browse it periodically to make everyone is behaving. Forums are a great idea. I’ve learned more on forums in specific areas than anywhere else online, best idea ever.

Speaking of this website, why am I seeing the same thing I’m typing on the left side mirror on the right? There’s no explanation or description or title for the right side and left side. When I click the diagonal arrows it opens up sort of full screen. But they forgot to include the Reply button. There’s plenty of room at the top of the page.

#17

There are problems with your “central” settings for people (like me) who do a lot of different things. Every time I switch projects I would have to change all necessary settings. (No problem, we call it profiles now and generate the next problem how to sync my stuff between profiles.) The other problem is the number of settings to be managed by new users to, so they are hidden in big menues, Tools>options, “expert configs” …
For LibreOffice Writer, Calc etc. you should create templates and style them, before you start. But you are free to start without plan as WYSIWIG was invented years ago and we have now much more space on screens (on desktops) for buttons and toolbars. But then don’t be surprised when some ignored basic setting is still found in seemingly empty cells (or paragraph settings).

I have to admit those are excellent points!

Maybe databases… And set up properly they even ignore the formatting of stuff you copy in like a text editor.
And speaking of logic. Why would a software to “keeping track of things” be named Calc. You already told “It’s like this program was designed by crazy people with no semblance of logic.”, but maybe they just follow other priorities and expressed this with a little hint in the name.

Good point. There’s plenty of software that have names that are not so intuitive.

#18

Thank you for that very useful page. It’s great. Just what I needed.

#19

Text to the left, numbers to the right. If do you see numbers to the left, they are text not numbers.

That makes sense and that’s what I’ve been doing. Good to hear I’m doing something right intuitively with Calc!

#21

Just wanted to thank all the posters. Hopefully this discussion will help a lot of other new users like myself. It helps us understand the type of things Calc is doing and not treat it as a text program even though a lot of the info on it may be text. You see text and you expect it to behave like text has always behaved in word processors.

#22

What is obvious to me is that this phrase makes it unclear, which formatting should be kept unchanged: of the destination, or of the source.

Oh the destination absolutely. Remember you have got it all organized looking exactly how you want. So wherever you copy and paste into, most people would want it to follow what’s around it.

People want to keep the source formatting slightly less than always (the more expert the user is, the more often they paste unformatted, which means, that most users - who are the basic users - expect formatting of the source to stay; and there are tasks requiring formatting to be kept regardless of the user expertise).

I’ll take your word for that. At least they should give us the option. Sort of priority option I guess.

In general, the idea that it’s as simple as making Writer from Calc is naive.

I just meant that when you are working with text in Calc it would seem logical to use the same keyboard shortcuts that most all word processors use. So often I have to use the mouse to position things because keyboard shortcuts don’t work. I just can’t see the harm of using these shortcuts since they are not used by anything else in Calc. I’m a keyboard guy and I realize many are mouse people. By the time they have reached for the mouse my keyboard shortcut has already been performed, meanwhile they have to position the mouse cursor, click it, then come back to the keyboard. It’s so ponderous. Typing should be taught in school! No one should be able to pass without learning it.

You mention people “misusing Calc as a Database”. I’m curious as to what you mean as I’m always looking for better ways of organizing stuff on my computer. I haven’t even used Database software before.

Later you make some good points about the enormous range of people entering data into Calc. It makes a lot of sense. I can see a word processor is a lot more limited in it’s range of users. As I learn more about spreadsheets this will be become more evident no doubt.

#8

Do you find some fields not adding up in Calc because the formatting was somehow altered?
No. (See below.)

No. (At least not any option which is more practical, AFAIK)

“Retain formatting” is always a choice between retaining source formatting vs. retaining the format of target (preformatted cells). Ctrl-Shift-V then Enter is only slightly slower than Ctrl-V, as long as you don’t need to use sticky modifier keys. The first “paste special” may also require you to select options as required, but your selection should stick on the next paste-special.

“Not adding up” is usually caused not by formatting, but by non numeric data type (i.e. text data in current Calc context). If the clipboard content is copied from another spreadsheet (or other data application which discerns between data types), the data type will be carried over, independently from format, as a “paste suggestion” to Calc. This is not very different from other spreadsheets.
Calc will try to interpret numeric text as number only in explicit formulas (=A1+B1), but assume zero when referenced by formulas which accept sets/ranges of cells (=SUM(A1:B1) ). Again, other spreadsheet apps do the same.

1 Like
#10

This is really important to know. Thank you.
If one is a beginner it would be prudent, if moving info between your spreadsheet documents, to make sure the formatting is the same for all. Otherwise you’ll get some surprises that will ruin your day. And if you’re not backing your stuff up…really ruin your day.

#11

Click View > Value highlighting to get visual feedback on data type. Blue for numbers (including dates), Black for text, Green for formulas.
Calc Number text to Numeric data
Preventing data disaster - The Document Foundation Wiki

1 Like

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