By default, the if cause ( Format cells if…) is set to Cell is not empty, but click it and you'll pull down a whole slew of options. In our demo sheet, select cell A1, and click Format > Conditional Formatting. The trigger for a conditional formatting rule can look very different on a case-by-case basis.
For more information about how to use color to your advantage, check out Canva's guide to color meaning and symbolism. We associate different colors with different topics, so be sure to pick one that works with your specific spreadsheet. In the example above, the style is "background color to black."īe intentional about the color you choose. The rule will play out by changing the style of your cell in whatever capacity you select. What trigger event needs to happen in order for the rule to play out? In the example above, the if cause is "is empty." In the example above, the range is "cell B2." Range defines which cell or cells the rule should apply to. For example, you might say "If cell B2 is empty, then change that cell's background color to black."Īll rules will follow that same structure, so let's define the various elements: Every rule you set is an if/then statement. Google Sheets conditional formatting allows you to change the aspect of a cell-that is, a cell's background color or the style of the cell's text-based on rules you set. Copy the spreadsheet, and then play around with it as we proceed through the tutorial. Here, we'll walk through the basics of conditional formatting in Google Sheets. It also serves as a great way to track goals, giving you visual indications of how you're progressing against specific metrics. The FIND function works the same as SEARCH, but with the added behavior of case-sensitivity.Conditional formatting-which allows you to highlight cells that meet certain criteria-can help you better understand spreadsheets at a glance and create spreadsheets that are more human-readable by your whole team.
If you wish to search for text and discriminate between different cases (case-sensitive), use the FIND function. A search for the letters “AT” would find “AT”, “At”, “aT”, and “at”. NOTE: The SEARCH function is NOT case-sensitive.
If we receive an error response, we know the text “AT” does not exist in the text string. If we see any number as a response, we know “AT” exists in the text string. We can use this new information to determine if the text “AT” exists in the companion text strings. The “ #VALUE!” responses are the SEARCH function’s way of letting us know that the letters “AT” were not found in the search text. The next discovery was in the 5 th character position, and the last discovery was in the 4 th character position. The first SEARCH found the letters “AT” beginning in the 1 st character position of the text. We are returned a character position which the letters “AT” were discovered by the SEARCH function. To see if the search function works properly on its own, lets perform a simple test with the following formula. If omitted, the default start character position is 1 ( the first character on the left of the text.)