LibreOffice Calc - The Document Foundation

1 LibreOffice Calc Conditional Formatting GuideTable of contents1. How to create, change and delete conditional Creating of conditional Changing of conditional Deleting of existing conditional Several conditions for one cell range. Priority of conditions Creating own cell style for conditional Dialog Cell Category and types of conditional Category All cells .. Category Cell value is .. Category Formula is .. Category Date ..237. Copying of conditional PrefaceThis guide is intended for LibreOffice Calc users, who wants to use conditional for-matting in their name of menu, toolbars, tooltips, dialogue, button and any other elements of GUI are from LibreOffice Calc I wrote this guide, I found some bugs in GUI and in execution of conditional formatting in LibreOffice Calc There are special reservations about it in the is requested that reader already knows how to work with computer and with spreadsheets ( LibreOffice Calc, MS excel , Gnumeric or any one else)

2 , knows terms Main menu , Sheet , Cell , Range of cells , Address of cells , of this guide is Roman big thank you to Sophie Gautier for review of this guide and Mike Kaganski for fixing some bugs in Conditional formatting I found when created this guide is shared under Creative Commons Attribution ShareAlike version li-cense. Text of the license is reachable under this link: 32. IntroductionUnfortunately, many users don t use conditional formatting in their work. What is conditional formatting and what it does??At first, we define some terms:Format cells it is customizing of cell view. We can change the following options: Background color of cell; Showing, color and type of cell borders; Options of text view: vertical and horizontal text alignment in cell, rotation of text in cell, text color, text effects, shadow of text, font and font style (bold, italic); Number format: Number, Percent, Currency, Date, Time, Scientific, Fraction, Bool-ean value, of cells is used in order to highlight important information in spread-sheet and to ease perception.

3 Classic example is color highlighting of cells: green, yellow and red. User shouldn t think about meaning of numbers in cells, rows or columns. Color indication of cells is more intuitive for people. Of course, you have to first understand what means each type of condition and which result you want to style it is a user defined set of options of cell customization, saved with unique name for this Document . LibreOffice Calc already has some pre-built cell styles, that can be used for conditional formatting. User can create his own cell styles or change existing formatting it automatically applies cell style to cell or cell range when user defined condition is performed. For example, you define automatically green highlighting of cells, if cell value is between 80 and 100; or green highlight cell if there is word Russia and red highlight if there is word USA in cell.

4 If value in cell is changed, then the formatting of cell will change too depending on the defined !Make sure, that item Data Calculate AutoCalculate is active. It is need for condi-tional formatting to work , by using conditional formatting, it allows user to forget about formatting of cells when changing values in cells, LibreOffice Calc takes care of changing cell format : Formatting example3. How to create, change and delete conditional Creating of conditional formattingTo create conditional formatting follow each of next sub-menu items Format Conditional (Condition; Color scale; Data bar; icon set; Date). Conditional widget on Formatting Add button in Manage Conditional Formatting dialog from Format Condi-tional Manage menuWhatever the used method, it opens Conditional Formatting for.

5 Dialog which al-lows to customize conditional create conditions follow the next about the result you expect after using conditional formatting and which type of condition you will cell or cell range (during customizing of conditions it can be changed). Conditional Formatting for .. dialog using any method as shown Conditional Formatting for .. dialog select for Condition 1 one of the available category of conditions (All cells, Cell value is, Formula is or Date is). on the selected category set up options for conditional formatting. More information will be in the following necessary, you can add several conditions for the same cell or cell range, click the Add and repeat steps as many times as you part of the dialog has field Range, that allows to change the cell range of your conditional finishing conditional customization, click the ОК button to close the Condi-tional Formatting for.

6 You opened Conditional Formatting for .. dialog by using the Add button in Manage Conditional Formatting dialog then click the OK button to close it Changing of conditional change the existing conditional formatting follow next steps:5 Illustration : Drop-down widget Conditional on Formatting Format Conditional Manage in main menu to open Manage Conditional Formatting the cell range, that you want to the Edit options of conditional formatting as written in items in section Creating ofconditional formatting : Conditional Formatting dialogIllustration : Manage Conditional Formatting Deleting of existing conditional delete existing conditional formatting follow next Format Conditional Manage in main menu to open the Manage Condi-tional Formatting the cell range in the list where you want to delete conditional the Delete the OK button to close the Manage Conditional Formatting dialog74.

7 Several conditions for one cell range. Priority of conditions one cell range you can create unlimited amount of conditions, but Attention! Very big amount of conditions in your spreadsheet or for one cell range can make work with your Document impossible. So don t set up full rows (for example, 1:1) or full col-umns (for example, A:A) for conditional formatting range, if you know, that your data will be only in A1:B20 up several conditions for one cell range is very simple:Open Conditional Formatting for .. dialog and click the Add button It adds a new Condition 2, which can be customized the same as Condition 1. Add as many conditions as you , LibreOffice Calc processes conditions in list from top to bottom. Therefore po-sition of conditions in the list is s see on the example below:Set up for A1:A10 range three conditions from Cell value is category and select type of condition Between:For value in cell between 10 and 20 will select Good style (green);For value in cell between 15 and 25 will select Bad style (red);For value in cell between 20 and 30 will select Neutral style (yellow).

8 It is in this sequence from top to : Example of priority of conditions for one rangeTry enter number values in the cell If you type in cell any value from 15 to 20, then cell background will be green. But we have a second condition where cell back-ground for range from 15 to 25 is red! Why do we have green color in cell? It is right, be-cause condition with green background is higher than condition with red color in list of conditions and first condition has more priority than you type in cell any value from 21 to 25, then cell background have red color. It s the same situation as above but for second and third conditions. Second condition has more priority than situation may happen where there are two (or more) intersecting cell ranges with different conditional formatting.

9 For example, there are cell range A1:A10 with own conditions and A5:A15 with its own conditions. For cells in range А5:А10 first condition for-matting will be processed that is first in list of ranges in Manage Conditional Formatting !Never make intersecting ranges of condition inside conditional formatting for one cell range. And it s not needed to make different conditional formatting for one cell range or for intersecting cell Creating own cell style for conditional some types of conditional formatting it is needed to set up cell style that will set cell appearance if condition for the cell is met. LibreOffice Calc has some number built-in cell styles with different cell views. But these styles are few or maybe you will need another cell view.

10 In this case you can create your own cell create your own cell style select one from next the Sidebar and click the Style icon. Right click on the style named Default and select the item New from context menu. It opens the Cell style the Conditional formatting dialog in Apple style drop-down list select the item New style. It opens the same Cell style up cell appearance as you need, then use the New style from selection icon in top of the Style section on the Sidebar or use the menu item Styles New Style. Enter a name for new style in the Create style dialog and click the OK Dialog Cell styleOrganizer this tab, enter the name of the new style. Values in drop-down lists Inherit from and Category are stay by s better if the new style name is selfspeaking , for example Red background with Italic font or Bold violet font.