4.4.20 Conditional Formatting


Contents

Origin supports multiple Conditional Format tools to color the cells in the worksheet.

To open the dialog:

or


Conditional Format Range Name.png

In these dialogs of the tools, you can specify Range of the cells in the worksheet and Name for the selected range.

By default, the Name is first cell-last cell of the range. For example: if the range is from Column1 Row5 to Column4 Row10, the name will be C1R5-C4R10. Also, you can define the name by entering text. And in the same Worksheet, the name of Conditional Format that have been used will list in this drop-box.

Note: for a worksheet, if you have added a Conditional Format, these tools dialog can support to update the rule, but not support to change the data range with the original Name. If you want to just update the range of the Conditional Format, you need to edit it in the Conditional Format Manager


Conditional Highlight

Color cells that matchs the condition setting in the dialog.

Conditional Format Highlight.png

Rule

Condition When the range (columns) Format = Numeric
  • Options:
Condition list 01.png
  • Click the triangle button to bring up the fly-out menu to call the value of statistics and the text-related functions.
Conditional Highlight fly-out.png
  • When you select Custom, the Custom edit box shows. It is used to input conditions. x repersents cells in range.
Conditional Highlight Custom.png


When the range (columns) Format = Date / Time

  • Options:
Condition list 02.png
  • Select the date value in the right side box.
Join The default state is None but you can set the drop-down to either And or Or and create a second condition.
Background Specify the background color for the cells value matching the conditions of the rule.
Text Color Specify the text color for the cells value matching the conditions of the rule.

Highlight Duplicate Values

Use this tool to color cells with duplicate value.

Conditional Format Duplicate.png

Option

Backgroud Specify the backgroud color for the cells with duplicate value.
Text Color Specify the text color for the cells with duplicate value.

Highlight Top Bottom

Color top/bottom N values in the selected colulmns.

Conditional Format Top Bottom.png

Rule

Top N Iterms Color the top N values in Range columns.
Top N% Color the top N% values in Range columns.
Bottom N Items Color the bottom N values in Range columns.
Bottom N% Color the bottom N% values in Range columns.

Highlight Min Max

Color minimumn and/or maximum values in each selected colulmn/row, independent of other columns or rows.

Conditional Format Min Max.png

Conditional Pareto

Calculate count of all items in the selected range, and order them from top frequent to least frequent like Pareto chart.

Conditional Format Pareto.png

Rule

Most Frequent N Iterms Color the top N items of count.
Most Frequent N% Color the items with cumulative frequency <=N%.
Least Frequent N Items Color the bottom N items of count.
Least Frequent N% Color the items with cumulative frequency >=(1-N%).

Conditional Outliers

Color outliers from the selected Range like Box chart.

Conditional Format Outliers.png

Rule

IQR Same as Whisker Range = Outlier in Box chart.

Select or type a value n into the Coef combo box to expand the whisker by a factor of n.

With the default Coef value of 1.5, whisker length will be determined by the outermost data point that falls within upper inner and lower inner fence (a Coef = 3 would be determined by the outermost data point that falls within the upper outer and lower outer fences).

Customizing the Box Chart Box Tab Controls 4.png
Upper Inner Fence = 75th Percentile + (1.5 * Interquartile Range)
Lower Inner Fence = 25th Percentile - (1.5 * Interquartile Range)
SD Same as Whisker Range = SD in Box chart.

Select or type a value n into the Coef combo box to expand the whisker by a factor of n.

Conditional Heatmap

Use heatmap to color cells according to the level setting.

Conditional Format Heatmap.png

Level

Auto Rescale Select this check box to auto detect automatically the maximum and minimum value of the range. By defualt, it is checked.
From This is available only when Auto Rescale is unchecked. Use it to specify the minimum value.
To This is available only when Auto Rescale is unchecked. Use it to specify the maximum value.
Find Mi/Max Click this button to detect the maximum and minimum value based on the selected data range, and automatically set the To and From values.
Type Select a type for the level scale. Please see details about the scale type here.
Levels
  • By Increment
Set levels by increment specified by the text box value in the right side
  • By Count
Set levels by count specified by the text box value in the right side
First Level Specify the value of the first level. Note: this value should be larger than or equal to the default value of the first level.

Colors

Format Type

Use the color to Fill Background or Color Text of the cells in the selected range.

Contrast Color for Text Against the background color of the cells, use the contrast color for the text
Color Generation
  • Limited Mixing
Select this option to select a minimum level fill color (From) and a maximum level fill color (To), and to fill the levels between these extremes with a linear mix of the two colors.
  • 3-Color Limited Mixing
Select this option to select a minimum level fill color (From), a middle level fill color (Middle) and a maximum level fill color (To), and to fill a range of cells by using a gradation of three colors.
  • Introducing Other Colors in Mixing
Select this option to have Origin automatically introduce complementary colors into the mix. This option provides fill colors that are more distinct than the ones provided by the Limited Mixing option.
  • Load Palette
Load the palette and apply it to color fills. Click the Select Palette button, you can select one palette from the built-in Increment list or Palettes.
From This is available only when either Limited Mixing, 3-Color Limited Mixing or Introducing Other colors in Mixing is selected. Use it to specify the color for the minimum level.
Middle This is available only when 3-Color Limited Mixing is selected. Use it to specify the color for the middle level.
To This is available only when either Limited Mixing, 3-Color Limited Mixing or Introducing Other colors in Mixing is selected. Use it to specify the color for the maximum level.
Middle Position

Used to control values associated with Middle color in 3-Color Limited Mixing:

  • If By Percentile, enter the percentile of the cell value that corresponds to the Middle color.
  • If By Percent, enter the percentage of cell values that corresponds to the Middle color.
  • If By Value, enter a cell value that you wish to correspond with the Middle color.
Palette This is available only when Load Palette is selected. Select the built-in Increment list or Palettes.
Flip Flip the order of colors for From' and To or in the selected palette.
Below Specify a color and it will be used to the cell that the value less than From value.
Above Specify a color and it will be used to the cell that the value greater than To value.

It can be handy to zoom or pan the Conditional Heatmap:

  • To zoom the worksheet, press CTRL and then rotate the mouse scroll wheel up or down.
Worksheet scroll zoom 2.png
  • To pan the worksheet, click with the mouse wheel (most have this feature) and then with the mouse pointer, lead the circular 4-pointed icon that appears in the direction that you wish to pan. Press ESC on the keyboard to quit.
Worksheet scroll zoom 1.png

Remove Conditional Format

There are two methods to remove the Conditional Format:

OR

Conditional Format Manager

The Conditional Format Manager dialog lists all the Conditional Formats in active worksheet, and it is used to manage the conditional format by editing, reordering and remove them.

To open the dialog: Worksheet: Conditional Formatting: Conditional Format Manager

Conditional Format Manager.png

Edit the Conditional Format

To change the Name:
Double click on the cell of the Name for the Conditional Format to rename it.


To change the Range:


or


To update the Rule:

  1. Click on the Conditional Format Edit Rule button.png to reopen the corresponding conditional format dialog.
  2. Update the rule or options in the dialog.

Reorder the Conditional Format

If the range are overlapping in two Conditional Formats, the new added Conditional Format shows in upper of this list. And in the overlapping range of the worksheet will be applied the format of the upper .

To Reorder the Conditional Format:

  1. Select the desired Conditional Format in the list.
  2. Use Conditional Format Move Up button.png or Conditional Format Move Down button.png button to move the Conditional Format.

or

  1. Click on blank place to the left of the desired Conditional Format name, and keep mouse click pressed.
  2. Drag that Conditional Format row up or drop.

Remove the Conditional Format

Select the row of Conditional Format in the list, and click Remove button to delete the format control.