google sheets highlight duplicates in a column

The Conditional format rules will use the Apply to range setting, moving down each cell in the range, applying the formula. In our formula, we’ve provided cell A2 as the criterion, but passed it as a relative cell reference (no $ symbol). When setting the range parameter in the COUNTIF function call, we need to use absolute cell referencing (we’ll see in a second why), hence we prepend both the column letter and row number with the dollar symbol, $A$2:$A$25.ĬOUNTIF then uses the criterion parameter, checking it against all the cells in the range, incrementing a counter every time a match (i.e. In our example, the range of cells used is from A2 to A25 inclusive. Otherwise, criterion may be a string containing a number (which also checks for equality), or a number prefixed with any of the following operators: =, >, >=, 1ĬOUNTIF takes two parameters, the range ( $A$2:$A$25), and the criterion ( A2), and what we are looking for is a count that is greater than 1. If a number is provided, each cell in range is checked for equality with criterion. If range contains numbers to check against, criterion may be either a string or a number.Each cell in range is then checked against criterion for equality (or match, if wildcards are used). A string criterion must be enclosed in quotation marks. To match an actual question mark or asterisk, prefix the character with the tilde (~) character (i.e. criterion can contain wildcards including ? to match any single character or * to match zero or more contiguous characters. If range contains text to check against, criterion must be a string.criterion – The pattern or test to apply to range.range – The range that is tested against criterion.Returns a conditional count across a range. The following function definition is taken from the Google sheets documentation. Our formula is going to use the COUNTIF function. It is here where we define the formula that will find any duplicates in our email addresses. This adds a new field to the Formula rules section, Value or formula. Since none of the predefined rules help in finding duplicates, we need to use a formula, by selecting the last option, Custom formula is. The next section, Format rules, is where we select what rules will be used on our selected range of cells from the dropdown menu. We can manually change this if we wanted to include more cells in the range, but as we’ll see in a moment, this range has to coincide with our custom formula. The first thing to notice is the Apply to range section, which by default has the range of cells we highlighted previously. This will open the Conditional format rules pane on the far right hand side. Then, we select Conditional formatting, available from Google sheet’s Format menu option. In the screenshot above, we’ve highlighted the range of cells A2 to A25. How we make this selection impacts on how we structure our custom formula. We begin by selecting the range of cells on which we want to test. In our first example, we have a list of email addresses and we want to highlight only those rows that constitute duplicates. You can also define a custom formula, and this is what we will be using. There are a number of predefined rules that can be used to test against text, dates and numbers. Google sheets offers a conditional formatting option which allows the user to create a rules based formatting strategy based on one or more criteria. Our examples will include a small dataset but the principle is the same regardless of whether there are 10 rows or 1000 rows. We’ll start with a simple, single column example and work on to more complex situations. We’ll be taking a look at ways Google sheets gives you for doing this using Conditional formatting. Highlighting duplicates is handy when you are comparing data to see where there’s a match, or going through trying to filter out double entries.

Google sheets highlight duplicates in a column