Created attachment 138782 [details] Range before enter Edit I cannot see a way of doing this: When I wish to do conditional formatting I need to apply a style to the cell. There is no way to create style which, say, only changes to Font to Bold, Caps and Red; leaving all the other attributes as they are in the underlying formatted cell. This is possible to do in Excel by choosing which of the formatting groups (TABS in the style dialog). Also, when I change the range for conditional formatting (Format/Conditional Format/Manage/Edit) the Range in the Name Box disappears, which means I have to remember to write it down before I go there.
Created attachment 138783 [details] Range in Name Box during Edit
I am not sure what you want but try F11; this is the styles and formatting tab on the right site. There you can find cell styles and page styles. New cell style will ask a name and you can then modify font etc. Hope this helps.
Elmar: like Xavier said + you can create child styles that inherit the parents. Then just change font to Bold in one child style, color to red in another child style etc. Try it and report your experience.
Created attachment 141966 [details] final document formatted for readability - colour and monchrome printing
Created attachment 141967 [details] interim: using styles with direct cell formatting
Created attachment 141968 [details] raw data file
I notice that if I stop what I am writing something here and then interrupt that to save an attachment, this tool loses what I have typed. I spent about 1/2 hour typing notes and have had to retype 2x because of being distracted and then forgetting about that and not saving the changes first. Is there no easy way to avoid that? I will try to reconstruct what I tried to say: One of the the formative periods in my career was when I worked for Arthur Andersen Consulting Division in the days before AA&Co was destroyed by the Enron corruption (the division survived because it had already started distancing itself and is Accenture today). We had a motto: Analyse, Simplify, Automate. When developing software, the basic principle was, you first design the optimal solution. Then you de-optimise it, to cater for the weaknesses of the technology and the users. You probably know that early computers suffered from two basic problems: limited data storage and computing power. So, for key functions (like adding two numbers) you typically had three options: one instruction would use minimal storage, another maximise speed, they were mutually exclusive. Then there was a third which you could use if you did not care. As the type of project I managed changed, I found that these principles applied elsewhere as well. For example, if we are building a plant, you have to consider the the file of the plant. If it will be used for a very long time, you want to build the highest quality plant will give you the best return in the long run even it it take syears to pass the break-even point, because of the minimisation of maintenance and stoppages during the life of the plant. If you are going to use it for only a year or so, then you ahev to use different rules. Now to get to my point: In a spreadsheet or any document the same applies. I spent a about two hours sorting out the formatting of the Styles1-2 final. Only a few minutes on doing the formulae and less in capturing the data. I wanted something that would be as readable whether viewing on the screen, or printing it in colour and monochrome (like a laser). Some of this, I was able to do with styles, but I could only go so far (compare Styles1-0, 1-1, 1-2). I hate sheets where a column as 3 digits in and 5cm wide column because of the heading. My early experience also taught me that people can interpret very quickly what the standard stuff is ... if your are consistent ... so, if you do headings and other standard stuff even in 7 or 6 point, that is quite OK. The thing which changes every time is the content, and that is also why they are reading the document. This needs to eb the clearest, least cluttered and most emphasised. So, what are the format issues that count? 1. Being able to format the contents according to the type of data. I you look at my styles, you will see the ones that I tend to use the most. 2. Varying the borders - having thick borders around unshaded cells tends to overpower the contents. But having thin borders around darker backgrounds (shading) tends to make the lines almost invisible. 3. Being able to distinguish levels when totalling - so I colour code them suing graded colouring (lighter to darker) 1. is quite acceptable is it is currently implemented. 2. is a problem because if I want to have the same content format but different borders, then I have to create sub-styles for each variant. However, there could be a simple solution here: if I apply a style, then I can override it at the cell level. If I leave a border greyed, then it will only change the ones that are not greyed. However, if I first draw borders then apply a style, even if the style Borders tab has the borders greyed then it will inherit going back in steps to the Default style. Which is consistent, but it means there is no easy way to vary the borders. 3. In the case of background, there is not even the greyed option. No Fill basically means fill with white or what ever the background colour is in the template or application. The sequence should be (as it seems to be): 1. Application defaults, overridden by 2. Application Tools default overrides (typically stored in template), overridden by 3. Default style, overridden by 4. Applied style, overridden by 5. Direct cell formatting (there seems to be a level between 2 and 3 which happens for the current session if I change the settings using the toolbar.) There seems to be an easy solution: Consistently utilise the Y/N/empty binary values, where empty is equivalent to greyed. If Y - then apply the format if N - do not do anything if grey - inherit Then if one added a tickbox into any tab, you could determine whether to apply the format or not. You could make it easy to see if a tab applies or not without having to go into it if the tab name text were to be white. Then the tab contents could be determined by the greyed feature. MS Excel does a partial implementation of this. They present you a list when you modify a style where you can tick boxes to indicate if you want to apply the different format sets. But in typical MS fashion, this seems to be more an afterthought than a design issue.
(In reply to Elmar from comment #7) > The sequence should be (as it seems to be): > 1. Application defaults, overridden by > 2. Application Tools default overrides (typically stored in template), > overridden by > 3. Default style, overridden by > 4. Applied style, overridden by > 5. Direct cell formatting > (there seems to be a level between 2 and 3 which happens for the current > session if I change the settings using the toolbar.) > > There seems to be an easy solution: > Consistently utilise the Y/N/empty binary values, where empty is equivalent > to greyed. > > If Y - then apply the format > if N - do not do anything > if grey - inherit > > Then if one added a tickbox into any tab, you could determine whether to > apply the format or not. > > You could make it easy to see if a tab applies or not without having to go > into it if the tab name text were to be white. > > Then the tab contents could be determined by the greyed feature. > > MS Excel does a partial implementation of this. They present you a list when > you modify a style where you can tick boxes to indicate if you want to apply > the different format sets. But in typical MS fashion, this seems to be more > an afterthought than a design issue. Ok, let's show this to the design team.
(In reply to Elmar from comment #7) > I notice that if I stop what I am writing something here... Sorry, but could you please describe your problem in only a few sentences? Like "I want to apply a style like good/bad to numbers above/below zero and cannot...". That saves us a lot of time :-).
Created attachment 142398 [details] proposed solution
Created attachment 142399 [details] proposed solution screenshot
@Heiko, let's see if I can be more explicit. Yes, I appreciate my long story might be a problem, but I had to do that to justify my issue. To cut to the chase, here is a proposal which should solve all the problems. See together with my latest attachments (styles1-2 proposal.odp and styles1-2 proposal.jpg). the jpg is just a screenshot of the picture in the odp. Add a checkbox to each of the "Format Cells" tabs. Good place is top right hand corner, so it will not be missed. If it is ticked, then change the tab label colour to white, so you can see without entering the table whether it is active or not. If the checkbox is ticked, then disable that formatting. This means that any tab which has been disabled will be ignored when I apply the formatting. So, let's say I want to create a style which only changes the Numbers formats. If I tick all of the other tabs, then they will be ignored. -------------------------------------- I do see a problem: if one cannot attach more than one style to a cell, then this cannot be used. ------------------------------------- In practice though, I would typically only want to have a variable Numbers formats, whilst all the others could be kept in one style. So that I can apply the same colours, borders, etc but have the target cells containing numbers, text, etc. But the solution could still work if the checkbox option were added, not to the style, but to the override "Format Cells" feature (when using feature in the Menu, Toolbar, or context dialog (right-click on cell or range of cells). Does this make more sense? ------------------------------------- When saving to Excel file, there has always been a problem with styles. My suggestion is that one does not try to fight MS in spite of how unpalatable that might be. Excel's solution (see the attachment styles1-2 excel.png) is to allow one choose which of the tabs to use by having a separate list. But I think that my suggestion is more elegant.
Created attachment 142400 [details] how excel solves the problem
If it is ticked, then change the tab label colour to white, so you can see without entering the table whether it is active or not. should read: If it is ticked, then change the tab label colour to white, so you can see without entering the <<<<tab>>>>> whether it is active or not. -------------------------------- In addition to my comment regarding Excel's solution. When saving the file to XLSX format, there does seem to be a problem with the the way the formats are converted: see row 8 in the files <styles1-2 final.xlsx> and <styles1-2 final.ods>
Thanks for the explanation. You are talking about a) direct formatting and b) cell style. The first happens per Format Cells, the second is a style. You apply it per sidebar "Styles & Formatting" or the menu entry "Styles". You can modify and add styles - and this dialog provides an additional tab "Organizer" with an overview of what properties are being changed with the style. Taking the summary into account you should define a new style for your condition that is based on Default with a different background color, for example. Apply your conditional formatting with this style and later change the parent (Default) and you will see that we do have a hierarchy and do selectively change properties. So I would close this issue as WORKSFORME. Just reopen in case I'm wrong.