Design/Whiteboards/Conditional Formatting
< Design | Whiteboards
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
 |
This page was marked as inactive and is retained for historical reference.
|
Summary
More usable dialog for Calc's Conditional Formatting feature (Format → Conditional Formatting...)
Status
| Call for Proposals | Analyzing Proposals | Shaping Tentative Design | Implementation in Progress | Implemented |
Scope
| In Scope | Out of Scope |
|---|---|
|
|
Relevant Bugs
Definition of Terms
- Conditional formatting: If a style is applied to a spreadsheet cell when a certain condition regarding its value is met.
- Overlapping Ranges of Conditional Formats: If one defines conditionally formatting for two or more ranges that partially overlap, like A1:B4 together with B2: C3.
Personas
- Geraldine: An HR person who uses a spreadsheet to track work metrics of employees, such as overtime, pay, and illnesses. She is a visual type and thus likes colorful visualization methods.
- Hermione: Hermione is a home user of LibreOffice, who has recently begun investing in the stock market. She uses a macro to insert live stock market data into her spreadsheet. To get a quick overview over all relevant factors, she uses conditional formatting.
- Ingrid: Ingrid is responsible for interpreting the results of a survey. She uses Calc to do so and conditional formatting helps her visually spot things like outliers etc. even before actually performing actual math.
Proposals
Proposal by Astron
Mockups
Overview over proposed new UI
"Pretty" Mock-up of part of the new UI
UI for color scales
UI for data bars
Description
Defining conditional formats
- the usual workflow of defining a conditional formatting remains, i. e.
- select a range of cells
- open Format > Conditional Formatting > Condition...
- the first condition is readily available
- this window needs to be resizable
- adding a mechanism for sorting conditions is not part of this proposal, but could be added later on
- the preferred way of sorting should be drag and drop, but development resources are too limited right now to implement that (as per discussion with Markus)
- adding ↑/↓ down buttons for sorting is simple but seems a clumsy workaround (it could be used as an accessible fallback method though)
- it might prove relatively hard to make the user adequately aware of status changes without some use of animations (see the slide sorter in Impress which now uses very nice animations when dragging and dropping)
- the style preview area(s) should now show the text that is also shown in the top-left cell of the range
- the Add button is below the field of conditions [now]
- as per discussion with Mirek/Alex, if the Add and Remove buttons were shown among the conditions, some users might double click, thus first adding, then removing a condition (even worse, users might double click on an existing condition and it would just disappear) – it could mitigated by adding a short timeout before the Remove button appears, but that seems clumsy
- we came to the conclusion that we should standardize on having buttons that manipulate a list below the list
- the button will now never be hidden
- overlapping ranges of conditional formats will not be implemented (as per discussion with Markus)
- thus, the formatting will be overwritten whenever a user tries to do that
- we should make the user aware of that and show a non-modal warning in place of the description text in the Conditional Formatting for ... window ("Applying conditional formatting to this range will delete conditional formatting in another range.")
Color scales (B)
- even though these are just another type of condition (and may even be converted, to advertize the feature, we should have a special menu item Color scale...
- the mockup for color scales tries to be on-par with Excel 2010 with respect to features
- the initial presentation tries to be as minimalistic as possible and relies on predefined color styles (users can discover the "power-user" stuff later on or ignore it)
- it should come up with the "traffic lights" preset (green, yellow, red) and the preset Automatic (the equivalent of Excel's Lowest Value/50th percentile/Highest value) initially
- more options (B.3) allows adding up to five different colors (this is a proposal, and it might help in some special cases like "give me different colors for extremely positive/positive/negative/extremely negative values" – if this seems not so useful, we could slash it)
- after returning from (B.3) to the main conditions window:
- (B.4) will be shown if the value types are mixed now (for instance: Highest value) or there are now more than three colors/values defined
- (B.3) will be shown in all other cases, including changing the colors to something that is not a preset
Data bars (C)
- even though these are just another type of condition (and may even be converted, to advertize the feature, we should have a special menu item Data bars...
- the mockup for color scales tries to be on-par with Excel 2010 with respect to features
- the initial presentation tries to be as minimalistic as possible and relies on predefined color styles (users can discover the "power-user" stuff later on or ignore it)
- it should come up with a green bar/gradient fill (and red bar/gradient fill for negative axis) preset and the preset Automatic (the equivalent of Excel's Lowest Value/Highest value) initially
- after returning from (B.3) to the main conditions window:
- (B.4) will be shown if the value types are mixed now (for instance: Highest value)
- (B.3) will be shown in all other cases, including changing the colors to something that is not a preset
Managing conditional formatting
- the Manage... window is supposed to make it easy to find the areas that one has applied conditional formatting to without having to go through trial and error
- additionally, the window makes it easy to delete conditional formatting rules for entire ranges
Suggestion by Alexander Wilms
- For the ease of use and consistency with MS Office it would be useful to add captions next to the widgets of a color scale/data bar/icon set condition.
- Adding a conditional formatting button to the Calc default toolbar.
Adding a toolbar button
Captions in the UI
Proposal by John Pratt
I have made a mockup showing some suggestions for how I would find this easier to use as an end user.
Proposal for UI Improvements to conditional formatting
- I find the use of 'condition' confusing as it is used to refer to all conditional formats (condition 1, etc in the CF->Manage->edit dialogue), but also as a specific type under CF -> condition. I think it would be clearer if 'rule' was used a general term and 'condition' as a specific term.
- I would fins it easier to follow if I selected the type of conditional formatting and that got into the details. In particular, I find the phrase 'all cells' confusing. I would suggest we select a type of rule (condition, databar, colourscale, iconset) and then get into the details of that type. This would make the number of entries for the colour scale a separate dialogue and I would expect the current choices to stay the same for max/min when the number of entries is changed, as shown on the mockup.
- I'm not clear why the databars have a more options window rather than all options being included in the edit dialogue (all other types of conditional formatting do not have an extra dialogue. I find this less user friendly as I invariably want to change the colours or at least check them, before saving a databar.
