libxlsxwriter
Working with Conditional Formatting

Conditional formatting is a feature of Excel that allows you to apply a format to a cell, or a range of cells, based on definable criteria. For example you might format cells that are >= 50 in red and cells that < 50 in green. Like this:

There are also types of conditional formats such as color scales and data bars that highlight the relative difference of cells in a range:

Libxlsxwriter supports almost all of Excel's conditional formatting types and options, which are explained in the sections below.

Getting started with conditional formatting

Conditional formatting is a feature in Excel that allows you to format cells based on criteria such as greater than or less than, top or bottom 10 items, or items that are above the average for the range.

The best place to start with conditional formats is in Excel. Create an example of the type for conditional format that you want and then translate that into an libxlsxwriter program using the worksheet_conditional_format_cell() or worksheet_conditional_format_range() functions and the lxw_conditional_format structure. We will walk through an example of this below.

Convert an Excel conditional format to libxlsxwriter

Say you wanted to highlight in red all the cells in the range "B1:B9" that have a value less than 33. To do this in Excel you would select the range, click on "Conditional Formatting" and create a rule like this:

In the image above we see the "type" which is "Cell", the "Criteria" which is "less than", the "Value" which is 33 and the "Custom Format" which is red text. To replicate this in libxlsxwriter we will start by creating the format.

We do this in the same way we create any worksheet format to use in libxlsxwriter by creating a lxw_format object using workbook_add_format():

// Add a format with red text.
lxw_format *custom_format = workbook_add_format(workbook);

The formats used for conditional formats are the same as any other worksheet format, apart from the fact that Excel restricts some of the available properties. We will look at that below in parameter: format.

Next we create a lxw_conditional_format object. We will use this to set the properties of the conditional format.

// Create a conditional format object. A static object would also work.
lxw_conditional_format *conditional_format = calloc(1, sizeof(lxw_conditional_format));

We then set the "Type", "Criteria" and "Value" like in the Excel dialog above:

// Set the format type: a cell conditional:
conditional_format->type = LXW_CONDITIONAL_TYPE_CELL;
// Set the criteria to use:
// Set the value to which the criteria will be applied:
conditional_format->value = 33;

We then add the format we created earlier which, again, this is like the Excel dialog:

// Set the format to use if the criteria/value applies:
conditional_format->format = custom_format;

And finally we apply the conditional format to the desired range:

// Now apply the format to data range.
worksheet_conditional_format_range(worksheet, RANGE("B1:B9"), conditional_format);

After we compile and run the program the output would look like this:

The complete version of this example is shown in conditional_format1.c. There is also another example that shows more advanced conditional formatting options, see conditional_format2.c.

Defining a conditional format in libxlsxwriter

The lxw_conditional_format struct is used to define a conditional format in libxlsxwriter. It has number of fields, most of which are optional. The main ones are explained here, as an introduction, and in more detail in the sections below. For reference here is our example from the previous section:

conditional_format->type = LXW_CONDITIONAL_TYPE_CELL;
conditional_format->value = 33;
conditional_format->format = custom_format;

The type field in is a required parameter and it has no default value. This defines which type of Excel conditional format will be used.

The criteria field defines the criteria used to evaluate the conditional format. Most conditional formats will have a criteria, in which case it is a required field, but some types such as duplicate or blanks don't need to specify one since it is implicit in the type.

The value or value_string field sets the value to which the criteria is applied. Most conditional formats will have a value, or range of values, to which the criteria is applied, but some types or criteria don't need one.

Finally, the format field defines the lxw_format that will be applied if the conditional format matches. Most conditional formats will used the format field, since that is somewhat the point. However, it isn't actually required, in Excel or in libxlsxwriter, and conditional format types such as Color Scale or Data Bars don't need it since they rely on simpler color definitions.

The allowable type values withe their descriptive and enum values are shown in the following table along with their associated parameters.

Type Enum value Parameters
cell LXW_CONDITIONAL_TYPE_CELL criteria
value
value_string
min_value
max_value
min_value_string
max_value_string
format
text LXW_CONDITIONAL_TYPE_TEXT criteria
value
value_string
format
time_period LXW_CONDITIONAL_TYPE_TIME_PERIOD criteria
format
average LXW_CONDITIONAL_TYPE_AVERAGE criteria
format
duplicate LXW_CONDITIONAL_TYPE_DUPLICATE format
unique LXW_CONDITIONAL_TYPE_UNIQUE format
top LXW_CONDITIONAL_TYPE_TOP criteria
value
value_string
format
bottom LXW_CONDITIONAL_TYPE_BOTTOM criteria
value
value_string
format
blanks LXW_CONDITIONAL_TYPE_BLANKS format
no_blanks LXW_CONDITIONAL_TYPE_NO_BLANKS format
errors LXW_CONDITIONAL_TYPE_ERRORS format
no_errors LXW_CONDITIONAL_TYPE_NO_ERRORS format
formula LXW_CONDITIONAL_TYPE_FORMULA value
value_string
format
2_color_scale LXW_CONDITIONAL_2_COLOR_SCALE min_rule_type
max_rule_type
min_value
max_value
min_value_string
max_value_string
min_color
max_color
3_color_scale LXW_CONDITIONAL_3_COLOR_SCALE min_rule_type
mid_rule_type
max_rule_type
min_value
mid_value
max_value
min_value_string
mid_value_string
max_value_string
min_color
mid_color
max_color
data_bar LXW_CONDITIONAL_DATA_BAR min_rule_type
max_rule_type
min_value
max_value
min_value_string
max_value_string
bar_only
bar_color
bar_solid*
bar_negative_color*
bar_border_color*
bar_negative_border_color*
bar_negative_color_same*
bar_negative_border_color_same*
bar_no_border*
bar_direction*
bar_axis_position*
bar_axis_color*
data_bar_2010*
icon_set LXW_CONDITIONAL_TYPE_ICON_SETS icon_style
reverse_icons
icons_only
Note
Data bar parameters marked with * are only available in Excel 2010 and later. Files that use these properties can still be opened in Excel 2007 but the data bars will be displayed without them.

Each conditional format type is explained in more detail in the sections below.

Conditional Formatting Types

type: cell

This is the most common conditional formatting type. It is used when a format is applied to a cell, or range of cells, based on a simple criterion.

For example using a single value and the "greater than" criteria:

conditional_format->type = LXW_CONDITIONAL_TYPE_CELL;
conditional_format->value = 5;
conditional_format->format = format1;
worksheet_conditional_format_range(worksheet, RANGE("A1:A4"), conditional_format);

Or, using 2 values and the between criteria:

conditional_format->type = LXW_CONDITIONAL_TYPE_CELL;
conditional_format->min_value = 20;
conditional_format->max_value = 30;
conditional_format->format = format1;
worksheet_conditional_format_range(worksheet, RANGE("A1:A4"), conditional_format);

If the criteria is "equal to" and the value_string is a text string (but not a cell range) then Excel requires that the text string is double quoted:

conditional_format->type = LXW_CONDITIONAL_TYPE_CELL;
conditional_format->value_string = "\"string to match\"";

The criteria that apply to Cell conditional formats are:

type: time_period

The time_period type is used to specify Excel's "Dates Occurring" style conditional format:

conditional_format->type = LXW_CONDITIONAL_TYPE_TIME_PERIOD;
conditional_format->format = format1;
worksheet_conditional_format_range(worksheet, RANGE("A1:A4"), conditional_format);
conditional_format->type = LXW_CONDITIONAL_TYPE_TIME_PERIOD;
conditional_format->format = format2;
worksheet_conditional_format_range(worksheet, RANGE("A1:A4"), conditional_format);

The period is set in the criteria and can have one of the following values:

type: text

The text type is used to specify Excel's "Specific Text" style conditional format. It is used to do simple string matching using the criteria and value parameters:

conditional_format->type = LXW_CONDITIONAL_TYPE_TEXT;
conditional_format->value_string = "foo";
conditional_format->format = format1;
worksheet_conditional_format_range(worksheet, RANGE("A1:A4"), conditional_format);
conditional_format->type = LXW_CONDITIONAL_TYPE_TEXT;
conditional_format->value_string = "bar";
conditional_format->format = format2;
worksheet_conditional_format_range(worksheet, RANGE("A1:A4"), conditional_format);
conditional_format->type = LXW_CONDITIONAL_TYPE_TEXT;
conditional_format->value_string = "a";
conditional_format->format = format2;
worksheet_conditional_format_range(worksheet, RANGE("A1:A4"), conditional_format);
conditional_format->type = LXW_CONDITIONAL_TYPE_TEXT;
conditional_format->value_string = "t";
conditional_format->format = format4;
worksheet_conditional_format_range(worksheet, RANGE("A1:A4"), conditional_format);

The value_string parameter should be a string with one or more characters.

The criteria for Text conditional formats can have one of the following values:

type: average

The average type is used to specify Excel's "Average" style conditional format:

conditional_format->type = LXW_CONDITIONAL_TYPE_AVERAGE;
conditional_format->format = format1;
worksheet_conditional_format_range(worksheet, RANGE("A1:A4"), conditional_format);
conditional_format->type = LXW_CONDITIONAL_TYPE_AVERAGE;
conditional_format->format = format2;
worksheet_conditional_format_range(worksheet, RANGE("B1:B4"), conditional_format);

The type of average for the conditional format range is specified by the criteria:

type: duplicate

The duplicate type is used to highlight duplicate cells in a range. It doesn't take a criteria:

conditional_format->type = LXW_CONDITIONAL_TYPE_DUPLICATE;
conditional_format->format = format1;
worksheet_conditional_format_range(worksheet, RANGE("A1:A4"), conditional_format);

type: unique

The unique type is used to highlight unique cells in a range. It doesn't take a criteria:

conditional_format->type = LXW_CONDITIONAL_TYPE_UNIQUE;
conditional_format->format = format1;
worksheet_conditional_format_range(worksheet, RANGE("A1:A4"), conditional_format);

type: top

The top type is used to specify the top n values by number or percentage in a range:

conditional_format->type = LXW_CONDITIONAL_TYPE_TOP;
conditional_format->value = 10;
conditional_format->format = format1;
worksheet_conditional_format_range(worksheet, RANGE("A1:A4"), conditional_format);

The criteria can be used to indicate that a percentage condition is required:

conditional_format->type = LXW_CONDITIONAL_TYPE_TOP;
conditional_format->value = 10;
conditional_format->format = format1;
worksheet_conditional_format_range(worksheet, RANGE("A1:A4"), conditional_format);

type: bottom

The bottom type is used to specify the bottom n values by number or percentage in a range.

It takes the same parameters as top, see above.

type: blanks

The blanks type is used to highlight blank cells in a range:

conditional_format->type = LXW_CONDITIONAL_TYPE_BLANKS;
conditional_format->format = format1;
worksheet_conditional_format_range(worksheet, RANGE("A1:A4"), conditional_format);

type: no_blanks

The no_blanks type is used to highlight non blank cells in a range. It doesn't take a criteria:

conditional_format->type = LXW_CONDITIONAL_TYPE_NO_BLANKS;
conditional_format->format = format1;
worksheet_conditional_format_range(worksheet, RANGE("A1:A4"), conditional_format);

type: errors

The errors type is used to highlight error cells in a range. It doesn't take a criteria:

conditional_format->type = LXW_CONDITIONAL_TYPE_ERRORS;
conditional_format->format = format1;
worksheet_conditional_format_range(worksheet, RANGE("A1:A4"), conditional_format);

type: no_errors

The no_errors type is used to highlight non error cells in a range. It doesn't take a criteria:

conditional_format->type = LXW_CONDITIONAL_TYPE_NO_ERRORS;
conditional_format->format = format1;
worksheet_conditional_format_range(worksheet, RANGE("A1:A4"), conditional_format);

type: formula

The formula type is used to specify a conditional format based on a user defined formula:

conditional_format->type = LXW_CONDITIONAL_TYPE_FORMULA;
conditional_format->value_string = "=$A$1>5";
conditional_format->format = format1;
worksheet_conditional_format_range(worksheet, RANGE("A1:A4"), conditional_format);
conditional_format->type = LXW_CONDITIONAL_TYPE_FORMULA;
conditional_format->value_string = "=$A$2<80";
conditional_format->format = format2;
worksheet_conditional_format_range(worksheet, RANGE("B1:B4"), conditional_format);
conditional_format->type = LXW_CONDITIONAL_TYPE_FORMULA;
conditional_format->value_string = "=$A$3>$A$4";
conditional_format->format = format3;
worksheet_conditional_format_range(worksheet, RANGE("C1:C4"), conditional_format);

Formulas must be written with the US style separator/range operator which is a comma (not semi-colon) and should follow the same rules as worksheet_write_formula(). See Non US Excel functions and syntax for a full explanation:

// This formula will cause an Excel error on load due to non-English
// language and use of semi-colons.
conditional_format->type = LXW_CONDITIONAL_TYPE_FORMULA;
conditional_format->value_string = "=ODER($B2<$C2;UND($B2="";$C2>HEUTE()))";
// This is the correct syntax.
conditional_format->type = LXW_CONDITIONAL_TYPE_FORMULA;
conditional_format->value_string = "=OR($B2<$C2,AND($B2="",$C2>TODAY()))";
Note
Conditional formatting formulas require some knowledge of how cell references work in Excel. For example the formulas "$A$1=5" and "A1=5" will give different results when applied to a range. It is best to verify the formula in Excel before transferring it to libxlsxwriter.

type: 2_color_scale

The 2_color_scale type is used to specify Excel's "2 Color Scale" style conditional format:

conditional_format->type = LXW_CONDITIONAL_2_COLOR_SCALE;
conditional_format->format = format1;
worksheet_conditional_format_range(worksheet, RANGE("A1:A12"), conditional_format);

This conditional type can be modified with additional parameters:

conditional_format->type = LXW_CONDITIONAL_2_COLOR_SCALE;
conditional_format->min_value = 20;
conditional_format->max_value = 80;
conditional_format->min_color = 0xFF7128;
conditional_format->max_color = 0xFFEF9C;
conditional_format->format = format1;

See the following sections below for more information:

type: 3_color_scale

The 3_color_scale type is used to specify Excel's "3 Color Scale" style conditional format:

conditional_format->type = LXW_CONDITIONAL_3_COLOR_SCALE;
conditional_format->format = format1;
worksheet_conditional_format_range(worksheet, RANGE("A1:A12"), conditional_format);

This conditional type can be modified with additional parameters:

conditional_format->type = LXW_CONDITIONAL_3_COLOR_SCALE;
conditional_format->min_value = 10;
conditional_format->mid_value = 52;
conditional_format->max_value = 99;
conditional_format->min_color = 0xC5D9F1;
conditional_format->mid_color = 0x8DB4E3;
conditional_format->max_color = 0x538ED5;
conditional_format->format = format1;

See the following sections below for more information:

type: data_bar

The data_bar type is used to specify Excel's "Data Bar" style conditional format:

conditional_format->type = LXW_CONDITIONAL_DATA_BAR;
conditional_format->format = format1;
worksheet_conditional_format_range(worksheet, RANGE("A1:A12"), conditional_format);

This conditional type can be modified with the following parameters, which are explained in the sections below. These properties were available in the original xlsx file specification used in Excel 2007:

Additional data bar properties were added in Excel 2010 such as solid (non-gradient) bars and control over how negative values are displayed. These properties can be set using the following parameters:

For example:

Files that use these Excel 2010 properties can still be opened in Excel 2007 but the data bars will be displayed without them.

type: icon_set

The icon_set type is used to specify a conditional format with a set of icons such as traffic lights or arrows:

conditional_format->type = LXW_CONDITIONAL_TYPE_ICON_SETS;
conditional_format->format = format1;
worksheet_conditional_format_cell(worksheet, CELL("A1"), conditional_format);

The icon set style is specified by the icon_style parameter. Valid options are:

The order of Icon Sets icons can be reversed by setting reverse_icons to LXW_TRUE.

conditional_format->type = LXW_CONDITIONAL_TYPE_ICON_SETS;
conditional_format->reverse_icons = LXW_TRUE;
worksheet_conditional_format_range(worksheet, RANGE("B4:D4"), conditional_format);
reset_conditional_format(conditional_format);

The icons can be displayed without the cell value by settings the icons_only parameter to LXW_TRUE:

conditional_format->type = LXW_CONDITIONAL_TYPE_ICON_SETS;
conditional_format->icons_only = LXW_TRUE;
worksheet_conditional_format_range(worksheet, RANGE("B5:D5"), conditional_format);
reset_conditional_format(conditional_format);

Conditional Format Parameters

The fields/options in the the lxw_conditional_format are used to define a worksheet conditional format. The type field was explained in the previous section. The sections below explain the other options.

parameter: criteria

The criteria parameter is used to set the criteria by which the cell data will be evaluated. It is defined in lxw_conditional_criteria and has no default value. Some conditional format types such as duplicate or blanks don't need to specify as criteria since it is implicit in the type.

The values for different conditional format types are shown in lxw_conditional_criteria and in the type sections to which they apply, above.

parameter: value and value_string

The value parameter is generally used along with criteria to set the rule by which the cell data will be evaluated:

conditional_format->value = 10;

The value_string parameter is used for non-numeric values. In conditional formats this will generally be a cell reference like the following:

conditional_format->value_string = "$B$1";
// Or like this.
conditional_format->value_string = "=$B$1";

In general any value_string property that refers to a cell reference should use an absolute cell reference, like $B$1, especially if the conditional formatting is applied to a range of values. Without an absolute cell reference the conditional format will not be applied correctly by Excel, apart from the first cell in the formatted range. Double check in Excel if you don't get the result that you expect.

If the type is Cell, the criteria is "equal to" and the value_string is a text string (but not a cell range) then Excel requires that the text string is double quoted:

conditional_format->value_string = "\"string to match\"";

parameter: format

The format parameter is used to specify the format that will be applied to the cell when the conditional formatting criterion is met. The format is created using the workbook_add_format() function in the same way as cell formats:

lxw_format *format = workbook_add_format(workbook);
conditional_format->type = LXW_CONDITIONAL_TYPE_CELL;
conditional_format->value = 20;
conditional_format->format = format;

Most conditional formats will have a lxw_format format, since that is somewhat the point. However, it isn't actually required, in Excel or in libxlsxwriter. Color Scale and Data Bar conditional format types use defined colors instead of a format.

Note

In Excel, a conditional format is superimposed over the existing cell format and not all cell format properties can be modified in a conditional format. Properties that cannot be modified in a conditional format in Excel are: font name, font size, superscript and subscript, diagonal borders, all alignment properties and all protection properties.

Excel specifies some default formats when using the conditional formatting dialog such as red, yellow and green. These can be replicated using the following libxlsxwriter formats:

// Light red fill with dark red text.
lxw_format *format1 = workbook_add_format(workbook);
format_set_bg_color( format1, 0xFFC7CE);
format_set_font_color(format1, 0x9C0006);
// Light yellow fill with dark yellow text.
lxw_format *format2 = workbook_add_format(workbook);
format_set_bg_color( format2, 0xFFEB9C);
format_set_font_color(format2, 0x9C6500);
// Green fill with dark green text.
lxw_format *format3 = workbook_add_format(workbook);
format_set_bg_color( format3, 0xC6EFCE);
format_set_font_color(format3, 0x006100);

See also lxw_format and Working with Formats.

parameter: min_value and min_value_string

The min_value parameter is used to set the lower limiting value for Cell, Color Scale and Data Bar conditional formats.

For cell formats it is usually used when the criteria is either LXW_CONDITIONAL_CRITERIA_BETWEEN or LXW_CONDITIONAL_CRITERIA_NOT_BETWEEN:

conditional_format->type = LXW_CONDITIONAL_TYPE_CELL;
conditional_format->min_value = 2;
conditional_format->max_value = 6;

The min_value_string parameter is used for non-numeric values. In conditional formats this will generally be a cell reference like $B$1:

conditional_format->type = LXW_CONDITIONAL_TYPE_CELL;
conditional_format->min_value_string = "$B$1;
conditional_format->max_value_string = "$B$2";

parameter: mid_value and mid_value_string

The mid_value parameter is used to set the middle limit for Color Scale and Data Bar conditional formats.

See parameter: min_value and min_value_string above.

parameter: max_value and max_value_string

The max_value parameter is used to set the lower limiting value for Cell, Color Scale and Data Bar conditional formats.

See parameter: min_value and min_value_string above.

parameter: min_rule_type

The min_rule_type and max_rule_type properties are used when the conditional formatting type is 2_color_scale, 3_color_scale or data_bar. The mid_rule_type is used with 3_color_scale. The properties are used as follows:

conditional_format->type = LXW_CONDITIONAL_2_COLOR_SCALE;
conditional_format->min_value = 20;
conditional_format->max_value = 80;
worksheet_conditional_format_range(worksheet, RANGE("A1:A12"), conditional_format);

The available min/mid/max rule types are defined in lxw_conditional_format_rule_types:

parameter: mid_rule_type

Used for 3_color_scale. Same as min_rule_type, see above.

parameter: max_rule_type

Same as min_rule_type, see above.

parameter: min_color

The min_color and max_color properties are available when the conditional formatting type is 2_color_scale or 3_color_scale. The mid_color is available for 3_color_scale. The properties are used as follows:

conditional_format->type = LXW_CONDITIONAL_2_COLOR_SCALE;
conditional_format->min_color = 0xFF7128;
conditional_format->max_color = 0xFFEF9C;
worksheet_conditional_format_range(worksheet, RANGE("A1:A12"), conditional_format);

The color can be a Html style RRGGBB hex number or a limited number of named colors, see Working with Colors.

parameter: mid_color

Used for 3_color_scale. Same as min_color, see above.

parameter: max_color

Same as min_color, see above.

parameter: bar_color

The bar_color parameter sets the fill color for data bars:

conditional_format->type = LXW_CONDITIONAL_DATA_BAR;
conditional_format->bar_color = 0x63C384;

The color can be a Html style RRGGBB hex number or a limited number of named colors, see Working with Colors.

parameter: bar_only

The bar_only property displays a bar data but not the data in the cells:

conditional_format->type = LXW_CONDITIONAL_DATA_BAR;
conditional_format->bar_only = LXW_TRUE;
worksheet_conditional_format_range(worksheet, RANGE("A1:A12"), conditional_format);

See the image above.

parameter: bar_solid

The bar_solid property turns on a solid (non-gradient) fill for data bars:

conditional_format->type = LXW_CONDITIONAL_DATA_BAR;
conditional_format->bar_solid = LXW_TRUE;
worksheet_conditional_format_range(worksheet, RANGE("A1:A12"), conditional_format);

See the image above.

Note, this property is only visible in Excel 2010 and later.

parameter: bar_negative_color

The bar_negative_color property sets the color fill for the negative portion of a data bar:

conditional_format->type = LXW_CONDITIONAL_DATA_BAR;
conditional_format->bar_negative_color = 0xFFFF00;
worksheet_conditional_format_range(worksheet, RANGE("A3:C3"), conditional_format);

The color can be a Html style RRGGBB hex number or a limited number of named colors, see Working with Colors.

Note, this property is only visible in Excel 2010 and later.

parameter: bar_border_color

The bar_border_color property sets the color for the border line of a data bar:

conditional_format->type = LXW_CONDITIONAL_DATA_BAR;
conditional_format->bar_color = 0xFF555A;
conditional_format->bar_border_color = 0xFF0000;
worksheet_conditional_format_range(worksheet, RANGE("A3:C3"), conditional_format);

The color can be a Html style RRGGBB hex number or a limited number of named colors, see Working with Colors.

Note, this property is only visible in Excel 2010 and later.

parameter: bar_negative_border_color

The bar_negative_border_color property sets the color for the border of the negative portion of a data bar:

conditional_format->type = LXW_CONDITIONAL_DATA_BAR;
conditional_format->bar_negative_border_color = 0x92D050;
worksheet_conditional_format_range(worksheet, RANGE("A2:B2"), conditional_format);

The color can be a Html style RRGGBB hex number or a limited number of named colors, see Working with Colors.

Note, this property is only visible in Excel 2010 and later.

parameter: bar_negative_color_same

The bar_negative_color_same property sets the fill color for the negative portion of a data bar to be the same as the fill color for the positive portion of the data bar:

conditional_format->type = LXW_CONDITIONAL_DATA_BAR;
conditional_format->bar_negative_color_same = LXW_TRUE;
worksheet_conditional_format_range(worksheet, RANGE("A3:C3"), conditional_format);

Note, this property is only visible in Excel 2010 and later.

parameter: bar_negative_border_color_same

The bar_negative_border_color_same property sets the border color for the negative portion of a data bar to be the same as the border color for the positive portion of the data bar:

conditional_format->type = LXW_CONDITIONAL_DATA_BAR;
worksheet_conditional_format_range(worksheet, RANGE("A3:C3"), conditional_format);

See the image above.

Note, this property is only visible in Excel 2010 and later.

parameter: bar_no_border

The bar_no_border property turns off the border for data bars:

conditional_format->type = LXW_CONDITIONAL_DATA_BAR;
conditional_format->bar_no_border = LXW_TRUE;
worksheet_conditional_format_range(worksheet, RANGE("A2:B2"), conditional_format);

Note, this property is only visible in Excel 2010 and later, however the default in Excel 2007 is to not have a border.

parameter: bar_direction

The bar_direction property sets the direction for data bars. This property can be either one of the values from lxw_conditional_format_bar_direction shown below. If the property isn't set then Excel will adjust the position automatically based on the context. The available options are:

Here is an example:

conditional_format->type = LXW_CONDITIONAL_DATA_BAR;
worksheet_conditional_format_range(worksheet, RANGE("A2:B2"), conditional_format);

Note, this property is only visible in Excel 2010 and later.

parameter: bar_axis_position

The bar_axis_position property sets the position of the axis that is shown in data bars when there are negative values to display. The property can be either midpoint or none.

conditional_format->type = LXW_CONDITIONAL_DATA_BAR;
worksheet_conditional_format_range(worksheet, RANGE("A2:B12"), conditional_format);

If the property isn't set then Excel will position the axis based on the range of positive and negative values. The setting are defined in lxw_conditional_bar_axis_position:

Note, this property is only visible in Excel 2010 and later.

parameter: bar_axis_color

The bar_axis_color property sets the color for the axis that is shown in data bars when there are negative values to display:

conditional_format->type = LXW_CONDITIONAL_DATA_BAR;
conditional_format->bar_axis_color = 0x0070C0;
worksheet_conditional_format_range(worksheet, RANGE("A3:C13"), conditional_format);

Note, this property is only visible in Excel 2010 and later.

parameter: data_bar_2010

The data_bar_2010 property sets Excel 2010 style data bars even when Excel 2010 specific properties aren't used. This can be used for ensure conformity across all the data bar formatting in a worksheet:

conditional_format->type = LXW_CONDITIONAL_DATA_BAR;
conditional_format->data_bar_2010 = LXW_TRUE;
worksheet_conditional_format_range(worksheet, RANGE("A3:C13"), conditional_format);

parameter: stop_if_true

The stop_if_true parameter can be used to set the "stop if true" feature of a conditional formatting rule when more than one rule is applied to a cell or a range of cells. When this parameter is set then subsequent rules are not evaluated if the current rule is true:

conditional_format->type = LXW_CONDITIONAL_TYPE_CELL;
conditional_format->value = 5;
conditional_format->stop_if_true = LXW_TRUE;
worksheet_conditional_format_cell(worksheet, CELL("A1"), conditional_format);

parameter: multi_range

The multi_range option is used to extend a conditional format over non-contiguous ranges.

It is possible to apply the conditional format to different cell ranges in a worksheet using multiple calls to worksheet_conditional_format(). However, as a minor optimization it is also possible in Excel to apply the same conditional format to different non-contiguous cell ranges.

This is replicated in worksheet_conditional_format() using the multi_range option. The range must contain the primary range for the conditional format and any others separated by spaces.

For example to apply one conditional format to two ranges, B3:K6 and B9:K12:

conditional_format->type = LXW_CONDITIONAL_TYPE_CELL;
conditional_format->value = 50;
conditional_format->format = format1;
conditional_format->multi_range = "B3:K6 B9:K12";
worksheet_conditional_format_range(worksheet6, RANGE("B3:K12"), conditional_format);

Next: Working with Cell Comments

lxw_conditional_format::bar_axis_color
lxw_color_t bar_axis_color
Definition: worksheet.h:1155
lxw_conditional_format::bar_negative_border_color_same
uint8_t bar_negative_border_color_same
Definition: worksheet.h:1131
lxw_conditional_format::min_value
double min_value
Definition: worksheet.h:1038
LXW_CONDITIONAL_CRITERIA_TEXT_BEGINS_WITH
@ LXW_CONDITIONAL_CRITERIA_TEXT_BEGINS_WITH
Definition: worksheet.h:332
lxw_conditional_format
Worksheet conditional formatting options.
Definition: worksheet.h:998
lxw_conditional_format::format
lxw_format * format
Definition: worksheet.h:1034
format_set_italic
void format_set_italic(lxw_format *format)
Turn on italic for the format font.
LXW_CONDITIONAL_CRITERIA_TEXT_ENDS_WITH
@ LXW_CONDITIONAL_CRITERIA_TEXT_ENDS_WITH
Definition: worksheet.h:335
LXW_CONDITIONAL_CRITERIA_TIME_PERIOD_LAST_7_DAYS
@ LXW_CONDITIONAL_CRITERIA_TIME_PERIOD_LAST_7_DAYS
Definition: worksheet.h:347
LXW_CONDITIONAL_BAR_AXIS_MIDPOINT
@ LXW_CONDITIONAL_BAR_AXIS_MIDPOINT
Definition: worksheet.h:464
lxw_conditional_format::value_string
char * value_string
Definition: worksheet.h:1021
lxw_conditional_format::min_rule_type
uint8_t min_rule_type
Definition: worksheet.h:1047
LXW_CONDITIONAL_TYPE_DUPLICATE
@ LXW_CONDITIONAL_TYPE_DUPLICATE
Definition: worksheet.h:246
LXW_CONDITIONAL_CRITERIA_GREATER_THAN_OR_EQUAL_TO
@ LXW_CONDITIONAL_CRITERIA_GREATER_THAN_OR_EQUAL_TO
Definition: worksheet.h:314
LXW_CONDITIONAL_BAR_DIRECTION_RIGHT_TO_LEFT
@ LXW_CONDITIONAL_BAR_DIRECTION_RIGHT_TO_LEFT
Definition: worksheet.h:446
format_set_bold
void format_set_bold(lxw_format *format)
Turn on bold for the format font.
LXW_CONDITIONAL_TYPE_AVERAGE
@ LXW_CONDITIONAL_TYPE_AVERAGE
Definition: worksheet.h:243
lxw_conditional_format::bar_direction
uint8_t bar_direction
Definition: worksheet.h:1142
lxw_conditional_format::mid_color
lxw_color_t mid_color
Definition: worksheet.h:1068
lxw_conditional_format::bar_border_color
lxw_color_t bar_border_color
Definition: worksheet.h:1114
LXW_CONDITIONAL_RULE_TYPE_NUMBER
@ LXW_CONDITIONAL_RULE_TYPE_NUMBER
Definition: worksheet.h:413
lxw_conditional_format::data_bar_2010
uint8_t data_bar_2010
Definition: worksheet.h:1102
LXW_CONDITIONAL_TYPE_TOP
@ LXW_CONDITIONAL_TYPE_TOP
Definition: worksheet.h:253
lxw_conditional_format::type
uint8_t type
Definition: worksheet.h:1003
LXW_CONDITIONAL_TYPE_TIME_PERIOD
@ LXW_CONDITIONAL_TYPE_TIME_PERIOD
Definition: worksheet.h:239
LXW_CONDITIONAL_TYPE_TEXT
@ LXW_CONDITIONAL_TYPE_TEXT
Definition: worksheet.h:235
LXW_CONDITIONAL_RULE_TYPE_PERCENTILE
@ LXW_CONDITIONAL_RULE_TYPE_PERCENTILE
Definition: worksheet.h:419
LXW_CONDITIONAL_TYPE_UNIQUE
@ LXW_CONDITIONAL_TYPE_UNIQUE
Definition: worksheet.h:249
lxw_format
Struct to represent the formatting properties of an Excel format.
Definition: format.h:358
LXW_CONDITIONAL_TYPE_ICON_SETS
@ LXW_CONDITIONAL_TYPE_ICON_SETS
Definition: worksheet.h:289
RANGE
#define RANGE(range)
Convert an Excel A1:B2 range into a (first_row, first_col, last_row, last_col) sequence.
Definition: utility.h:82
LXW_TRUE
@ LXW_TRUE
Definition: common.h:53
lxw_conditional_format::bar_only
uint8_t bar_only
Definition: worksheet.h:1094
lxw_conditional_format::mid_value
double mid_value
Definition: worksheet.h:1055
worksheet_conditional_format_cell
lxw_error worksheet_conditional_format_cell(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, lxw_conditional_format *conditional_format)
Add a conditional format to a worksheet cell.
LXW_CONDITIONAL_TYPE_FORMULA
@ LXW_CONDITIONAL_TYPE_FORMULA
Definition: worksheet.h:273
lxw_conditional_format::stop_if_true
uint8_t stop_if_true
Definition: worksheet.h:1190
format_set_bg_color
void format_set_bg_color(lxw_format *format, lxw_color_t color)
Set the pattern background color for a cell.
lxw_conditional_format::bar_negative_color_same
uint8_t bar_negative_color_same
Definition: worksheet.h:1125
LXW_CONDITIONAL_ICONS_3_TRAFFIC_LIGHTS_UNRIMMED
@ LXW_CONDITIONAL_ICONS_3_TRAFFIC_LIGHTS_UNRIMMED
Definition: worksheet.h:486
LXW_CONDITIONAL_CRITERIA_BETWEEN
@ LXW_CONDITIONAL_CRITERIA_BETWEEN
Definition: worksheet.h:320
LXW_CONDITIONAL_TYPE_NO_BLANKS
@ LXW_CONDITIONAL_TYPE_NO_BLANKS
Definition: worksheet.h:263
LXW_CONDITIONAL_TYPE_BLANKS
@ LXW_CONDITIONAL_TYPE_BLANKS
Definition: worksheet.h:260
LXW_CONDITIONAL_CRITERIA_TOP_OR_BOTTOM_PERCENT
@ LXW_CONDITIONAL_CRITERIA_TOP_OR_BOTTOM_PERCENT
Definition: worksheet.h:398
LXW_CONDITIONAL_CRITERIA_AVERAGE_1_STD_DEV_ABOVE
@ LXW_CONDITIONAL_CRITERIA_AVERAGE_1_STD_DEV_ABOVE
Definition: worksheet.h:380
lxw_conditional_format::bar_negative_border_color
lxw_color_t bar_negative_border_color
Definition: worksheet.h:1119
lxw_conditional_format::icons_only
uint8_t icons_only
Definition: worksheet.h:1167
lxw_conditional_format::min_color
lxw_color_t min_color
Definition: worksheet.h:1051
LXW_CONDITIONAL_TYPE_CELL
@ LXW_CONDITIONAL_TYPE_CELL
Definition: worksheet.h:231
lxw_conditional_format::bar_solid
uint8_t bar_solid
Definition: worksheet.h:1106
LXW_CONDITIONAL_DATA_BAR
@ LXW_CONDITIONAL_DATA_BAR
Definition: worksheet.h:285
lxw_conditional_format::bar_negative_color
lxw_color_t bar_negative_color
Definition: worksheet.h:1110
lxw_conditional_format::mid_rule_type
uint8_t mid_rule_type
Definition: worksheet.h:1064
lxw_conditional_format::bar_no_border
uint8_t bar_no_border
Definition: worksheet.h:1135
LXW_CONDITIONAL_CRITERIA_TIME_PERIOD_YESTERDAY
@ LXW_CONDITIONAL_CRITERIA_TIME_PERIOD_YESTERDAY
Definition: worksheet.h:338
LXW_CONDITIONAL_CRITERIA_TEXT_CONTAINING
@ LXW_CONDITIONAL_CRITERIA_TEXT_CONTAINING
Definition: worksheet.h:326
lxw_conditional_format::multi_range
char * multi_range
Definition: worksheet.h:1183
lxw_conditional_format::bar_axis_position
uint8_t bar_axis_position
Definition: worksheet.h:1150
LXW_CONDITIONAL_CRITERIA_TEXT_NOT_CONTAINING
@ LXW_CONDITIONAL_CRITERIA_TEXT_NOT_CONTAINING
Definition: worksheet.h:329
LXW_CONDITIONAL_TYPE_ERRORS
@ LXW_CONDITIONAL_TYPE_ERRORS
Definition: worksheet.h:266
lxw_conditional_format::bar_color
lxw_color_t bar_color
Definition: worksheet.h:1090
lxw_conditional_format::criteria
uint8_t criteria
Definition: worksheet.h:1011
LXW_CONDITIONAL_CRITERIA_EQUAL_TO
@ LXW_CONDITIONAL_CRITERIA_EQUAL_TO
Definition: worksheet.h:302
LXW_CONDITIONAL_ICONS_3_ARROWS_COLORED
@ LXW_CONDITIONAL_ICONS_3_ARROWS_COLORED
Definition: worksheet.h:477
LXW_CONDITIONAL_CRITERIA_GREATER_THAN
@ LXW_CONDITIONAL_CRITERIA_GREATER_THAN
Definition: worksheet.h:308
lxw_conditional_format::reverse_icons
uint8_t reverse_icons
Definition: worksheet.h:1163
LXW_COLOR_RED
@ LXW_COLOR_RED
Definition: format.h:217
lxw_conditional_format::icon_style
uint8_t icon_style
Definition: worksheet.h:1159
lxw_conditional_format::value
double value
Definition: worksheet.h:1015
LXW_CONDITIONAL_RULE_TYPE_PERCENT
@ LXW_CONDITIONAL_RULE_TYPE_PERCENT
Definition: worksheet.h:416
format_set_font_color
void format_set_font_color(lxw_format *format, lxw_color_t color)
Set the color of the font used in the cell.
LXW_CONDITIONAL_TYPE_NO_ERRORS
@ LXW_CONDITIONAL_TYPE_NO_ERRORS
Definition: worksheet.h:269
LXW_CONDITIONAL_CRITERIA_AVERAGE_ABOVE
@ LXW_CONDITIONAL_CRITERIA_AVERAGE_ABOVE
Definition: worksheet.h:368
lxw_conditional_format::max_rule_type
uint8_t max_rule_type
Definition: worksheet.h:1082
LXW_CONDITIONAL_CRITERIA_LESS_THAN
@ LXW_CONDITIONAL_CRITERIA_LESS_THAN
Definition: worksheet.h:311
lxw_conditional_format::max_value
double max_value
Definition: worksheet.h:1073
lxw_conditional_format::max_color
lxw_color_t max_color
Definition: worksheet.h:1086
worksheet_conditional_format_range
lxw_error worksheet_conditional_format_range(lxw_worksheet *worksheet, lxw_row_t first_row, lxw_col_t first_col, lxw_row_t last_row, lxw_col_t last_col, lxw_conditional_format *conditional_format)
Add a conditional format to a worksheet range.
CELL
#define CELL(cell)
Convert an Excel A1 cell string into a (row, col) pair.
Definition: utility.h:45
LXW_CONDITIONAL_3_COLOR_SCALE
@ LXW_CONDITIONAL_3_COLOR_SCALE
Definition: worksheet.h:281
LXW_CONDITIONAL_2_COLOR_SCALE
@ LXW_CONDITIONAL_2_COLOR_SCALE
Definition: worksheet.h:277
workbook_add_format
lxw_format * workbook_add_format(lxw_workbook *workbook)
Create a new Format object to formats cells in worksheets.
lxw_conditional_format::min_value_string
char * min_value_string
Definition: worksheet.h:1042