电子表格^{(Spreadsheet)}只不过是一种以行和列的形式排列数据的文档。几乎每个业务组织都使用电子表格^{(Spreadsheet)}来维护其数据记录并对该数据执行操作。甚至学校和学院也使用电子表格软件来维护他们的数据库。在电子表格软件方面，Microsoft Excel和Google表格是许多人使用的顶级软件。如今，更多的用户选择Google 表格^{(Google Sheets)}而不是Microsoft Excel，因为它将电子表格存储在他们的云存储^{(Cloud Storage)}上，即Google Drive可以从任何位置访问。唯一的必要条件是您的计算机应连接到Internet。Google 表格^{(Google Sheets)}的另一个优点是您可以在 PC 上的浏览​​器窗口中使用它。

在维护数据条目时，许多用户面临的常见问题之一是重复或重复条目。例如，假设您有从调查中收集的人员详细信息。当您使用电子表格软件（例如Google 表格^{(Google Sheets)}）列出它们时，可能会出现重复记录。也就是说，一个人可能不止一次填写了调查表，因此Google 表格^{(Google Sheets)}会列出该条目两次。这种重复的条目在涉及到业务时更加麻烦。想象^(Imagine)如果在记录中多次输入现金交易。当您使用该数据计算总费用时，这将是一个问题。为避免这种情况，应确保电子表格中没有重复记录。如何做到这一点？好吧，在本指南中，您将讨论在^(Well)Google 表格^{(Google Sheets)}中删除重复项的 6 种不同方法。来吧^(Come)，不做进一步介绍，让我们来看看这个话题。

如何删除 Google 表格中的重复项？^{(How to Remove Duplicates in Google Sheets?)}

在维护数据记录的情况下，重复记录确实很麻烦。但您不必担心，因为您可以轻松地从Google 表格^{(Google Sheets)}电子表格中删除重复条目。让我们看看您可以在Google 表格^{(Google Sheets)}中删除重复项的一些方法。

方法 1：使用删除重复选项^{(Method 1: Using the Remove Duplicates Option)}

Google 表格^{(Google Sheets)}有一个内置选项可以删除重复的条目（重复条目）。要使用该选项，请按照下图进行操作。

1. 例如，看看这个（见下面的截图）。在这里您可以看到“Ajit”记录被输入了两次。这是重复记录。

2. 要删除重复条目，请选择或突出显示行和列。^{( select or highlight the rows and columns.)}

3. 现在点击标有数据^(Data)的菜单选项。向下滚动，然后单击“删除重复^{(Remove duplicates )}项”选项。

4. 会出现一个弹出框，询问要分析哪些列。根据您的需要选择选项，然后单击“删除重复项^{(Remove duplicates )}”按钮。

5.所有重复记录将被消除，唯一的元素将被保留。Google 表格^{(Google Sheets)}会提示您删除的重复记录的数量^{(number of duplicate records that were eliminated)}。

6. 在我们的例子中，只删除了一个重复条目（Ajit）。您可以看到Google 表格^{(Google Sheets)}已删除重复条目（请参阅下面的屏幕截图）。

方法 2：使用公式删除重复项^{(Method 2: Remove Duplicates with Formulae)}

公式 1：独一无二^{(Formula 1: UNIQUE)}

Google 表格^{(Google Sheets)}有一个名为UNIQUE的公式，它保留唯一的记录，并会从您的电子表格中删除所有重复的条目。

例如：=UNIQUE(A2:B7)

1. 这将检查指定单元格范围 (A2:B7)^{(specified range of cells (A2:B7))}中的重复条目。

2.单击电子表格中的任何空白单元格^{(Click on any empty cell on your spreadsheet)}并输入上述公式。Google 表格^{(Google Sheets)}会突出显示您指定的单元格范围。

3. 谷歌表格^{(Google Sheets)}将列出您输入公式的唯一记录。然后，您可以用唯一记录替换旧数据。^{(You can then replace the old data with the unique records.)}

公式 2：COUNTIF^{(Formula 2: COUNTIF)}

您可以使用此公式突出显示电子表格中的所有重复条目。

1. 例如：考虑以下包含一个重复条目的屏幕截图。

2. 在上面的截图中，在 C2 单元格中，让我们输入公式：=COUNTIF(A$2:A2, A2)>1

3. 现在，一旦按下Enter键，结果将显示为 FALSE。

4. 移动鼠标指针并将其放在所选单元格底部的小方块上。^{( small square)}现在您将看到一个加号而不是鼠标光标。单击^(Click)并按住该框，然后将其向上拖动到要查找重复条目的单元格。谷歌^(Google)表格会自动将公式复制到剩余的单元格中^{(automatically copy the formula to the remaining cells)}。

5. Google Sheet会自动在重复条目前添加“ TRUE ”。

注意^(NOTE)：在这种情况下，我们指定为 >1（大于 1）。因此，在多次找到条目的地方，此条件将导致TRUE 。在所有其他地方，结果都是FALSE。

方法 3：使用条件格式删除重复条目^{(Method 3: Remove Duplicate Entries with Conditional Formatting)}

您还可以使用条件格式来消除Google 表格^{(Google Sheets)}中的重复记录。

1.首先^(First)，选择您要对其执行条件格式化的数据集。然后，从菜单中^(Menu)选择格式^{(Format )}并向下滚动，然后选择条件格式。^{(Conditional formatting.)}

2. 单击Format cells if...下拉框，然后选择Custom Formula选项。

3. 输入公式为=COUNTIF(A$2:A2, A2)>1

注意：^(Note:)您需要根据您的Google Sheet更改行列数据。

4. 此公式将过滤 A 列中的记录。

5. 单击完成^(Done)按钮。如果 A 列包含任何重复记录^{(duplicate records)}，Google 表格将突出显示重复的条目（重复项）。^{( Google Sheets will highlight the repeated entries (duplicates).)}

6. 现在您可以轻松删除这些重复记录。

方法 4：使用数据透视表删除重复记录^{(Method 4: Remove Duplicate Records with Pivot Tables)}

由于数据透视表使用快速且灵活，您可以使用它来查找和消除Google Sheet中的重复记录。

首先，您必须突出显示Google Sheet中的数据。接下来^(Next)，创建一个数据透视表并再次突出显示您的数据。要使用您的数据集创建数据透视表，请导航到Google 表格^{(Google Sheet)}菜单下的数据^{(Data )}，然后单击数据透视表^{(Pivot table)}选项。系统将提示您一个框，询问是在现有工作表还是新工作表中创建数据透视表。选择一个合适的选项并继续。

您的数据透视表将被创建。从右侧的面板中，选择行附近的^(Rows)添加^{(Add )}按钮以添加相应的行。在值附近，选择添加列以检查值是否重复。您的数据透视表将列出值及其计数（即值在您的工作表中出现的次数）。您可以使用它来检查Google Sheet中的条目是否重复。如果计数超过 1，则意味着该条目在您的电子表格中重复多次。

方法 5：使用 Apps 脚本^{(Method 5: Using Apps Script)}

另一种从文档中消除重复的好方法是使用Apps 脚本^{(Apps Script)}。下面给出的是从电子表格中删除重复条目的应用程序脚本：

/**
* remove duplicate rows from Google Sheets data range
*/
function removeDupRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  // change the row number of your header row
  var startRow = 7;
  // get the data
  var range = sheet.getRange(startRow,1,sheet.getLastRow(),sheet.getLastColumn()).getValues();
  // remove duplicates with helper function
  var dedupRange = arrayUnique(range);
  Logger.log(dedupRange);
  // check if duplicate sheet exists already, if not create new one
  if (ss.getSheetByName('Sheet1 Duplicates Removed')) {
    // case when dedup sheet already exists
    var dedupSheet = ss.getSheetByName('Sheet1 Duplicates Removed');
    var lastRow = Math.max(dedupSheet.getLastRow(),1);
    var lastColumn = Math.max(dedupSheet.getLastColumn(),1);
    // clear out any previous de-duplicate data
    dedupSheet.getRange(1,1,dedupSheet.getLastRow(),dedupSheet.getLastColumn()).clear();
    // replace with new de-duplicated data
    dedupSheet.getRange(1,1,dedupRange.length,sheet.getLastColumn()).setValues(dedupRange);
  }
  else {
    // case when there is no dedup sheet
    var dedupSheet = ss.insertSheet('Sheet1 Duplicates Removed',0);
    dedupSheet.getRange(1,1,dedupRange.length,dedupRange[0].length).setValues(dedupRange);
  }
  // make the de-duplicate sheet the active one
  dedupSheet.activate();
}
/**
* helper function returns a unique array
*/
function arrayUnique(arr) {
  var tmp = [];
  // filter out duplicates
  return arr.filter(function(item, index){
    // convert row arrays to strings for comparison
    var stringItem = item.toString();
    // push string items into temporary arrays
    tmp.push(stringItem);
    // only return the first occurrence of the strings
    return tmp.indexOf(stringItem) >= index;
  });
}
You can also use the below function to add a custom menu to Google Sheets to remove duplicates so that you can easily use it.
/**
* add a menu to run a function from Sheet
*/
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Remove duplicates')
  .addItem('Highlight duplicate rows','highlightDupRows')
  .addItem('Remove duplicate rows','removeDupRows')
  .addToUi();
}

方法 6：使用插件删除 Google 表格中的重复项^{(Method 6: Use Add-on to Remove Duplicates in Google Sheets)}

使用附加组件消除电子表格中的重复条目可能是有益的。几个这样的扩展被证明是有帮助的。一个这样的附加程序是Ablebits的附加程序，名为“ Remove Duplicates ”。

1. 打开 Google 表格，然后从附加组件^{(Add-ons )}菜单中单击获取附加组件^{(Get add-ons )}选项。

2. 选择启动^{(Launch )}图标（在屏幕截图中突出显示）启动G-Suite 市场^{(G-Suite Marketplace)}。

3. 现在搜索您需要的插件^(Add-on)并安装它。

4. 如果您愿意，请浏览附加组件的描述，然后单击安装^{( click on the Install)}选项。

接受安装插件所需的权限。您可能必须使用您的Google帐户凭据登录。安装插件后，您可以轻松地从Google 表格^{(Google Sheets)}中删除重复项。

受到推崇的：^{(Recommended:)}

• 如何在 Microsoft Word 中删除分节符^{(How to Delete a Section Break in Microsoft Word)}
• 在 Word 中插入平方根符号的 5 种方法^{(5 Ways to Insert a Square Root Symbol in Word)}
• 如何在 Google 表格中快速换行？^{(How to Quickly Wrap Text in Google Sheets?)}

我们希望这些信息对您有所帮助，并且您能够轻松地从 Google 表格中删除重复条目。^{(easily remove duplicate entries from Google Sheets.)}如果您有任何建议或问题，请使用评论部分提出问题。

6 Ways to Remove Duplicates In Google Sheets

A Spreadsheet is nothing but a document that arranges dаta in the form of rows and columns. Spreadsheets are υsed by almost every bυsiness organization to maintain its datа records and perform operations on that data. Even schools and colleges use spreadsheet software to mаintain their databasе. When it comes to spreadsheet software, Microsoft Excel and Google sheets are the top-ranking software that many people use. Nowadays, more users choose Google Sheets over Microsoft Excel as it stores the Spreadsheets on their Cloud Storage, i.e. Google Drive which can be accessed from any location. The only requisite is that your computer should be connected to the Internet. Another great thing about Google Sheets is that you can use it from your browser window on your PC.

When it comes to maintaining data entries, one of the common issues faced by many users are duplicates or duplicate entries. For example, imagine that you have the details of people collected from a survey. When you list them using your spreadsheet software such as Google Sheets, there is a possibility of duplicate records. That is, one person might have filled the survey more than once, and hence Google Sheets would list the entry twice. Such duplicate entries are more troublesome when it comes to businesses. Imagine if a cash transaction is entered in the records more than once. When you calculate the total expenses with that data, it would be an issue. To avoid such situations, one should ensure that there are no duplicate records in the spreadsheet. How to achieve this? Well, in this guide, you will discuss 6 different ways to remove duplicates in Google Sheets. Come on, without further introduction, let us take a peek into the topic.

How to Remove Duplicates in Google Sheets?

Duplicate records are really troublesome in the case of maintaining data records. But you need not worry as you can easily remove duplicate entries from your Google Sheets spreadsheet. Let us see some ways in which you can get rid of duplicates in Google Sheets.

Method 1: Using the Remove Duplicates Option

Google Sheets has a built-in option to remove entries that are repetitive (duplicate entries). To use that option, follow the illustration below.

1. For example, take a look at this (see screenshot below). Here you can see that the record “Ajit” is entered two times. This is a duplicate record.

2. To remove the duplicate entry, select or highlight the rows and columns.

3. Now click on the menu option labelled Data. Scroll down then click on the Remove duplicates option.

4. A pop-up box will come up, asking which columns to analyze. Choose the options as per your needs and then click on the Remove duplicates button.

5. All duplicate records would be eliminated, and unique elements would remain. Google Sheets will prompt you with the number of duplicate records that were eliminated.

6. In our case, only one duplicate entry was removed (Ajit). You can see that Google Sheets has removed the duplicate entry (refer to the screenshot that follows).

Method 2: Remove Duplicates with Formulae

Formula 1: UNIQUE

Google Sheets has a formula named UNIQUE that retains unique records and would eliminate all duplicate entries from your spreadsheet.

For Example: =UNIQUE(A2:B7)

1. This would check for duplicate entries in the specified range of cells (A2:B7).

2. Click on any empty cell on your spreadsheet and enter the above formula. Google Sheets would highlight the range of cells that you specify.

3. Google Sheets will list the unique records where you typed the formula. You can then replace the old data with the unique records.

Formula 2: COUNTIF

You can use this formula to highlight all duplicate entries in your spreadsheet.

1. For Example: Consider the following screenshot that contains one duplicate entry.

2. In the above screenshot, at cell C2, let’s enter the formula as, =COUNTIF(A$2:A2, A2)>1

3. Now, once the Enter key is pressed, it will show the result as FALSE.

4. Move the mouse pointer and place it over the small square at the bottom portion of the selected cell. Now you will see a plus symbol instead of your mouse cursor. Click and hold on that box, and then drag it up to the cell where you want to find the duplicate entries. Google sheets would automatically copy the formula to the remaining cells.

5. Google Sheet will automatically add “TRUE” in front of duplicate entry.

NOTE: In this condition, we have specified as >1 (greater than 1). So, this condition would result TRUE in places where an entry is found more than once. In all other places, the result is FALSE.

Method 3: Remove Duplicate Entries with Conditional Formatting

You can also make use of conditional formatting to eliminate duplicate records from Google Sheets.

1. First, select the data set on which you would like to perform conditional formatting. Then, from the Menu select Format and scroll down then choose Conditional formatting.

2. Click on the Format cells if… drop-down box, and choose the Custom Formula option.

3. Enter the formula as =COUNTIF(A$2:A2, A2)>1

Note: You need to change the row & column data according to your Google Sheet.

4. This formula would filter records from column A.

5. Click on the Done button. If column A contains any duplicate records, Google Sheets will highlight the repeated entries (duplicates).

6. Now you can easily delete these duplicate records.

Method 4: Remove Duplicate Records with Pivot Tables

As pivot tables are fast-to-use and flexible, you can use it to find & eliminate duplicate records from your Google Sheet.

First, you will have to highlight the data in Google Sheet. Next, create a pivot table and again highlight your data. To create a pivot table with your dataset, navigate to the Data under the Google Sheet menu and click on the Pivot table option. You will be prompted with a box asking whether to create the pivot table in the existing sheet or a new sheet. Choose a suitable option and proceed.

Your pivot table will be created. From the panel on the right, choose the Add button near Rows to add the respective rows. Near the values, choose to Add a column to check for duplication of values. Your pivot table would list the values with their counts (i.e. number of times the value occurs in your sheet). You can use this to check for the duplication of entries in Google Sheet. If the count is more than one, that means the entry is repeated more than once in your spreadsheet.

Method 5: Using Apps Script

Another great way to eliminate duplicated from your document is by using the Apps Script. Given below is the apps-script to get rid of duplicate entries from your spreadsheet:

/**
* remove duplicate rows from Google Sheets data range
*/
function removeDupRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  // change the row number of your header row
  var startRow = 7;
  // get the data
  var range = sheet.getRange(startRow,1,sheet.getLastRow(),sheet.getLastColumn()).getValues();
  // remove duplicates with helper function
  var dedupRange = arrayUnique(range);
  Logger.log(dedupRange);
  // check if duplicate sheet exists already, if not create new one
  if (ss.getSheetByName('Sheet1 Duplicates Removed')) {
    // case when dedup sheet already exists
    var dedupSheet = ss.getSheetByName('Sheet1 Duplicates Removed');
    var lastRow = Math.max(dedupSheet.getLastRow(),1);
    var lastColumn = Math.max(dedupSheet.getLastColumn(),1);
    // clear out any previous de-duplicate data
    dedupSheet.getRange(1,1,dedupSheet.getLastRow(),dedupSheet.getLastColumn()).clear();
    // replace with new de-duplicated data
    dedupSheet.getRange(1,1,dedupRange.length,sheet.getLastColumn()).setValues(dedupRange);
  }
  else {
    // case when there is no dedup sheet
    var dedupSheet = ss.insertSheet('Sheet1 Duplicates Removed',0);
    dedupSheet.getRange(1,1,dedupRange.length,dedupRange[0].length).setValues(dedupRange);
  }
  // make the de-duplicate sheet the active one
  dedupSheet.activate();
}
/**
* helper function returns a unique array
*/
function arrayUnique(arr) {
  var tmp = [];
  // filter out duplicates
  return arr.filter(function(item, index){
    // convert row arrays to strings for comparison
    var stringItem = item.toString();
    // push string items into temporary arrays
    tmp.push(stringItem);
    // only return the first occurrence of the strings
    return tmp.indexOf(stringItem) >= index;
  });
}
You can also use the below function to add a custom menu to Google Sheets to remove duplicates so that you can easily use it.
/**
* add a menu to run a function from Sheet
*/
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Remove duplicates')
  .addItem('Highlight duplicate rows','highlightDupRows')
  .addItem('Remove duplicate rows','removeDupRows')
  .addToUi();
}

Method 6: Use Add-on to Remove Duplicates in Google Sheets

Using an add-on to eliminate duplicate entries from your spreadsheet can be beneficial. Several such extensions turn out to be helpful. One such add-on program is the add on by Ablebits named “Remove Duplicates”.

1. Open Google Sheets, then from Add-ons menu click on the Get add-ons option.

2. Choose the Launch icon (highlighted in the screenshot) to launch the G-Suite Marketplace.

3. Now search for the Add-on you need and install it.

4. Go through the description of the add-on if you wish and then click on the Install option.

Accept the necessary permissions to install the add-on. You may have to sign in with your Google account credentials. After you have installed the add-on, you can easily remove duplicates from Google Sheets.

Recommended:

• How to Delete a Section Break in Microsoft Word
• 5 Ways to Insert a Square Root Symbol in Word
• How to Quickly Wrap Text in Google Sheets?

We hope this information was helpful and you were able to easily remove duplicate entries from Google Sheets. If you have any suggestions or questions in your mind, make use of the comments section to ask them.

Related posts

• 2 Ways更改Google Docs中的Margins

• 4 Ways在Google Docs中旋转图像

• 3 Ways更新Google Play Store [Force Update]

• 3 Ways共享Wi-Fi Access，无需透露Password

• 3 Ways删除Slideshow From Any Website

• 3 Ways更改Spotify Profile Picture（Quick Guide）

• 如何在 Snapchat 上进行民意调查？创建民意调查的 3 种方法

• 如何在Google上关闭SafeSearch

• 7 Ways至Outbox至Gmail的Fix Email Stuck

• 如何在Google Docs中添加页面

• 使用Someone Else分享您的Google Calendar

• 修复 Netflix 错误代码 M7121-1331-P7 的 6 种方法

• 如何在Google Meet上更改Your Name

• Gmail or Google Account Automatically的注销（带图片）

• 在 iPhone 上下载 Facebook 视频的 3 种方法

• Delete Google Search History & Everything它知道你！

• 如何在Google Maps查看Traffic

• 10 Ways至Fix Google Photos不是Backing

• Google Chrome Crashes？ 8简单Ways修复它！

• 在游戏中检查 FPS（每秒帧数）的 4 种方法