Why Learning Conditional Formatting Is A Must

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 you time.


What is Conditional Formatting?

Conditional formatting is a rule in Google Sheets that you can create using your own formula, and it helps you to format certain 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 are stipulating, and the kind of formatting you want. So if you want 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.


Locate Duplicates in Google Sheets With Conditional Formatting

One benefit of conditional formatting is that it helps you to locate duplicates in Google Sheets. 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 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 seeing a section called “Format Rules.” Click on that drop-down menu and you will get a number of options on what kind of rules to create. 

For this example, you would click on “Format cells if 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. You might want your “Yes” cells covered in green paint for example. Click the paint bucket, select green, and click Done. Now you know where your Yeses are.

You can do this with anything. 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 into the Value or formula field exactly what you want to find, and conditional formatting does that for you.


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 certain date as you will be calculating a special bonus for them this year. Don’t worry about the bonus part just yet. Here, you just want to know who they are, with the ones that have been with you the longest getting the largest 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 the values in the Minpoint, Midpoint, and Maxpoint of your values. Select the colors you want to use for this. Click Done. You should have a beautiful selection of employees and know at a glance who has been with you the longest.


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 rules of conditional formatting 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.

More on Geekweek


Sign in to comment with your TypePad, Twitter, Facebook, Google, Yahoo or OpenID.