libxlsxwriter
Loading...
Searching...
No Matches
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);
void format_set_font_color(lxw_format *format, lxw_color_t color)
Set the color of the font used in the cell.
@ LXW_COLOR_RED
Definition: format.h:218
Struct to represent the formatting properties of an Excel format.
Definition: format.h:359
lxw_format * workbook_add_format(lxw_workbook *workbook)
Create a new Format object to formats cells in worksheets.

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));
Worksheet conditional formatting options.
Definition: worksheet.h:1125

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;
uint8_t criteria
Definition: worksheet.h:1138
double value
Definition: worksheet.h:1142
uint8_t type
Definition: worksheet.h:1130
@ LXW_CONDITIONAL_CRITERIA_LESS_THAN
Definition: worksheet.h:318
@ LXW_CONDITIONAL_TYPE_CELL
Definition: worksheet.h:238

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;
lxw_format * format
Definition: worksheet.h:1161

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);
#define RANGE(range)
Convert an Excel A1:B2 range into a (first_row, first_col, last_row, last_col) sequence.
Definition: utility.h:83
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.

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);
@ LXW_CONDITIONAL_CRITERIA_GREATER_THAN
Definition: worksheet.h:315

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);
double max_value
Definition: worksheet.h:1200
double min_value
Definition: worksheet.h:1165
@ LXW_CONDITIONAL_CRITERIA_BETWEEN
Definition: worksheet.h:327

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\"";
const char * value_string
Definition: worksheet.h:1148
@ LXW_CONDITIONAL_CRITERIA_EQUAL_TO
Definition: worksheet.h:309

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);
@ LXW_CONDITIONAL_CRITERIA_TIME_PERIOD_YESTERDAY
Definition: worksheet.h:345
@ LXW_CONDITIONAL_CRITERIA_TIME_PERIOD_LAST_7_DAYS
Definition: worksheet.h:354
@ LXW_CONDITIONAL_TYPE_TIME_PERIOD
Definition: worksheet.h:246

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);
@ LXW_CONDITIONAL_CRITERIA_TEXT_NOT_CONTAINING
Definition: worksheet.h:336
@ LXW_CONDITIONAL_CRITERIA_TEXT_ENDS_WITH
Definition: worksheet.h:342
@ LXW_CONDITIONAL_CRITERIA_TEXT_BEGINS_WITH
Definition: worksheet.h:339
@ LXW_CONDITIONAL_CRITERIA_TEXT_CONTAINING
Definition: worksheet.h:333
@ LXW_CONDITIONAL_TYPE_TEXT
Definition: worksheet.h:242

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);
@ LXW_CONDITIONAL_CRITERIA_AVERAGE_ABOVE
Definition: worksheet.h:375
@ LXW_CONDITIONAL_CRITERIA_AVERAGE_1_STD_DEV_ABOVE
Definition: worksheet.h:387
@ LXW_CONDITIONAL_TYPE_AVERAGE
Definition: worksheet.h:250

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);
@ LXW_CONDITIONAL_TYPE_DUPLICATE
Definition: worksheet.h:253

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);
@ LXW_CONDITIONAL_TYPE_UNIQUE
Definition: worksheet.h:256

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);
@ LXW_CONDITIONAL_TYPE_TOP
Definition: worksheet.h:260

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);
@ LXW_CONDITIONAL_CRITERIA_TOP_OR_BOTTOM_PERCENT
Definition: worksheet.h:405

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);
@ LXW_CONDITIONAL_TYPE_BLANKS
Definition: worksheet.h:267

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);
@ LXW_CONDITIONAL_TYPE_NO_BLANKS
Definition: worksheet.h:270

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);
@ LXW_CONDITIONAL_TYPE_ERRORS
Definition: worksheet.h:273

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);
@ LXW_CONDITIONAL_TYPE_NO_ERRORS
Definition: worksheet.h:276

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);
@ LXW_CONDITIONAL_TYPE_FORMULA
Definition: worksheet.h:280

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);
@ LXW_CONDITIONAL_2_COLOR_SCALE
Definition: worksheet.h:284

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;
lxw_color_t max_color
Definition: worksheet.h:1213
lxw_color_t min_color
Definition: worksheet.h:1178
uint8_t min_rule_type
Definition: worksheet.h:1174
uint8_t max_rule_type
Definition: worksheet.h:1209
@ LXW_CONDITIONAL_RULE_TYPE_NUMBER
Definition: worksheet.h:420

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);
@ LXW_CONDITIONAL_3_COLOR_SCALE
Definition: worksheet.h:288

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;
uint8_t mid_rule_type
Definition: worksheet.h:1191
double mid_value
Definition: worksheet.h:1182
lxw_color_t mid_color
Definition: worksheet.h:1195
@ LXW_CONDITIONAL_RULE_TYPE_PERCENTILE
Definition: worksheet.h:426
@ LXW_CONDITIONAL_RULE_TYPE_PERCENT
Definition: worksheet.h:423

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);
@ LXW_CONDITIONAL_DATA_BAR
Definition: worksheet.h:292

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);
uint8_t icon_style
Definition: worksheet.h:1286
#define CELL(cell)
Convert an Excel A1 cell string into a (row, col) pair.
Definition: utility.h:46
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_ICONS_3_ARROWS_COLORED
Definition: worksheet.h:484
@ LXW_CONDITIONAL_TYPE_ICON_SETS
Definition: worksheet.h:296

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);
@ LXW_TRUE
Definition: common.h:54
uint8_t reverse_icons
Definition: worksheet.h:1290
@ LXW_CONDITIONAL_ICONS_3_TRAFFIC_LIGHTS_UNRIMMED
Definition: worksheet.h:493

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);
uint8_t icons_only
Definition: worksheet.h:1294

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;
void format_set_italic(lxw_format *format)
Turn on italic for the format font.
void format_set_bold(lxw_format *format)
Turn on bold for the format font.

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);
void format_set_bg_color(lxw_format *format, lxw_color_t color)
Set the pattern background color for a cell.

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";
const char * min_value_string
Definition: worksheet.h:1169

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;
lxw_color_t bar_color
Definition: worksheet.h:1217

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);
uint8_t bar_only
Definition: worksheet.h:1221

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);
uint8_t bar_solid
Definition: worksheet.h:1233

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);
lxw_color_t bar_negative_color
Definition: worksheet.h:1237

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);
lxw_color_t bar_border_color
Definition: worksheet.h:1241

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);
lxw_color_t bar_negative_border_color
Definition: worksheet.h:1246

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);
uint8_t bar_negative_color_same
Definition: worksheet.h:1252

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);
uint8_t bar_negative_border_color_same
Definition: worksheet.h:1258

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);
uint8_t bar_no_border
Definition: worksheet.h:1262

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);
uint8_t bar_direction
Definition: worksheet.h:1269
@ LXW_CONDITIONAL_BAR_DIRECTION_RIGHT_TO_LEFT
Definition: worksheet.h:453

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);
uint8_t bar_axis_position
Definition: worksheet.h:1277
@ LXW_CONDITIONAL_BAR_AXIS_MIDPOINT
Definition: worksheet.h:471

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);
lxw_color_t bar_axis_color
Definition: worksheet.h:1282

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);
uint8_t data_bar_2010
Definition: worksheet.h:1229

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);
uint8_t stop_if_true
Definition: worksheet.h:1317

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);
const char * multi_range
Definition: worksheet.h:1310
@ LXW_CONDITIONAL_CRITERIA_GREATER_THAN_OR_EQUAL_TO
Definition: worksheet.h:321

Next: Working with Worksheet Tables