The calculation of the color applied will be made in relation to: Min - Max - Percentile - Value - Percent - Formula. You must choose the two "extreme" colors indicate the method of calculation. A typical example might be an array of temperatures, lower blue colored, warmer red with a gradient nuances to the intermediate values. Apply a color scale to a range consist of displaying a bicolor or tricolor gradient on this range depending on the value of each cell. This is the same dialog box as if you select All cells in the first sub menu entry Condition.
Click again on the button to come back to the dialog box once the range is selected. Click on the Shrink button to minimize the dialog box. In the Range field, define the range of cells concerned by the conditional formatting.Click the Add button to add another condition, click the Remove button to remove a condition.In front of Apply Styles, select the desired style in the list or chose New Style to create one.*Select a condition in the drop down list for the format to be applied to the selected cells and enter the value.
If you select All cells, see Color Scale, Data Bar or Icon Set explanations below, depending on which visual representation the conditional formatting should be represented.Specify if conditional formatting is dependent on one of the entry listed on the drop down box: Click OK.You can define as many conditions as you want. In the Selection area, check only the Formats box. Select the cells that are to receive this same formatting.Ĭhoose Edit - Paste Special. To apply the conditional formatting to other cells later:Ĭlick one of the cells that has been assigned conditional formatting. Select all cells with the random numbers.Ĭhoose the Format - Conditional Formatting command to open the corresponding dialog.ĭefine the condition as follows: If cell value is less than J14, format with cell style "Below", and if cell value is greater than or equal to J14, format with cell style "Above". Now you can apply the conditional formatting to the sheet: If you cannot see the entire range, because the Function Wizard is obscuring it, you can temporarily shrink the dialog using the Shrink / Maximize icon. Use the mouse to select all your random numbers. Set the cursor in a blank cell, for example, J14, and choose Insert - Function. In our particular example, we are calculating the average of the random values. Assign a different background color for the cell and assign a name (for this example, "Below"). To define a second style, click again in a blank cell and proceed as described above. For this example, name the style "Above". In the Styles window, click the New Style from Selection icon. In the Format Cells dialog on the Background tab, select a background color. Ensure that the Styles window is visible before proceeding.Ĭlick in a blank cell and select the command Format Cells in the context menu. The next step is to apply a cell style to all values that represent above-average turnover, and one to those that are below the average. In the same way as described above, drag down the corner of the rightmost cell in order to create more rows of random numbers. Click the bottom right corner of the selected cell, and drag to the right until the desired cell range is selected. If you want integers of between 0 and 50, enter the formula =INT(RAND()*50).Ĭopy the formula to create a row of random numbers. In one of the cells enter the formula =RAND(), and you will obtain a random number between 0 and 1. For your test you can create tables with any random numbers: This is possible with conditional formatting.įirst of all, write a table in which a few different values occur. For example, in a table of turnovers, you can show all the values above the average in green and all those below the average in red. You want to give certain values in your tables particular emphasis. The dialog is described in detail in LibreOffice Help, and an example is provided below:Įxample of Conditional Formatting: Highlighting Totals Above/Under the Average Value Step1: Generate Number Values Select the cells to which you want to apply a conditional style.Įnter the condition(s) into the dialog box. If the totals change, the formatting changes correspondingly, without having to apply other styles manually. With conditional formatting, you can, for example, highlight the totals that exceed the average value of all totals. Choose Data - Calculate - AutoCalculate (you see a check mark next to the command when AutoCalculate is enabled). To apply conditional formatting, AutoCalculate must be enabled.