Conditional Formatting

Introduction

In addition to standard cell formatting options, most spreadsheet software provides an option known as Conditional Formatting, which allows formatting options to be set based on the value of a cell.

The cell's standard formatting defines most style elements that will always be applied, such as the font face and size; but Conditional Formatting allows you to override some elements of that cell style such as number format mask; font colour, bold, italic and underlining; borders; and fill colour and pattern.

Conditional Formatting can be applied to individual cells, or to a range of cells.

Example

As a simple example in MS Excel itself, if we wanted to highlight all cells in the range A1:A10 that contain values greater than 80, start by selecting the range of cells.

On the Home tab, in the "Styles" group, click "Conditional Formatting". This allows us to select an Excel Wizard to guide us through the process of creating a Conditional Rule and defining a Style for that rule.

Click "Highlight Cells Rules", then "Greater Than".

Enter the value "80" in the prompt box; and either select one of the pre-defined formatting style (or create a custom style from there).

Then click "OK". The rule is immediately applied to the selected range of cells, highlighting all those with a value greater than 80 in the chosen style.

Any change to the value of a cell within that range will immediately check the rule, and automatically apply the new styling if it applies.

If we wanted to set up the same Conditional Formatting rule in PHPSpreadsheet, we would do so using the following code:

$conditional = new \PhpOffice\PhpSpreadsheet\Style\Conditional();
$conditional->setConditionType(\PhpOffice\PhpSpreadsheet\Style\Conditional::CONDITION_CELLIS);
$conditional->setOperatorType(\PhpOffice\PhpSpreadsheet\Style\Conditional::OPERATOR_GREATERTHAN);
$conditional->addCondition(80);
$conditional->getStyle()->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_DARKGREEN);
$conditional->getStyle()->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
$conditional->getStyle()->getFill()->getEndColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_GREEN);

$conditionalStyles = $spreadsheet->getActiveSheet()->getStyle('A1:A10')->getConditionalStyles();
$conditionalStyles[] = $conditional;

$spreadsheet->getActiveSheet()->getStyle('A1:A10')->setConditionalStyles($conditionalStyles);

Depending on the Rules that we might want to apply for a Condition, sometimes an "operator Type" is required, sometimes not (and not all Operator Types are appropriate for all Condition Types); sometimes a "Condition" is required (or even several conditions), sometimes not, and sometimes it must be a specific Excel formula expression. Creating conditions manually requires a good knowledge of when these different properties need to be set, and with what type of values. This isn't something that an end-user developer should be expected to know.

So - to eliminate this need for complex and arcane knowledge - since PHPSpreadsheet verson 1.22.0 there is also a series of Wizards that can assist with creating Conditional Formatting rules, and which are capable of setting the appropriate operators and conditions (including the sometimes complex Excel formula expressions) for a Conditional Rule:

$wizardFactory = new \PhpOffice\PhpSpreadsheet\Style\ConditionalFormatting\Wizard('A1:A10');
$wizard = $wizardFactory->newRule(\PhpOffice\PhpSpreadsheet\Style\ConditionalFormatting\Wizard::CELL_VALUE);
$wizard->greaterThan(80);
$wizard->getStyle()->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_DARKGREEN);
$wizard->getStyle()->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
$wizard->getStyle()->getFill()->getEndColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_GREEN);

$conditional = $wizard->getConditional();

The Wizards know which operator types match up with condition types, and provide more meaningful method names for operators, and they build expressions and formulae when required; and also work well with an IDE such as PHPStorm.

---

Note that $conditionalStyles is an array: it is possible to apply several conditions to the same range of cells. If we also wanted to highlight values that were less than 10 in the the A1:A10 range, we can add a second style rule.

In Excel, we would do this by selecting the range again, and going through the same process, this time selecting the "Highlight Cells Rules", then "Less Than" from the "Conditional Styles" menu, entering the value "10" in the prompt box, and selecting the appropriate style.

In PHPSpreadsheet, we would do:

$conditional2 = new \PhpOffice\PhpSpreadsheet\Style\Conditional();
$conditional2->setConditionType(\PhpOffice\PhpSpreadsheet\Style\Conditional::CONDITION_CELLIS);
$conditional2->setOperatorType(\PhpOffice\PhpSpreadsheet\Style\Conditional::OPERATOR_LESSTHAN);
$conditional2->addCondition(10);
$conditional2->getStyle()->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_DARKRED);
$conditional2->getStyle()->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
$conditional2->getStyle()->getFill()->getEndColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);

$conditionalStyles = $spreadsheet->getActiveSheet()->getStyle('A1:A10')->getConditionalStyles();
$conditionalStyles[] = $conditional2;

$spreadsheet->getActiveSheet()->getStyle('A1:A10')->setConditionalStyles($conditionalStyles);

or again, using the Wizard:

$wizardFactory = new \PhpOffice\PhpSpreadsheet\Style\ConditionalFormatting\Wizard('A1:A10');
$wizard = $wizardFactory->newRule(\PhpOffice\PhpSpreadsheet\Style\ConditionalFormatting\Wizard::CELL_VALUE);
$wizard->lessThan(10);
$wizard->getStyle()->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_DARKGREEN);
$wizard->getStyle()->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
$wizard->getStyle()->getFill()->getEndColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_GREEN);

$conditional = $wizard->getConditional();

Order of Evaluating Multiple Rules/Conditions

$conditionalStyles is an array, which not only represents multiple conditions that can be applied to a cell (or range of cells), but also the order in which they are checked. Some spreadsheet programs stop processing conditions once they find a match. On the other hand, MS Excel will check each of those conditions in turn in the order they are defined. It will stop checking only if it finds a matching rule that specifies 'stop if true'; however, if it finds conflicting matches with conflicting formatting (e.g. both set a background fill color but use different choices), the first match wins. In either case, this means that the order of checking conditions can be important.

Consider the following. We have one condition that checks if a cell value is between -2 and 2, styling the fill color of the cell in yellow if that condition matches; and a second condition that checks if the cell value is equal to 0, styling the fill color of the cell in red if that matches. - Yellow if value is between -2 and 2 - Red if value equals 0

If they are evaluated in the order I've described above, and the cell has a value of 0, then the first rule will match (because 0 is between -2 and 2), and the cell will be styled in yellow, and no further conditions will be checked. So the rule that styles the cell in red if the value is 0 will never be assessed, even though that would also match (and is probably what we actually wanted, otherwise why have an explicit rule defined for that condition).

If the rule order is reversed - Red if value equals 0 - Yellow if value is between -2 and 2

then the cell containing the value 0 will be rendered in red, because that is the first matching condition; and the formatting in the other condition conflicts with this, so is discarded.

So when you have multiple conditions where the rules might "overlap", the order of these is important.

If the cell matches multiple conditions, Excel (but not most other spreadsheet programs) will apply non-conflicting styles from each match. So, for the example above, if we wanted a match of 0 to have a different font color rather than a different fill color, Excel can honor both.

Here is the same spreadsheet opened in LibreOffice - cell A4 has only the first conditional style applied to it. (You would see the same if you checked 'Stop if True' in Excel.) If you want the spreadsheet to appear the same in both Excel and LibreOffice, you would need to use more complicated conditions.

PhpSpreadsheet supports the setting of Stop If True.

Reader/Writer Support

Currently, the following Conditional Types are supported for the following Readers and Writers:

MS Excel	Conditional Type	Readers	Writers
	Cell Value	Conditional::CONDITION_CELLIS	Xlsx
Specific Text	Conditional::CONDITION_CONTAINSTEXT	Xlsx	Xlsx
	Conditional::CONDITION_NOTCONTAINSTEXT	Xlsx	Xlsx
	Conditional::CONDITION_BEGINSWITH	Xlsx	Xlsx
	Conditional::CONDITION_ENDSWITH	Xlsx	Xlsx
Dates Occurring	Conditional::CONDITION_TIMEPERIOD	Xlsx	Xlsx
Blanks	Conditional::CONDITION_CONTAINSBLANKS	Xlsx	Xlsx
No Blanks	Conditional::CONDITION_NOTCONTAINSBLANKS	Xlsx	Xlsx
Errors	Conditional::CONDITION_CONTAINSERRORS	Xlsx	Xlsx
No Errors	Conditional::CONDITION_NOTCONTAINSERRORS	Xlsx	Xlsx
Duplicates/Unique	Conditional::CONDITION_DUPLICATES	Xlsx	Xlsx
	Conditional::CONDITION_UNIQUE	Xlsx	Xlsx
Use a formula	Conditional::CONDITION_EXPRESSION	Xlsx	Xlsx, Xls
Data Bars	Conditional::CONDITION_DATABAR	Xlsx	Xlsx

The following Conditional Types are currently not supported by any Readers or Writers:

MS Excel	Conditional Type
Above/Below Average	?
Top/Bottom Items	?
Top/Bottom %age	?
Colour Scales	?
Icon Sets	?

Unsupported types will by ignored by the Readers, and cannot be created through PHPSpreadsheet.

Wizards

While the Wizards don't simplify defining the Conditional Style itself; they do make it easier to define the conditions (the rules) where that style will be applied. MS Excel itself has wizards to guide the creation of Conditional Formatting rules and styles.

The Wizard Factory allows us to retrieve the appropriate Wizard for the CF Rule that we want to apply. Most of those that have already been defined fall under the "Format only cells that contain" category. MS Excel provides a whole series of different types of rule, each of which has it's own formatting and logic. The Wizards try to replicate this logic and behaviour, similar to Excel's own "Formatting Rule" helper wizard.

MS Excel	Wizard Factory newRule() Type Constant	Wizard Class Name
Cell Value	Wizard::CELL_VALUE	CellValue
Specific Text	Wizard::TEXT_VALUE	TextValue
Dates Occurring	Wizard::DATES_OCCURRING	DateValue
Blanks	Wizard::BLANKS	Blanks
No Blanks	Wizard::NOT_BLANKS	Blanks
Errors	Wizard::ERRORS	Errors
No Errors	Wizard::NOT_ERRORS	Errors

Additionally, Wizards also exists for "Format only unique or duplicate values", and for "Use a formula to determine which cells to format":

MS Excel	Wizard Factory newRule() Type Constant	Wizard Class Name
Duplicates/Unique	Wizard::DUPLICATES or Wizard::UNIQUE	Duplicates
Use a formula	Wizard::EXPRESSION or Wizard::FORMULA	Expression

There is currently no Wizard for Data Bars, even though this Conditional Type is supported by the Xlsx Reader and Writer.

---

We instantiate the Wizard Factory, passing in the cell range where we want to apply Conditional Formatting rules; and can then call the newRule() method, passing in the type of Conditional Rule that we want to create in order to return the appropriate Wizard:

$wizardFactory = new \PhpOffice\PhpSpreadsheet\Style\ConditionalFormatting\Wizard('C3:E5');
$wizard = $wizardFactory->newRule(\PhpOffice\PhpSpreadsheet\Style\ConditionalFormatting\Wizard::CELL_VALUE);

You can, of course, instantiate the Wizard that you want directly, rather than using the factory; but still remember to pass in the cell range.

$wizard = new \PhpOffice\PhpSpreadsheet\Style\ConditionalFormatting\Wizard\CellValue('C3:E5');

That Wizard then provides methods allowing us to define the rule, setting the operator and the values that we want to compare for that rule. Note that not all rules require values, or even operators, but the individual Wizards provide whatever is necessary; and this document lists all options available for every Wizard.

Once we have used the Wizard to define the conditions and values that we want; and have defined a style using the setStyle() method, then we call the Wizard's getConditional() method to return a Conditional object that can be added to the array of Conditional Styles that we pass to setConditionalStyles().

CellValue Wizard

For the CellValue Wizard, we always need to provide an operator and a value; and for the "between" and "notBetween" operators, we need to provide two values to specify a range.

Condition Type	Wizard Factory newRule() Type Constant	Conditional Operator Type	Wizard Methods	Notes
Conditional::CONDITION_CELLIS	Wizard::CELL_VALUE	Conditional::OPERATOR_EQUAL	equals()
		Conditional::OPERATOR_NOTEQUAL	notEquals()
		Conditional::OPERATOR_GREATERTHAN	greaterThan()
		Conditional::OPERATOR_GREATERTHANOREQUAL	greaterThanOrEqual()
		Conditional::OPERATOR_LESSTHAN	lessThan()
		Conditional::OPERATOR_LESSTHANOREQUAL	lessThanOrEqual()
		Conditional::OPERATOR_BETWEEN	between()
		Conditional::OPERATOR_NOTBETWEEN	notBetween()
			and()	Used to provide the second operand for between() and `notBetween()

A single operator call is required for every rule (except between() and notBetween, where the Wizard also provides and()); and providing a value is mandatory for all operators. The values that we need to provide for each operator can be numeric, boolean or string literals (even NULL); cell references; or formulae.

So to set the rule using an operator, we would make a call like:

$wizard->lessThan(10);

or when setting a between() or notBetween() rule, we can make use of the fluent interface with the and() method to set the range of values:

$wizard->between(-10)->and(10);

Providing a second value using and() is mandatory for a between() or notBetween() range.

To retrieve the Conditional, to add it to our $conditionalStyles array, we call the Wizard's getConditional() method.

$conditional = $wizard->getConditional();
$conditionalStyles = [$conditional];

or simply

$conditionalStyles[] = $wizard->getConditional();

Putting it all together, we can use a block of code like (using pre-defined Style objects):

$cellRange = 'A2:E5';
$conditionalStyles = [];
$wizardFactory = new Wizard($cellRange);
/** @var Wizard\CellValue $cellWizard */
$cellWizard = $wizardFactory->newRule(Wizard::CELL_VALUE);

$cellWizard->equals(0)
    ->setStyle($yellowStyle);
$conditionalStyles[] = $cellWizard->getConditional();

$cellWizard->greaterThan(0)
    ->setStyle($greenStyle);
$conditionalStyles[] = $cellWizard->getConditional();

$cellWizard->lessThan(0)
    ->setStyle($redStyle);
$conditionalStyles[] = $cellWizard->getConditional();

$spreadsheet->getActiveSheet()
    ->getStyle($cellWizard->getCellRange())
    ->setConditionalStyles($conditionalStyles);

You can find an example that demonstrates this in the code samples for the repo.

Value Types

When we need to provide a value for an operator, that value can be numeric, boolean or string literals (even NULL); cell references; or even an Excel formulae.

Literals

If the value is a literal (even a string literal), we simply need to pass the value; the Wizard will ensure that strings are correctly quoted when we get the Conditional from the Wizard.

$wizard->equals('Hello World');

If you weren't using the Wizard, but were creating the Conditional directly, you would need to remember to wrap this value in quotes yourself ('"Hello World"')

However, a cell reference or a formula are also string data, so we need to tell the Wizard if the value that we are passing in isn't just a string literal value, but should be treated as a cell reference or as a formula.

Cell References

If we want to use the value from cell H9 in our rule; then we need to pass a value type of VALUE_TYPE_CELL to the operator, in addition to the cell reference itself.

$wizard->equals('$H$9', Wizard::VALUE_TYPE_CELL);

You can find an example that demonstrates this in the code samples for the repo.

Note that we are passing the cell as an absolute cell reference, "pinned" (with the $ symbol) for both the row and the column.

In this next example, we need to use relative cell references, so that the comparison will match the value in column A against the values in columns B and C for each row in our range (A18:A20); ie, test if the value in A18 is between the values in B18 and C18, test if the value in A19 is between the values in B19 and C19, etc.

$wizard->between('$B1', Wizard::VALUE_TYPE_CELL)
    ->and('$C1', Wizard::VALUE_TYPE_CELL)
    ->setStyle($greenStyle);

This example can also be found in the code samples for the repo.

In this case, we "pin" the column for the address; but leave the row "unpinned". Notice also that we treat the first cell in our range as cell A1: the relative row number will be adjusted automatically to match our defined range; that is, the range that we specified when we instantiated the Wizard (passed in through the Wizard Factory) when we make the call to getConditional().

Formulae

It is also possible to set the value/operand as an Excel formula expression, not simply a literal value or a cell reference. Again, we do need to specify that the value is a Formula.

$cellRange = 'C26:C28';
$conditionalStyles = [];
$wizardFactory = new Wizard($cellRange);
/** @var Wizard\CellValue $cellWizard */
$cellWizard = $wizardFactory->newRule(Wizard::CELL_VALUE);

$cellWizard->equals('CONCATENATE($A1," ",$B1)', Wizard::VALUE_TYPE_FORMULA)
    ->setStyle($yellowStyle);
$conditionalStyles[] = $cellWizard->getConditional();

$spreadsheet->getActiveSheet()
    ->getStyle($cellWizard->getCellRange())
    ->setConditionalStyles($conditionalStyles);

You can find an example that demonstrates this in the code samples for the repo.

When the formula contains cell references, we again need to be careful with pinning to absolute columns/rows; and when unpinned, we reference based on the top-left cell of the range being cell A1 when we define the formula. Here we're defining the formula as CONCATENATE($A1," ",$B1), so we're "pinning" the column references for columns A and B; but leaving the row unpinned (explicitly set to start from row 1), which is then adjusted to the conditional range when we get the Conditional from the Wizard, and adjusted to the individual rows within that range when MS Excel displays the results.

TextValue Wizard

While we can use the CellValue Wizard to do basic string comparison rules, the TextValue Wizard provides rules for comparing parts of a string value.

For the TextValue Wizard, we always need to provide an operator and a value. As with the CellValue Wizard, values can be literals (but should always be string literals), cell references, or formula.

Condition Type	Wizard Factory newRule() Type Constant	Conditional Operator Type	Wizard Methods	Notes
Conditional::CONDITION_CONTAINSTEXT	Wizard::TEXT_VALUE	Conditional::OPERATOR_CONTAINSTEXT	contains()
Conditional::CONDITION_NOTCONTAINSTEXT	Wizard::TEXT_VALUE	Conditional::OPERATOR_NOTCONTAINS	doesNotContain()
			doesntContain()	synonym for doesNotContain()
Conditional::CONDITION_BEGINSWITH	Wizard::TEXT_VALUE	Conditional::OPERATOR_BEGINSWITH	beginsWith()
			startsWith()	synonym for beginsWith()
Conditional::CONDITION_ENDSWITH	Wizard::TEXT_VALUE	Conditional::OPERATOR_ENDSWITH	endsWith()

The Conditional actually uses a separate "Condition Type" for each option, each with its own "Operator Type", and the condition should be an Excel formula (not simply the string value to check), and with a custom text attribute. The Wizard should make it a lot easier to create these conditional rules.

To create a Conditional rule manually, you would need to do something like:

$cellRange = 'A14:B16';
$conditionalStyles = [];
$conditional = new Conditional();
// Remember to use the correct Condition Type
$conditional->setConditionType(Conditional::CONDITION_CONTAINSTEXT);
// Remember to use the correct Operator Type
$conditional->setOperatorType(Conditional::OPERATOR_CONTAINSTEXT);
// Remember to set the text attribute
// Remember to wrap the string literal
$conditional->setText('"LL"');
// Remember that the condition should be the first element in an array
// Remember that we need a specific formula for this Conditional 
// Remember to wrap the string literal
// Remember to use the top-left cell of the range that we want to apply this rule to
$conditional->setConditions(['NOT(ISERROR(SEARCH("LL",A14)))']);
$conditional->setStyle($greenStyle);

$conditionalStyles[]

$spreadsheet->getActiveSheet()
    ->getStyle($cellRange)
    ->setConditionalStyles($conditionalStyles);

But using the Wizard, the same Conditional rule can be created by:

$cellRange = 'A14:B16';
$conditionalStyles = [];
$wizardFactory = new Wizard($cellRange);
/** @var Wizard\TextValue $textWizard */
$textWizard = $wizardFactory->newRule(Wizard::TEXT_VALUE);

$textWizard->contains('LL')
    ->setStyle($greenStyle);
$conditionalStyles[] = $textWizard->getConditional();

$spreadsheet->getActiveSheet()
    ->getStyle($textWizard->getCellRange())
    ->setConditionalStyles($conditionalStyles);

You can find an example that demonstrates this in the code samples for the repo.

There are also examples in that file for notContains(), beginsWith() and endsWith() comparisons; using literal text, and with cell references.

The actual Excel Expressions used (and that you would need to set manually if you were defining the Conditional yourself rather than using the Wizard) are listed below:

Conditional Type	Condition Expression
Conditional::CONDITION_CONTAINSTEXT	NOT(ISERROR(SEARCH(<value>,<cell address>)))
Conditional::CONDITION_NOTCONTAINSTEXT	ISERROR(SEARCH(<value>,<cell address>))</
Conditional::CONDITION_BEGINSWITH	LEFT(<cell address>,LEN(<value>))=<value>
Conditional::CONDITION_ENDSWITH	RIGHT(<cell address>,LEN(<value>))=<value>

The <cell address> always references the top-left cell in the range of cells for this Conditional Formatting Rule. The <value> should be wrapped in double quotes (") for string literals; but unquoted if it is a value stored in a cell reference, or a formula. The TextValue Wizard handles defining these expressions for you.

As with the operand for the CellValue Wizard, you can specify the value passed to contains(), doesNotContain(), beginsWith() and endsWith() as a cell reference, or as a formula; and if you do so, then you need to pass a second argument to those methods specifying Wizard::VALUE_TYPE_CELL or Wizard::VALUE_TYPE_FORMULA. The same rules also apply to "pinning" cell references as described above for the CellValue Wizard.

$textWizard->beginsWith('$D$1', Wizard::VALUE_TYPE_CELL)
    ->setStyle($yellowStyle);

DateValue Wizard

For the DateValue Wizard, we always need to provide an operator; but no value is required.

Condition Type	Wizard Factory newRule() Type Constant	Conditional Operator Type	Wizard Methods	Notes
Conditional::CONDITION_TIMEPERIOD	Wizard::DATES_OCCURRING	Conditional::TIMEPERIOD_TODAY	today()
		Conditional::TIMEPERIOD_YESTERDAY	yesterday()
		Conditional::TIMEPERIOD_TOMORROW	tomorrow()
		Conditional::TIMEPERIOD_LAST_7_DAYS	last7Days()
			lastSevenDays()	synonym for last7Days()
		Conditional::TIMEPERIOD_LAST_WEEK	lastWeek()
		Conditional::TIMEPERIOD_THIS_WEEK	thisWeek()
		Conditional::TIMEPERIOD_NEXT_WEEK	nextWeek()
		Conditional::TIMEPERIOD_LAST_MONTH	lastMonth()
		Conditional::TIMEPERIOD_THIS_MONTH	thisMonth()
		Conditional::TIMEPERIOD_NEXT_MONTH	nextMonth()

The Conditional has no actual "Operator Type", and the condition/value should be an Excel formula, and with a custom timePeriod attribute. The Wizard should make it a lot easier to create these condition rules.

The above image shows a grid that demonstrate each of the possible Date Occurring rules, and was generated using the code samples

Typical sample code wod look something like:

$wizardFactory = new Wizard("E2:E19");
/** @var Wizard\DateValue $dateWizard */
$dateWizard = $wizardFactory->newRule(Wizard::DATES_OCCURRING);
$conditionalStyles = [];

$dateWizard->last7Days()
    ->setStyle($yellowStyle);

$conditionalStyles[] = $dateWizard->getConditional();

$spreadsheet->getActiveSheet()
    ->getStyle($dateWizard->getCellRange())
    ->setConditionalStyles($conditionalStyles);

The actual Excel Expressions used (and that you would need to set manually if you were defining the Conditional yourself rather than using the Wizard) are listed below:

timePeriod Attribute	Condition Expression
today	FLOOR(<cell address>,1)=TODAY()-1
yesterday	FLOOR(<cell address>,1)=TODAY()
tomorrow	FLOOR(<cell address>,1)=TODAY()+1
last7Days	AND(TODAY()-FLOOR(<cell address>,1)<=6,FLOOR(<cell address>,1)<=TODAY())
lastWeek	AND(TODAY()-ROUNDDOWN(<cell address>,0)>=(WEEKDAY(TODAY())),TODAY()-ROUNDDOWN(<cell address>,0)<(WEEKDAY(TODAY())+7))
thisWeek	AND(TODAY()-ROUNDDOWN(<cell address>,0)<=WEEKDAY(TODAY())-1,ROUNDDOWN(<cell address>,0)-TODAY()<=7-WEEKDAY(TODAY()))
nextWeek	AND(ROUNDDOWN(<cell address>,0)-TODAY()>(7-WEEKDAY(TODAY())),ROUNDDOWN(<cell address>,0)-TODAY()<(15-WEEKDAY(TODAY())))
lastMonth	AND(MONTH(<cell address>)=MONTH(EDATE(TODAY(),0-1)),YEAR(<cell address>)=YEAR(EDATE(TODAY(),0-1)))
thisMonth	AND(MONTH(<cell address>)=MONTH(TODAY()),YEAR(<cell address>)=YEAR(TODAY()))
nextMonth	AND(MONTH(<cell address>)=MONTH(EDATE(TODAY(),0+1)),YEAR(<cell address>)=YEAR(EDATE(TODAY(),0+1)))

The <cell address> always references the top-left cell in the range of cells for this Conditional Formatting Rule.

Blanks Wizard

This Wizard is used to define a simple boolean state rule, to determine whether a cell is blank or not blank. Whether created using the Wizard Factory with a rule type of Wizard::BLANKS or Wizard::NOT_BLANKS, the same Blanks Wizard is returned. The only difference is that in the one case the rule state is pre-set for CONDITION_CONTAINSBLANKS, in the other it is pre-set for CONDITION_NOTCONTAINSBLANKS. However, you can switch between the two rules using the isBlank() and notBlank() methods; and it is only at the point when you call getConditional() that a Conditional will be returned based on the current state of the Wizard.

Condition Type	Wizard Factory newRule() Type Constant	Conditional Operator Type	Wizard Methods	Notes
Conditional::CONDITION_CONTAINSBLANKS	Wizard::BLANKS	-	isBlank()	Default state
			notBlank()
			isEmpty()	Synonym for isBlank()
			notEmpty()	Synonym for notBlank()
Conditional::CONDITION_NOTCONTAINSBLANKS	Wizard::NOT_BLANKS	-	notBlank()	Default state
			isBlank()
			isEmpty()	Synonym for isBlank()
			notEmpty()	Synonym for notBlank()

The following code shows the same Blanks Wizard being used to create both Blank and Non-Blank Conditionals, using a pre-defined $redStyle Style object for Blanks, and a pre-defined $greenStyle Style object for Non-Blanks.

$cellRange = 'A2:B3';
$conditionalStyles = [];
$wizardFactory = new Wizard($cellRange);
/** @var Wizard\Blanks $blanksWizard */
$blanksWizard = $wizardFactory->newRule(Wizard::BLANKS);

$blanksWizard->setStyle($redStyle);
$conditionalStyles[] = $blanksWizard->getConditional();

$blanksWizard->notBlank()
    ->setStyle($greenStyle);
$conditionalStyles[] = $blanksWizard->getConditional();

$spreadsheet->getActiveSheet()
    ->getStyle($blanksWizard->getCellRange())
    ->setConditionalStyles($conditionalStyles);

This example can also be found in the code samples for the repo.

No operand/value is required for the Blanks Wizard methods; but the Conditional that is returned contains a defined expression with an Excel formula:

Blanks Type	Condition Expression
isBlank()	LEN(TRIM(<cell address>))=0
notBlank()	LEN(TRIM(<cell address>))>0

The <cell address> always references the top-left cell in the range of cells for this Conditional Formatting Rule.

Errors Wizard

Like the Blanks Wizard, this Wizard is used to define a simple boolean state rule, to determine whether a cell with a formula value results in an error or not. Whether created using the Wizard Factory with a rule type of Wizard::ERRORS or Wizard::NOT_ERRORS, the same Errors Wizard is returned. The only difference is that in the one case the rule state is pre-set for CONDITION_CONTAINSERRORS, in the other it is pre-set for CONDITION_NOTCONTAINSERRORS. However, you can switch between the two rules using the isError() and notError() methods; and it is only at the point when you call getConditional() that a Conditional will be returned based on the current state of the Wizard.

Condition Type	Wizard Factory newRule() Type Constant	Conditional Operator Type	Wizard Methods	Notes
Conditional::CONDITION_CONTAINSERRORS	Wizard::ERRORS	-	isError()	Default state
			notError()
Conditional::CONDITION_NOTCONTAINSERRORS	Wizard::NOT_ERRORS	-	notError()	Default state
			isError()

The following code shows the same Errors Wizard being used to create both Error and Non-Error Conditionals, using a pre-defined $redStyle Style object for Errors, and a pre-defined $greenStyle Style object for Non-Errors.

$cellRange = 'C2:C6';
$conditionalStyles = [];
$wizardFactory = new Wizard($cellRange);
/** @var Wizard\Errors $errorsWizard */
$errorsWizard = $wizardFactory->newRule(Wizard::ERRORS);

$errorsWizard->setStyle($redStyle);
$conditionalStyles[] = $errorsWizard->getConditional();

$errorsWizard->notError()
    ->setStyle($greenStyle);
$conditionalStyles[] = $errorsWizard->getConditional();

$spreadsheet->getActiveSheet()
    ->getStyle($errorsWizard->getCellRange())
    ->setConditionalStyles($conditionalStyles);

This example can also be found in the code samples for the repo.

No operand/value is required for the Errors Wizard methods; but the Conditional that is returned contains a defined expression with an Excel formula:

Blanks Type	Condition Expression
isError()	ISERROR(<cell address>)
notError()	NOT(ISERROR(<cell address>))

The <cell address> always references the top-left cell in the range of cells for this Conditional Formatting Rule.

Duplicates/Unique Wizard

This Wizard is used to define a simple boolean state rule, to determine whether a cell value matches any other cells with the same value within the conditional cell range, or if the value is unique in that range of cells. It only has any meaning if it is applied to a range of cells, not to an individual cell. Whether created using the Wizard Factory with a rule type of Wizard::DUPLICATES or Wizard::UNIQUE, the same Duplicates Wizard is returned. The only difference is that in the one case the rule state is pre-set for CONDITION_DUPLICATES, in the other it is pre-set for CONDITION_UNIQUE. However, you can switch between the two rules using the duplicates() and unique() methods; and it is only at the point when you call getConditional() that a Conditional will be returned based on the current state of the Wizard.

Condition Type	Wizard Factory newRule() Type Constant	Conditional Operator Type	Wizard Methods	Notes
Conditional::CONDITION_DUPLICATES	Wizard::DUPLICATES	-	duplicates()	Default state
			unique()
Conditional::CONDITION_UNIQUE	Wizard::UNIQUE	-	unique()	Default state
			duplicates()

The following code shows the same Duplicates Wizard being used to create both Blank and Non-Blank Conditionals, using a pre-defined $redStyle Style object for Blanks, and a pre-defined $greenStyle Style object for Non-Blanks.

$cellRange = 'A2:E6';
$conditionalStyles = [];
$wizardFactory = new Wizard($cellRange);
/** @var Wizard\Duplicats $duplicatesWizard */
$duplicatesWizard = $wizardFactory->newRule(Wizard::DUPLICATES);

$duplicatesWizard->setStyle($redStyle);
$conditionalStyles[] = $duplicatesWizard->getConditional();

$duplicatesWizard->unique()
    ->setStyle($greenStyle);
$conditionalStyles[] = $duplicatesWizard->getConditional();

$spreadsheet->getActiveSheet()
    ->getStyle($duplicatesWizard->getCellRange())
    ->setConditionalStyles($conditionalStyles);

This example can also be found in the code samples for the repo.

Duplicates/Uniques Conditions are only identified by a Condition Type in Excel, with no operator and no expression.

Expression Wizard

The Expression Wizard expects to be provided with an Expression, an MS Excel formula that evaluates to either false or true.

Condition Type	Wizard Factory newRule() Type Constant	Conditional Operator Type	Wizard Methods	Notes
Conditional::CONDITION_EXPRESSION	Wizard::EXPRESSION or Wizard::FORMULA	-	expression()	The argument is an Excel formula that evaluates to true or false
				formula()

Just as a simple example, here's a code snippet demonstrating expressions to determine if a cell contains an odd or an even number value:

$cellRange = 'A2:C11';
$conditionalStyles = [];
$wizardFactory = new Wizard($cellRange);
/** @var Wizard\Expression $expressionWizard */
$expressionWizard = $wizardFactory->newRule(Wizard::EXPRESSION);

$expressionWizard->expression('ISODD(A1)')
    ->setStyle($greenStyle);
$conditionalStyles[] = $expressionWizard->getConditional();

$expressionWizard->expression('ISEVEN(A1)')
    ->setStyle($yellowStyle);
$conditionalStyles[] = $expressionWizard->getConditional();

$spreadsheet->getActiveSheet()
    ->getStyle($expressionWizard->getCellRange())
    ->setConditionalStyles($conditionalStyles);

This example can also be found in the code samples for the repo.

As a more complicated example, let's look at a Sales Grid, and use conditional formatting to highlight sales in the "USA" region:

$greenStyleMoney = clone $greenStyle;
$greenStyleMoney->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_ACCOUNTING_USD);

$cellRange = 'A17:D22';
$conditionalStyles = [];
$wizardFactory = new Wizard($cellRange);
/** @var Wizard\Expression $expressionWizard */
$expressionWizard = $wizardFactory->newRule(Wizard::EXPRESSION);

$expressionWizard->expression('$C1="USA"')
    ->setStyle($greenStyleMoney);
$conditionalStyles[] = $expressionWizard->getConditional();

$spreadsheet->getActiveSheet()
    ->getStyle($expressionWizard->getCellRange())
    ->setConditionalStyles($conditionalStyles);

This example can also be found in the code samples for the repo.

Or we could apply multiple comparisons in the same expression, so to check for all sales for the "USA" region in "Q4", combining them using an Excel AND():

$expressionWizard->expression('AND($C1="USA",$D1="Q4")')
    ->setStyle($greenStyleMoney);

This example can also be found in the code samples for the repo.

General Notes

Stop If True, and No Format Set

Normally, Excel continues to check even after it finds a match. To tell it to stop once a match is found, 'stop if true' should be specified:

$conditional->setStopIfTrue(true);

Sometimes you want a matched cell to just show its unconditional format. This is most useful in conjunction with 'stop if true'.

$conditional->setNoFormatSet(true);

Changing the Cell Range

If you want to apply the same Conditional Rule/Style to several different areas on your spreadsheet, then you can do this using the setCellRange() method between calls to getConditional().

$wizardFactory = new Wizard();
/** @var Wizard\CellValue $wizard */
$wizard = $wizardFactory->newRule(Wizard::CELL_VALUE);

// Apply the wizard conditional to cell range A2:A10
$cellRange = 'A2:A10';
$conditionalStyles = [];
$wizard->between('$B1', Wizard::VALUE_TYPE_CELL)
    ->and('$C1', Wizard::VALUE_TYPE_CELL)
    ->setStyle($greenStyle);

$spreadsheet->getActiveSheet()
    ->getStyle($wizard->getCellRange())
    ->setConditionalStyles($conditionalStyles);

// Apply the same wizard conditional to cell range E2:E10
$cellRange = 'E2:E10';
$wizard->setCellRange($cellRange);
$conditionalStyles = [];
$wizard->between('$B1', Wizard::VALUE_TYPE_CELL)
    ->and('$C1', Wizard::VALUE_TYPE_CELL)
    ->setStyle($greenStyle);

$spreadsheet->getActiveSheet()
    ->getStyle($wizard->getCellRange())
    ->setConditionalStyles($conditionalStyles);

Because we use cell A1 as the baseline cell for relative references, the Wizard is able to handle the necessary adjustments for cell references and formulae to match the range of cells that it is being applied to when getConditional() is called, so it returns the correct expression.

Converting a Conditional to a Wizard

If you already have a Conditional object; you can create a Wizard from that Conditional to manipulate it using all the benefits of the Wizard before using that to create a new version of the Conditional:

$wizard = Wizard\CellValue::fromConditional($conditional, '$A$3:$E$8');
$wizard->greaterThan(12.5);

$newConditional = $wizard->getConditional();

This is ok if you know what type of Conditional you want to convert; but it will throw an Exception if the Conditional is not of an appropriate type (ie. not a cellIs).

If you don't know what type of Conditional it is, then it's better to use the Wizard Factory fromConditional() method instead; and then test what type of Wizard object is returned:

$wizard = Wizard::fromConditional($conditional, '$A$3:$E$8');
if ($wizard instanceof Wizard\CellValue) {
    $wizard->greaterThan(12.5);

    $newConditional = $wizard->getConditional();
}