When working with Google Sheets, if you don’t know formulas such as conditional formatting, you can spend a lot of time using manual labor highlighting, deleting, and copying and pasting. The magic of Google Sheets is that you don’t have to do this, and hundreds of formulas exist that can help you out. You don’t need to learn them all, just the ones you are most likely to use, like conditional format.
Conditional formatting is a hidden feature of Google Sheets that will change everything in your workday regardless if your job consists of HR, payroll or in the marketing department. Here, you can change any cell based on the condition that you determine in your formulas in less time. Have all of your January reports highlighted in your annual calendar, highlight every customer’s name that is “Smith,” and so on. Highlighting duplicates is the most common use of conditional formatting. Learn more about how to use that feature here.
How to Use It
To use conditional formatting rules in Google Sheets, you will open up your spreadsheet and select the cell or cells to which you want to apply this rule. Click the Format tab, and then click or tap Conditional Formatting.
A toolbar opens where you can create a rule. So let’s say you want to highlight in red all of the employees with “Overtime” on their schedule. For “Format Cells if” you will choose what condition you want in this rule. You’ll then see “Formatting Style” and will see how the cell will appear when that condition of “Overtime” is met. Here you can choose a color from the Color Scale, in “Preview.” Then click Done. To remove it, simply point at the rule and hit “Remove.”
Single Column: How to Highlight Duplicates
When you want to highlight duplicates in a single column, select the column you are looking for duplicates in. So, let’s say you want to find any duplicates of the word “Overtime” in a column. Select the column, click or tap “Format,” then “Conditional Formatting.”
Here you will see the list of rules you can create when highlighting duplicates. For this one, you might select “Text contains” and enter the criterion of “Overtime.” You can then click on Default text, then “Formatting style,” and select a preset color that will highlight every entry in that column where the word “Overtime” appears. Click “Done.”
In another world where you didn’t use this rule, you would have to manually highlight every employee that had overtime that month, which would take much longer.
Let’s do this with multiple columns now.
Highlighting Duplicates in Many Columns in Google Sheets
Let’s say you now have a few columns where you want to highlight duplicates. For this example, we will say you want to know how many employees worked Overtime in November and December.
Start by selecting both columns. To add columns, hold the Ctrl key, or hold Shift and click on the first and last cell of each column. When your columns are selected, click on the Format tab, then on Conditional Formatting.
You can now edit the rule you just made above on “Overtime” alone, or “Add another rule” found at the bottom of this menu. You will see the same menu and have the opportunity now to add another rule, such as “Text contains November” or “Text contains…” whatever condition you want to highlight.
Here, there might be data or numbers that you are highlighting. The Overtime example here is just that. When you want to highlight a number of things across more than one column, you simply select the columns and use the same conditional formatting rules to highlight duplicate cells.
Start Using Conditional Formatting
When working with Google Sheets, listen to that little voice inside you that tells you that there is an easier way of doing this. Conditional formatting to highlight duplicates in Google Sheets cells takes seconds to learn and seconds to do. This is a hidden feature that even many business owners don’t know about.
When you tap into the power that comes when you highlight duplicates in Google Sheets, you will save time and money for yourself and your company. Look like a superstar at work today, and start using conditional formatting, one of the most valuable tools that helps everybody understand what your Google spreadsheet is saying.
