4.4.17 Conditional Formatting
From Origin 2019, it supports three Conditional Format tools to color the cells in the worksheet.
To open the dialog:
- Select menu Worksheet: Conditional Formatting: Highlight/ Duplicate/ Heatmap
- Select the cells in the worksheet, then right-click and select Conditional Formatting: Highlight/ Duplicate/ Heatmap
In three 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 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
Use this tool to color cells that the value matchs the condition setting in the dialog.
Highlight Duplicate Values
Use this tool to color cells with duplicate value.
||Specify the backgroud color for the cells with duplicate value.
||Specify the text color for the cells with duplicate value.
Use heatmap to color cells according to the level setting.
||Select this check box to auto detect automatically the maximum and minimum value of the range. By defualt, it is checked.
||This is available only when Auto Rescale is unchecked. Use it to specify the minimum value.
||This is available only when Auto Rescale is unchecked. Use it to specify the maximum value.
||Click this button to detect the maximum and minimum value based on the selected data range, and automatically set the To and From values.
|| Select a type for the level scale. Please see details about the scale type here.
- Set levels by increment specified by the text box value in the right side
- Set levels by count specified by the text box value in the right side
||Specify the value of the first level. Note: this value should be larger than or equal to the default value of the first level.
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
- 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.
- 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 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.
||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.
||This is available only when 3-Color Limited Mixing is selected. Use it to specify the color for the middle level.
||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.
|| This is available only when Load Palette is selected. Select the built-in Increment list or Palettes.
|| Flip the order of colors for From' and To or in the selected palette.
|| Specify a color and it will be used to the cell that the value less than From value.
|| Specify a color and it will be used to the cell that the value greater than To value.
Remove Conditional Format
There are two methods to remove the Conditional Format:
- Highlight the range you added the Conditional Format and select Worksheet: Conditional Formatting: Remove Format in the menu.
- Active the worksheet with the Conditional Format. Select the menu Worksheet: Conditional Formatting: Conditional Format Manager to open the Conditional Format Manager. Choose the row of Conditional Format in the list, and click Remove button to delete the format control.
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
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:
- Click the range selector button , then you can re-select the range in the Worksheet.
- Double click on the cell of the Range, then you can enter a new range in it.
To update the Rule:
- Click on the to reopen the corresponding conditional format dialog.
- 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:
- Select the desired Conditional Format in the list.
- Use or button to move the Conditional Format.
- Click on blank place to the left of the desired Conditional Format name, and keep mouse click pressed.
- 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.