Do you want to format a cell in a Google Sheets spreadsheet based on the contents of another cell? Here's how to do it the easy way. In google sheets, conditional formatting is based on the idea of automatically updating some cell value if it meets a certain condition. In this article, I will show you how you can quickly and effectively apply conditional formatting to any google sheet that you have made.
Step #1: Highlight the Cell Range
First, highlight the cell range that you want to format and open the "Format" menu (learn all about the format menu here).
Make sure that you avoid highlighting the header row, because that can lead to confusing the conditional format software.
Step #2: Select "Conditional Formatting"
In the Drop-Down Menu, select the option titled "Conditional Formatting".
Step #3: Rules Menu
Follow these steps:
- Verify Range: Make sure that your range is properly stated; it could be a cause of error later on.
- Select "Custom Formula is": Under "Format Cells If", there is a massive Drop-Down Menu of possible conditional statements. Select "Custom Formula is"; that will let you add your own custom statements.
- State Your Function: The reference cell in the equation should be from wherever the reference column begins (the header being excluded). In this case, my reference cell is C3 because the reference data begins at C3. Also, I want to highlight what students have a score higher than 89. Therefore, my equation will read as "=C3>89".
- Style your format. You can customize the color and layout of your formatted cells as you wish. I chose to highlight the names in green, as I want to know what students got an A in the class.
Step #4 (optional): Add More Rules
You can also add more format rules if you would like. As you can see, I assigned a color code to the grades:
- Red is a failing grade (=C3<70)
- Orange is a C (=C3>69)
- Yellow is a B (=C3>79)
- Green is an A (=C3>89)
Conditional formatting is a great tool that will help you quickly make sense of any Data Table