Our example shows 7 people and their ages. We want to apply a custom format of all rows where the person's age is less than 33.
To do this we need to use conditional formatting with a custom formula.
1. Highlight the desired rows
Highlight the rows you wish to apply the conditional formatting to by clicking and dragging over the row numbers.
If you want to select individual or separate rows press and hold the Ctrl (Cmd ⌘ on Mac) key as you click each row.
In our example we want all the rows to be included so will highlight them all excluding the header row.
2. Click Format > Conditional formatting
From the navigation bar at top of the display click Format. Then click Conditional formatting.
3. Set the Format rule to “Custom formula is”
The conditional formatting rules will appear on the right hand side of the display.
Expand the dropdown menu in the Format rules section labelled Format cells if ..
By default this will display “is not empty”, change this to Custom formula is by selecting this option at the bottom of the dropdown menu:
4. Enter the custom formula =$Column and First_Row [Statement]
An input box will now appear, enter your custom formula:
=$Column and First_Row [Statement]
Column and First_Row: This will be the column the formula will search followed by the first row number of your selection. Note: It is important to match the first row number to the first row in your selected cell range.
[Statement]: This will be whichever statement you want to include in the formula. We will use a less than statement in our example.
In our example the custom formula will start with =$C4. As we want to apply the formatting to all rows where the age is less than 33 we need to include the following statement <33.
Our full custom formula for our example is:
5. Set the formatting style
In the Formatting style section set the desired format of all rows that meet the conditions. There are several formatting options including text and cell color and the option to bold, italic, underline or strikethrough the text.
For our example we set the format to underline the text and turn the cell color orange.
6. See the results by clicking done
At the bottom of the formatting rules section is the Done button. Click this and the conditional formatting will be applied and results will display if the conditions are met.
In our example all rows which contain the age number under 33 are now formatted to our conditions.
Tired of constantly monitoring your spreadsheets? Scale operations with Lido automations and free up time to focus on what really matters.