Many people use Google Sheets to create data tables. Large tables created in Google’s spreadsheet tool may sometimes include duplicate number or text data inside columns and rows. Manually searching for duplicated data to eliminate inside big data tables may be time-consuming and frustrating.
We need a means to flag redundant data in a Google Sheets file to find it faster. However, there is no built-in capability in Sheets for highlighting duplication. Nonetheless, as shown in the ways below, you may flag duplicates in Google Sheets using custom formulae and a Power Tools add-on.
How to Highlight Duplicate Column and Row Data
Google Sheets, like other spreadsheet programs, supports conditional formatting. Conditional formatting is a spreadsheet formatting technique that allows users to apply formatting depending on certain cell circumstances. By using conditional formatting, you may, for example, make a range of spreadsheet cells glow red when they contain values less than zero.
We may use the conditional formatting tool in Sheets to apply a distinct color to all duplicate cells inside columns or rows. However, none of the conditional formatting rules in Sheets are adequate for highlighting duplicated data. As a result, you’ll need to create a formatting condition in Google Sheets based on a custom COUNTIF formula to identify duplicates, as seen below.
- To begin, create a blank Google Sheets spreadsheet and insert some fake data to test the conditional formatting.
- Then, in spreadsheet cells B2 through B5, insert the numbers 444, 333, 444, and 222, as seen in the picture below. In Sheets, there is one duplicated value (444) in the data range.
- By selecting B at the top of the column, you may select the whole B column.
- Then, in Sheets, go to Format > Conditional Formatting.
- Select the Custom formula is option from the Format cells if drop-down menu.
- In the Value or formula box, enter =COUNTIF(B:B,B1)>1.
- To open a palette, choose the Fill color option. Choose a color from the palette to highlight duplicates in Google Sheets.
- To conclude, press the Done button.
The duplicate 444 values in column B will now be highlighted with whichever color you picked, as seen in the screenshot below. Continue to input duplicate values in that column. All cells with duplicated numbers or text will be highlighted via conditional formatting.
The supplied custom COUNTIF formula indicates all values in column B that are repeated at least once. That algorithm may be used to highlight values that are replicated at least two, three, four, or more times. A =COUNTIF(B:B,B1)>2 will, for example, only indicate duplicated values in two or more cells (three including the original cell).
To alter the formula:
- Select the B column once again, and then launch the conditional formatting tool.
- Select the format rule.
- After changing the number at the end of the COUNTIF formula, click the Done button.
The same conditional formatting may be used to duplicates in spreadsheet rows. However, you’ll need to change the formula references to specify a row and the first cell inside it. For row two, for example, the formula would be =COUNTIF(2:2,A2)>1. The row in that formula is 2:2, and the first cell is A2.
How to Highlight Duplicated Data Across Multiple Columns
The COUNTIF algorithm above will detect duplicates inside single rows or columns. What if you want to highlight duplicates in numerous columns? The above formula might be used to each of the columns. You may, however, use a single COUNTIF function to several columns in this manner.
- In Sheets, create a new blank spreadsheet.
- In cells A1 and A2, enter the fake data 333 and 222.
- In B1 and B2, enter 444 and 333. The Sheets spreadsheet should then look like the one below.
- Choose the cell range where you input the numbers.
- Columns A and B are selected by holding down the Ctrl key.
- Select the Conditional formatting option by clicking File.
- In the formula text box, enter =COUNTIF($A$1:$C$10,A1)>1.
- Click the Fill color option to select a color for the formatting.
- Select Done to apply.
The duplicated 333 value is now noted in two distinct columns. All cells in columns A and B have the same conditional formatting. You may also choose to apply the same algorithm to other columns.
Power Tools is a free Google Sheets addon that highlights duplicate data in a different manner. This addon includes nine different spreadsheet utilities, including a DeDupe and Compare application. This is how you can use DeDupe and Compare to spot and eliminate duplicates in Google Sheets.
- Open the Power Tools webpage
- Click Install on that page.
- Select Continue to confirm.
- Then you must choose your Google Account.
- Allow Power Tools access by pressing the Allow button.
- To dismiss the installation message, click Done.
- Launch Google Sheets and a spreadsheet with some duplicate data.
- Click the Extensions menu.
- To open the extension, go to Power Tools > Start.
- In the Power Tools sidebar, choose Deduce & Compare.
- To bring up the tool displayed right below, choose Remove duplicate cells.
- On the right of the Select the range box, click the grid button.
- Then, to choose a cell range, move the mouse over it and click the OK button.
- To see the choices below, click Next.
- Depending on your preferences, you may pick Duplicates or Duplicates + 1 instances. The option Duplicates + 1 occurrences will highlight any cells that contain duplicated data.
- Click Next again.
- On step three, choose the Fill with color option.
- From the drop-down menu, choose a color to highlight the cells.
- To highlight all duplicates inside your specified cell range, click the Finish button.
On step three of the Find duplicate or unique cells wizard, you may choose Clear values instead of Fill with color. This will save you from having to manually delete highlighted duplicates. If you don’t want the initial data occurrence removed, choose the Duplicates option.
A Remove duplicate row wizard is also included in the Deduce & Compare tool. The similar wizard allows you to highlight duplicate rows inside spreadsheets. Alternatively, you may use the Remove duplicate row wizard to delete duplicated rows.
Find Duplicates in Google Sheets by Highlighting Them
Duplicate data in Google Sheets spreadsheets may be identified faster by highlighting it using conditional formatting or Power Tools. The Power Tools technique may be faster and easier to use for highlighting duplicated data.
Highlighting using the COUNTIF formula, on the other hand, is a more versatile solution with a greater range of conditional formatting possibilities.
You are looking for information, articles, knowledge about the topic How to Highlight Duplicates in Google Sheets on internet, you do not find the information you need! Here are the best content compiled and compiled by the achindutemple.org team, along with other related topics such as: Google.