Articles with the tag:
Close
Changelog
Close
Articles with the tag:
Close

Conditional Formatting

Note: the ONLYOFFICE Spreadsheet Editor currently does not support creating and editing conditional formatting rules.

Conditional formatting allows you to apply various formatting styles (color, font, decoration, gradient) to cells to work with data on the spreadsheet: highlight or sort through and display the data that meets the needed criteria. The criteria are defined by several rule types. The ONLYOFFICE Spreadsheet Editor currently does not support creating and editing conditional formatting rules.

Rule types supported in the ONLYOFFICE Spreadsheet Editor View mode are cell value (+formula), top/bottom and above/below average value, unique values and duplicates, icon sets, data bars, gradient (color scale), and formula-based rules.

  • Cell value is used to find needed numbers, dates, and text within the spreadsheet. For example, you need to see sales for the current month (pink highlight), products named “Grain” (yellow highlight), and product sales amounting to less than $500 (blue highlight).

    Cell value

  • Cell value with formula is used to display a dynamically changed number or text value within the spreadsheet. For example, you need to find products named “Grain”, “Produce”, or “Dairy” (yellow highlight), or product sales amounting to a value between $100 and $500 (blue highlight).

    Cell value with formula

  • Top and bottom value / Above and below average value is used to find and display the top and bottom values as well as above and below average values within the spreadsheet. For example, you need to see top values for fees in the cities you visited (orange highlight), the cities where the attendance was above average (green highlight), and bottom values for cities where you sold a small number of books (blue highlight).

    Top and bottom value / Above and below average value

  • Unique / Duplicates is used to display duplicate values within the spreadsheet and the cell range defined by the conditional formatting. For example, you need to find duplicate contacts. Enter the drop-down menu. The number of duplicates is shown to the right of the contact name. If you check the box, only the duplicates will be shown on the list.

    Unique and duplicates

  • Icon set is used to show the data by displaying a corresponding icon in the cell that meets the criteria. The Spreadsheet Editor supports various icon sets. Below you will find examples for the most common icon set conditional formatting cases.
    • Instead of numbers and percent values, you see formatted cells with corresponding arrows showing you revenue achievement in the “Status” column and the dynamics for trends in the future in the “Trend” column.

      Icon set

    • Instead of cells with rating numbers ranging from 1 to 5, the conditional formatting tool displays corresponding icons from the legend map at the top for each bike in the rating list.

      Icon set

    • Instead of manually comparing monthly profit dynamics data, the formatted cells have a corresponding red or green arrow.

      Icon set

    • Use the traffic lights system (red, yellow, and green circles) to visualize sales dynamics.

      Icon set

  • Data bars are used to compare values in the form of a diagram bar. For example, compare mountain heights by displaying their default value in meters (green bar) and the same value in 0 to 100 percent range (yellow bar); percentile when extreme values slant the data (light blue bar); bars only instead of numbers (blue bar); two-column data analysis to see both numbers and bars (red bar).

    Data bars

  • Gradient, or color scale, is used to highlight values within the spreadsheet through a gradient scale. The columns from “Dairy” through “Beverage” display data via a two-color scale with variation from yellow to red; the “Total Sales” column displays data via a three-color scale from the smallest amount in red to the largest amount in blue.

    Gradient

  • Formula-based formatting uses various formulas to filter data as per specific needs. For example, you can shade alternate rows,

    Formula-based

    compare with a reference value (here it is $55), and show if it is higher (green) or lower (red),

    Formula-based

    highlight the rows that meet the needed criteria (see what goals you shall achieve this month, in this case, it is October),

    Formula-based

    and highlight unique rows only

    Formula-based

Please note that this guide contains graphic information from the Microsoft Office Conditional Formatting Samples and guidelines workbook. Try the aforementioned rules display by downloading the workbook and opening it in the Spreadsheet Editor.

Return to previous page
Try now for free Try and make your decision No need to install anything
to see all the features in action