Spreadsheets are one of the most powerful tools that are used across business units. They not only help us keep our business heads updated with the latest numbers on sales, marketing, and revenue but also help us communicate messages across to other units through visual graphs and charts. However, for all of this to be true you need to master the world of Google Sheets and Ms-Excel and yes, it is a world on its own. We hope that you found our first blog post in our Google Sheets Cheat Series on How To VLOOKUP with wild card useful. Getting right into our second post for this series, let’s get started with conditional formatting.
Conditional formatting is an extremely useful technique for mastering cell formatting based on whether or not certain conditions are fulfilled. An example would be to highlight cells based on the cell value or the data within the cell. Or even better highlighting the entire row to stand out based on the value within a single cell. In the example below, we want to highlight the row if the continent is “Asia” in Column A.
Step 1: Highlight the data range that you want to select for the formatting.
Step 2: Select Format and then click on Conditional Formatting
Step 3: Click on the drop down from “Cell is not empty” and select “Custom formula is”. This will add a box where you can insert the custom formula.
Step 4: Enter the formula use the $ sign to lock your column as reference.
The core concept is that we lock the column you want to base your conditional formatting on and want to use it as a reference by adding a dollar sign $. You can find the workbook for this example here.
Conditional Formatting Based On A Threshold Value
Similarly, by selecting custom formula in conditional formatting you can select a threshold value, above or below which you would like to highlight rows which fall in the category relative to the reference threshold value.
In the example below, All students who received scores less than 50% have been highlighted by setting custom formula is:
You can find the workbook for this example here.
We hope you found this article useful. Use our guide Free Keyboard Shortcuts: Spreadsheet Secrets and watch yourself transform from a Google Sheets newbie to a ninja in no time! Save your copy and add the shortcuts that you discover to quickly refer back anytime you need. Stay subscribed to Wyzebulb Blog for more blogs and articles on Automation, Marketing, and Productivity!