Conditional formatting is a powerful feature in Google sheets which allows you to highlight data that may be important so that you can spot interesting pieces of information quickly and easily.
Using the standard conditional formatting in Google sheets is quite limited, however if you're looking to have more control over how your conditional formatting works then custom formulas can help.
When adding your custom formula simply set "format cells if..." to "Custom formula is" a text box will appear below where you can type in your custom formula.
The formula that you type into this text box will need to be formatted just like any other formula you would type into a cell, it starts with a equals sign, and follows the correct syntax for any functions that you use.
Another thing to note is that in custom formulas you cannot reference cells in other sheets, however if you use the indirect function you can get around this by using this standard notation to reference another sheet ('SheetName'!A:A) .E.g Indirect('SheetName'!A4).
When writing custom formulas for conditional formatting you can also use some special functions which retrieve the row or column of the current item, the row() function will return the row number of the current item.
Similar to the row function there is also a column function which will return the current column, however it returns as a column index rather than a column letter so if you wanted to use the column() function you have to use a solution like this (https://stackoverflow.com/a/22129496) to convert the index to a letter.
Alternatively if you know the columns that you will be using you can just type in the column letters as a string in your formula.
In my formula I typed the column letters for the columns that I wanted and used the row function to get the current row, then with the indirect function got the value of the two cells and used the less than symbol to compare the two.
Using the standard conditional formatting in Google sheets is quite limited, however if you're looking to have more control over how your conditional formatting works then custom formulas can help.
When adding your custom formula simply set "format cells if..." to "Custom formula is" a text box will appear below where you can type in your custom formula.
The formula that you type into this text box will need to be formatted just like any other formula you would type into a cell, it starts with a equals sign, and follows the correct syntax for any functions that you use.
Common Functions in Custom Formulas
The indirect() function will get the value for a specified cell reference, so let's say in cell A4 there is a value of 40, by using the function indirect("A4") the value 40 is returned.Another thing to note is that in custom formulas you cannot reference cells in other sheets, however if you use the indirect function you can get around this by using this standard notation to reference another sheet ('SheetName'!A:A) .E.g Indirect('SheetName'!A4).
When writing custom formulas for conditional formatting you can also use some special functions which retrieve the row or column of the current item, the row() function will return the row number of the current item.
Similar to the row function there is also a column function which will return the current column, however it returns as a column index rather than a column letter so if you wanted to use the column() function you have to use a solution like this (https://stackoverflow.com/a/22129496) to convert the index to a letter.
Alternatively if you know the columns that you will be using you can just type in the column letters as a string in your formula.
Comparing cells for conditional formatting in Google sheets
In my use case I wanted to find and compare data from a relative cell in the same row, if the data in that cell was less than the data in the current cell then a red background would be applied.
To achieve this I could use the indirect() function mentioned above to get the value from the desired adjacent cell, as well as the current cell.
So to compare a value in one column with a value in another column, comparing a cell relative to another cell in Google sheets use the indirect function.
In my formula I typed the column letters for the columns that I wanted and used the row function to get the current row, then with the indirect function got the value of the two cells and used the less than symbol to compare the two.
=INDIRECT("$B"&ROW())<INDIRECT("$I"&ROW())
Was this helpful?
Comments
Post a Comment