Conditional formatting in Google Sheets is a way of helping you to analyze and identify trends in large amounts of data. As its name implies, it is all about the formatting, with conditional formatting allowing you to enter a few formulas into your spreadsheet resulting in highlighted data that gives you answers in seconds. Gone are the days where you spend hours using Google’s highlighting pen.
When you learn this formatting rule, you can highlight duplicates in Google Sheets, visualize your information, and find exactly what you are looking for when you need it. These are just a few of the benefits of learning conditional formatting.
Learn more about conditional formatting and how it can save your time.
1 What is Conditional Formatting?
Conditional formatting is a rule in Google Sheets that you can create using your formula, and it helps you format specific cells on your spreadsheet instantly. These rules allow you to change the appearances or characteristics of different cells or data on your Google Sheet.
This is done through if/then formulas. To make this formatting rule, you need a range of cells, the rule you stipulate, and the kind of formatting you want. So if you wish to all of the cells that are duplicates of the word “Red” in your sheet, you specify the range, specify “Red,” and then specify what you want all cells with the word “Red” to look like on your Google Sheet.
2 Locate Duplicates in Google Sheets With Conditional Formatting
One benefit of conditional formatting is that it helps you to locate duplicates in Google Sheets. For example, let’s say you have a list of names in a spreadsheet and want to find out which of those names resulted in the word “Yes” to a question such as: “Has this customer purchased within the last year?” You want to send a holiday card to each of these customers but don’t want to spend all day finding out where the “Yes” results are.
Select the range of cells that you are working with and highlight them. Go to the Format tab in your Google Sheets, click on Conditional formatting. Here you will see the range you have selected. Now you are visiting a section called “Format Rules.” Click on that drop-down menu, and you will get several options on what kind of rules to create.
For this example, you would click on “Format cells if the text contains,” and in the “value or formula” box right below that, you would type, “Yes.”
Now, select how you want that formatted on your spreadsheet. For example, you might wish to have your “Yes” cells covered in green paint. Then, click the paint bucket, select green, and click Done. Now you know where your Yeses are.
You can do this with anything. For example, let’s say you want to know where all of your duplicate names are or where all of the names that begin with “B” are. Just enter the Value or formula field precisely what you want to find, and conditional formatting does that for you.
3 Use Color Scales Wisely
When you are using conditional formatting, you can use a color scale to represent ranges of similar values.
So let’s say you want to highlight all of your employees that started before a specific date as you will be calculating a special bonus for them this year. Don’t worry about the bonus part just yet. Here, you want to know who they are, with the ones that have been with you the longest getting the most significant bonus.
You can use the color scale to indicate this. So, lighter colors have been here the longest, and darker colors signify your most recent employees.
Click on Format, go to Conditional formatting. Select the cells you are thinking about, say, G33:G177. Now pick your colors by clicking the Color Scale tab at the top of the Conditional formatting menu. Enter the range this applies to, and enter your values’s Minpoint, Midpoint, and Maxpoint. Select the colors you want to use for this. Click Done. You should have a beautiful selection of employees and know who has been with you the longest at a glance.
4 Learn Conditional Formatting
Learning conditional formatting is not only easy, but it can even be fun if you are accustomed to copying and pasting, and formatting spreadsheets all day. Use these simple conditional formatting rules to add a pop to your sheets and help you find duplicates in Google Sheets faster than just by searching with your cursor all day.
