Setting "include formats" persistently when sorting

English
,
#1

Hi!

This is a little annoyance / QOL issue, but when sorting data in my sheets which are pretty (since I’ve formatted them in a way I want them to - alternating background colors and/or lines which don’t depend on the data but the actual line numbers for easier readability) - all the prettyness will be gone since I forgot to uncheck the “include formats” checkbox all the time. Is there a way to set it unchecked permanently? I could not find one, if there is.

I’m constantly in a situation where I need to redo my formatting since I’ve forgotten to uncheck the box, and the chances I’ve made are not in the undo buffer anymore (or if they are, they are very, very far away).

Now, this can be opinionated and my view can be controversial, but IMHO that checkbox should be disabled per default. I can think of only a few situations when I’d like to include formats when sorting, but vast majority of time it is only the data I want to sort, not the formats.

One way to work around the issue would be to use conditional formatting for a range, with functions which know the position (row() / column() functions in conjunction with styles), and hence don’t care if formatting is included; but I feel this should not be enforced on the user.

Cheers!

p.s. (there is another thread with a similar headline but the actual underlying issue the user was trying to solve was different and the user has it solved: Uncheck the "Include Format" in the sort options by default ; I chose to start another thread to keep the discussion relevant).

#2

If it would disabled by DEFAULT - then 8 of 10 would be complain: “why is it disabled … I NEED it enabled” … you understand the issue?

#3

Yes, that’s why I said my opinion (about the default) might be controversional.

Anyways, that part is off topic and does not help in solving the issue. Sorry for writing that paragraph.

Now, the main point as per title is: is there a setting to set this persistently? That would cater to every users need (if there is not, let’s not turn this into a discussion about which would be a more sensible default).

#6
  1. Concerning formatting like “alternating background colors to ease reading” it’s definitely better, imo, to resort to CF (ConditionalFormatting). If you don’t want to do it for all the sheet in the same way, it’s easy to keep a few “templates” In a hidden range of the far NorthEast - or in special sheets or SheetTemplates. If you feel sure that CF isn’t the appropriate means for you, I would like to learn the reasons.
  2. Concerning the sorting in sheets it’s also possible, to write user code (probably a user defined dialog) to set the arguments, and to set the preferred defaults. Concerning the BindFormatsToContent the preset default is also True, but you can change it… A SortDescriptor you created yourself exposes all the needed properties. (Only the EnableNaturalSort is still mising.)
#8

I suggested myself using conditional formatting for alternating backgrouds. However, after working a bit more for a while, in the end I must disagree. The reasons are:

  1. LibreOffice does not work well with overlapping several conditional formattings. They can be set, but there is no easy way to change their order, and even then the result is not what is expected by the user (I tried this, the issues are a bit too complicated to describe here and would warrant another thread).
  2. The user may need conditional formatting based on output/data, which is not known beforehand (such as: highlighting interesting values, outlier, errors etc…).

Alternating background is a static feature of the spreadsheet. As such using conditional formatting is useful only if the user does not need any other kind of conditional formatting - which is not my use case!

I believe writing own dialogs is beyond what can be expected from an average users (time-wise nor skill-wise). I have no idea what is BindFormatsToContents.

#4

The setting is remembered with the last sort area per sheet. For persistent areas use defined database ranges, which also remember the sort settings. Data → Define Range…

3 Likes
#5

…sets as default [x] Range contains columns labels; so unless you unmark it, first row will be no sorted.

#7

Hi,

This is indeed the solution. I didn’t know about the data rangfes feature! However, there are two “problems”:

  1. LibreOfffice doesn’t remember the sorting ranges after re-opening the file. Which means: I still need to manually uncheck it (if not using data ranges). However, if doing many sorts without re-opening the file, the option is remembered.
  2. By setting the data ranges and unchecking the “include formats” option, it is still checked when selecting the data range (from Data->Select Range). I can see how it should work but it is not working as intended, so it seems there are some bugs here.

Because of these problems/bugs, I still need to uncheck the option most of the time, or at least double-check. There is no consistent way to keep it unchecked for any data range, though I see there should be a feature for it.

#9

It is remembered even when the document is saved and loaded in 7.4.1 but apparently not in 7.3 or earlier.

Btw, you don’t need to Data → Select Range before a Sort, just place the cell cursor somewhere in the data range, it will get selected.

#10

Thanks for the information about data ranges - I noticed it does that, but just wanted to confirm that I have not done a mistake.

I’m using 7.4.1.2 community version on Arch Linux here and not checking “include formats” is definitely not remembered over a restart of LibreOffice (nor de-applied when sorting a data range with the option deselected).

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