Colouring
Colouring
The Colouring() function returns the colours of an array of highlighted cells.
Related functions: Sieve
Syntax
Colouring (Range)
| Range | The input range of cells in Excel |
| Colour | A specific colour to return as opposed to all colours. |
| Include | When ‘FALSE’, the colour specified by the second parameter is excluded, and all other colours are returned. The default is TRUE. |
Note:
When only the first parameter is provided (the input range), then the colour ‘WHITE’ is excluded by default.
Note:
Input parameters can consist of Quintessence functions. This includes functions that return ranges in cases where a parameter value can be a range.
Examples
Example 1. Return the colours of highlighted cells within a block of cells
Example 2. Return only a specific cell colour found within a block of cells
Example 3. Exclude a specific colour and return all other colours
Example 4. Use Colouring in conjunction with Sieve
Example 1. Return the colour of highlighted cells within a block of cells
The input range consists of a block of highlighted cells. The Colouring() function outputs the colours of any highlighted cells.
| Range | Colouring | |||
| =Colouring(A2:C5) | ||||
| Range | Output | |||||
| Color [Red] | ||||||
| Color [Red] | Color [Black] | |||||
| Color [Red] | ||||||
| Color [Black] | Color [Red] | |||||
Note:
The cell containing the function does not output a colour. Depending on the use case, the input range can be prefixed with a blank column, and the function extended to include the blank column, such that all colours are retrieved.
Example 2. Return only a specific cell colour found within a block of cells
The input range consists of a block of highlighted cells. The Colouring() function outputs those cells of a specific colour – in this example, ‘Black’.
| Range | Colouring | |||
| =Colouring(A2:C5,”Black”) | ||||
| Range | Output | |||||
| Color [Black] | ||||||
| Color [Black] | ||||||
Example 3. Exclude a specific colour and return all other colours
The input range consists of a block of highlighted cells. The Colouring() function outputs the colours of all cells excluding the specified colour – in this example, ‘Red’.
| Range | Colouring | |||
| =Colouring(A2:C5,”Red”,FALSE) | ||||
| Range | Output | |||||
| Color [White] | ||||||
| Color [Yellow] | Color [Black] | |||||
| Color [White] | Color [Yellow] | |||||
| Color [Black] | Color [Yellow] | |||||
Example 4. Use Colouring in conjunction with Sieve
Some use cases require that highlighted cells are excluded from analysis. These can be removed from the output using a combination of Colouring and Sieve.
| Input Range | Locate the Red cells | Mask the red cells | ||||
| 11 | 22 | 33 | =Colouring(A2:C4,”red”) | =Sieve(A2:C4,E2:G4) | ||
| 44 | 55 | 66 | ||||
| 77 | 88 | 99 | ||||
| Input Range | Location of Red cells | Red cells are masked | ||||||||
| 11 | 22 | 33 | Color [Red] | 11 | 22 | |||||
| 44 | 55 | 66 | Color [Red] | Color [Red] | 66 | |||||
| 77 | 88 | 99 | Color [Red] | 77 | 88 | |||||