How Can You Color Every Duplicate a Different Color in Excel
Sentinel Video – Highlight Duplicates in Google Sheets
With a picayune know-how, you tin can use Google Sheets to highlight duplicates. You lot tin can customize how Sheets represents a duplicate match so it makes sense to a person looking at the data.
For example, Google Sheets can highlight an entire row of data if in that location's a duplicate value in a specific column.
Alternatively, Sheets can just highlight any cell with a value that appears more than once on a spreadsheet folio.
The post-obit guide breaks downward how to highlight duplicates in Google Sheets (explained with multiple examples)
Highlight Duplicates in Google Sheets (in a Single Column)
Suppose you have a dataset as shown below and you want to highlight all the duplicate values in column A.
To highlight duplicates cells with conditional formatting, follow these steps.
- Select the range. In the example example, we desire to use A2:A15 (image i, box).
- Open the conditional formatting window past opening the "Format" drop carte and choosing "Conditional formatting" from the list.
- Click on the "+ Add another rule" option in the conditional formatting pane that opens. In case yous already have conditional formatting applied, this option will be at the bottom (beneath the existing dominion).
- Set "Format cells if…" to "Custom formula is" and enter the indistinguishable cheque formula "=countif(Range,Criteria)>1" and hit "Done" to return a "true/false" response. In the example of our example, we're using the formula "=countif($A$2:$A$15,A2)>1" to check for duplicates.
Google Sheets is now highlighting duplicates in our grocery list.
Note that using this method highlights all the instances of the indistinguishable cell. And so if an particular repeats twice or thrice, all the cells that have this particular/text will exist highlighted.
The COUNTIF function used in provisional formatting counts how many times a cell text string appears in the list. If information technology's more than than one, the formula returns Truthful and those cells are highlighted.
Note: Use the "Formatting mode" section to change the highlighting colour and font style. This is very helpful if you need to highlight more than than ane color at a time.
Highlighting and Find Duplicates in Google Sheets
When working with larger spreadsheets with many columns, you will likely desire to highlight the unabridged cavalcade when there's a duplicate instead of just the jail cell.
This makes information technology easier to find duplicates when the duplicated prison cell isn't on the screen.
This process differs in ii means:
- The range needs to include all rows (A2:A15 is now A2:F15).
- The criteria section needs to utilize an accented value for the column (A2 is now $A2).
Let's use our grocery list from before, just at present nosotros've added where you detect each production in the store as a new column (equally shown below).
Follow these steps to highlight the entire row for duplicate cells in i column:
- Select the range of all columns and rows you desire to highlight instead of just the column y'all're searching for matches. In the example, select A2:B15 instead of A2:A15
- Open the "Conditional formatting" feature and check the new range (paradigm two, height box).
- In the "Conditional formatting" tab, set up the "Format rules" to "Custom formula is…" and enter the duplicate-finding formula with the criteria adjusted for an absolute column. This means adding "$" before "A2" in our instance. So "=countif($A$2:$A$15,A2)>1" is now "=countif($A$2:$A$15,$A2)>1".
- Hit "Done" to apply the new formatting (image 2, bottom box).
Now nosotros've highlighted the entire row range for columns with duplicates.
Highlight Duplicates Instances Only
As I mentioned earlier, when you use conditional formatting to highlight duplicates in Google Sheets, it will highlight all the instances of duplicate cells.
Only what if you desire to highlight only the indistinguishable instances (and not the first time information technology appears in the list).
You lot can adjust the formula and so Google Sheets will simply highlight the second or later on instance of a duplicate past making a tweak to the formula.
Suppose you have the dataset as shown beneath and you desire to highlight only the cells that have the duplicate detail.
We need to brand a tweak to our old "=countif($A$two:$A$xv,$A2)>i" formula (this is the one nosotros used in the previous section).
- Adjust the ending range value from $A$fifteen to $A2. Now that we've set the column to absolute in the catastrophe, merely now the row, each row will but expect at rows above itself for duplicates.
- Our new formula will look like this: "=countif($A$2:$A2,$A2)>1"
In our instance, this alter to the formula (arrow) now only highlights the 2nd instance in which our duplicates appear (box).
Remove all duplicates shortcut
Google Sheets has a handy tool for removing duplicates instead of highlighting them.
We'll utilise our grocery list with production location data from before to testify how information technology works.
The post-obit steps demonstrate this tool:
- Select the cell range you want to analyze.
- Open the "Data" menu and select "Remove Duplicates".
- From the "Remove duplicates" popular-up, check "Information has header row" if the information has this characteristic in your selection and so choose the columns you desire to cheque for duplicate values from the checklist. In our example, nosotros only want to compare column A values.
- Choose "Remove duplicates" to apply the change.
The spreadsheet now only has rows without indistinguishable cells from our choice.
Highlight Duplicates with Added criteria
Google Sheets tin check for provisional highlighting with additional criteria. You can gear up the programme to highlight merely rows with duplicate values in multiple columns or merely duplicates with specific values.
The formula needs to use the "*" (and) operator to apply both conditions. An instance formula would look like this:
=(countif(Range,Criteria)>one) * (New Status) )
For case, we can have our grocery list examples from before just highlight duplicate products that are fruits or vegetables. We don't care about products that appear twice in the bakery or any of the aisles.
Our spreadsheet now features lists all the different fruits and vegetables bachelor at the store:
In our case, we want to highlight the values in columns C and D if a row value appears twice in column C and at all in columns A and B.
Our formula construction will look like this:
- Kickoff with our old formula, at present modified to check column C instead of A: =countif($C$two:$C$15,$C2)>one
- Wrap the formula in parenthesis to add the second condition: =(countif($C$2:$C$fifteen,$C2)>1))
- Add the "and/*" operator and the second set of parentheses for the 2nd condition:
- =(countif($C$2:$C$15,$C2)>1)*())
- Our second status checks if the match appears in columns A and B, so it volition look a little different. Instead of checking for "greater than 1" it will bank check for "greater than 0" and look similar this: countif($A$2:$B$xv,$C2)>0
- Our new formula will expect like this: =(countif($C$two:$C$15,$C2)>i)*(countif($A$ii:$B$15,$C2)>0)
Applying the new range and new formula to our data set will highlight all the fruits and vegetables that are duplicates. It will not highlight the bagels which appear twice because they aren't in the fruits or vegetable columns.
Some tips when highlighting duplicates in Google Sheets
Here are some things to proceed in mind when working on highlighting duplicates in Google Sheets:
Create a Unique Cells Listing (Instead of higlighting duplicates)
Sometimes going through the procedure of highlighting information to detect duplicates isn't the fastest way to find out what you're looking for.
If you just desire to come up with a list of unique values to visualize how many duplicates you're working with, the "=unique()" formula might exist right for you lot.
To use this feature: enter "=unique(range beginning: range end)" in the cell you want to be at the top of your list.
In this grocery list case, we are inbound "=unique(A2:A15)" at cell B2. When applied, it populates all the cells below information technology necessary to come up with a unique values list.
Trim Whitespace to Curate Data
It'southward common to encounter extra spaces before and subsequently information when you're importing information into Google Sheets from other sources.
For example, an email might come in as both "me@mysite.tld" and "me@mysite.tld " with a infinite at the end of the 2d version. These are duplicates but won't show upward equally such because that empty space confuses Google Sheets.
You can trim whitespace from your information with the post-obit:
- Select the data you desire to trim (left bßox).
- Open the "Information" drop menu (arrow).
- Choose "Trim whitespace" from the list.
If the selected data has whatsoever invisible whitespaces, Google Sheets volition remove them. The data won't look whatsoever dissimilar to you lot, but it is now different for data analysis.
Highlighting duplicates in Google Sheets is a valuable tool for making information easier for people to empathize.
Sheets offers an immense range of customization options for highlighting duplicates with multiple weather.
I hope this tutorial helped you amend understand how to utilise this useful characteristic!
Sumit
Google Sheets and Microsoft Excel Expert.
Source: https://productivityspot.com/highlight-duplicates-google-sheets/
0 Response to "How Can You Color Every Duplicate a Different Color in Excel"
Post a Comment