Top 10 # Xem Nhiều Nhất Xóa Duplicate Trong Excel Mới Nhất 3/2023 # Top Like

What to Know

This article explains how to copy a sheet in Excel using various methods. Instructions apply to Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel for Microsoft 365.

How to Duplicate a Sheet in Excel by Dragging

The most simple and straightforward way to copy a sheet to another location within the workbook is to drag it.

Select the worksheet you want to duplicate.

Press and hold the Ctrl key.

Drag the selected tab and drop it where you want to create a copy.

How to Duplicate a Sheet in Excel From the Worksheet Tab

How to Duplicate a Sheet in Excel From the Ribbon

The Format section of ribbon in Excel provides an additional way to duplicate a worksheet.

How to Copy a Sheet in Excel to a Different Workbook

The methods used to copy a worksheet to another spot in the same workbook also apply when duplicating a sheet to another Excel file, although there are a few additional steps for each method.

How to Copy a Sheet to a Different Workbook by Dragging

Both workbooks must be open and visible in order to copy a sheet from one Excel file to another. The easiest way to do this is to use Microsoft’s split screen options to make the workbooks appear side-by-side on the page.

Open the Excel file containing the worksheet you want to duplicate and the Excel file to which you want to copy the first sheet.

Select the worksheet you want to duplicate.

Press and hold the Ctrl key.

Drag the selected tab and drop it into the second Excel workbook.

How to Copy a Sheet to a Different Workbook From the Worksheet Tab

Send a duplicate sheet to another workbook by making changes in the Move or Copy dialog box.

Select the target file under To Book.

To place a copy into a new workbook, select New Book.

How to Copy a Sheet to a Different Workbook From the Ribbon

Create a duplicate sheet in another workbook by making changes in the Move or Copy dialog box from the Ribbon.

Open the worksheet you want to copy.

Select the target file under To Book.

To place a copy into a new workbook, select New Book.

How to Copy Multiple Sheets at Once in Excel

Duplicating multiple sheets can be accomplished using any of the methods listed, including copying multiple sheets to a different workbook in Excel. The key is to select all of the worksheets you want to make copies of before you begin duplicating them elsewhere.

Select all of the sheets you want to copy.

To select adjacent sheets, select the first sheet tab, press and hold the Shift key, and select on the last tab.

To select non-adjacent sheets, select first sheet tab, press and hold the Ctrl key, and select each additional tab you want to duplicate.

To drag the duplicates to another location, select any of the highlighted tabs, press the Ctrl key and drag the tabs to the desired position.

How to Move a Sheet in Excel

If you do not want to duplicate a worksheet in another location or another Excel file but would rather relocate an Excel worksheet, moving it is very similar to making a copy and you have several options.

Select the tab of the worksheet and simply drag it to the location to which you want to move it.

Select Format on the Home tab, select Move or Copy Sheet and then choose where you want to create duplicates of the worksheet.

Thanks for letting us know!

Other Not enough details Hard to understand

Remove Duplicates là công cụ rất hữu ích trong Excel trong việc gộp dữ liệu trùng lặp. Công cụ này được sử dụng nhiều trong việc tạo ra một danh sách (list) các đối tượng thu gọn từ một bảng nhật ký thu – chi, nhật ký bán hàng, sổ nhật ký chung trong kế toán, danh sách khách hàng trong kinh doanh, theo dõi các khoản thanh toán trong kinh doanh, theo dõi ngày nghỉ trong tháng với hành chính nhân sự, tổng hợp dữ liệu với những người làm thống kê, banking, …

Cách sử dụng Remove Duplicates

Đây là 1 phần của báo cáo bán hàng xuất ra từ phần mềm kế toán Fast. Accounting. Chúng ta nhìn vào đây có thể thấy, một số thành phần được lặp lại tại cột Ten_kh (Tên khách); Ma_VT (Mã hàng) và Ten_VT (Tên hàng). Tất nhiên là đơn giá cũng sẽ lặp lại theo mã hàng và tên hàng.

Như vậy, nếu chúng ta cần phải tạo 1 danh sách khách hàng để tính toán tổng doanh số hoặc tạo ra một danh sách hàng hóa để phục vụ việc kiểm tra số lượng hàng bán hoặc doanh số bán hàng theo mặt hàng. (Trong trường hợp chúng ta phải sử dụng công thức tính toán thủ công để khi vùng dữ liệu thay đổi, báo cáo thủ công của chúng ta sẽ thay đổi theo. Nếu chỉ cần một báo cáo cố định, chúng ta sử dụng Consolidate – sẽ được hướng dẫn ở bài sau – sẽ nhanh hơn rất nhiều).

Giả sử bài toán yêu cầu chúng ta tạo một danh sách khách hàng. Đầu tiên chúng ta chọn vùng chứa dữ liệu chúng ta cần phải loại bỏ các giá trị trùng lặp, kể cả ô trống (blank). Ở đây chúng ta chọn cột mã khách và tên khách như hình bên dưới và copy và paste ra một vùng khác/sheet khác.

Tại sao chọn 2 cột? Chúng ta có thể chọn nhiều cột hơn. Cũng có thể chọn 1 cột. Cách cách chọn này đều được. Quan trọng là vùng được chọn phải chứa dữ liệu chúng ta cần gộp các giá trị trùng lặp Ở đây chúng ta chọn 2 cột như vậy để thấy được hiệu ứng của Remove Duplicate một cách đầy đủ.

Tại sao phải copy và paste ra vùng/sheet khác? Vì chúng ta không muốn thay đổi cấu trúc của bảng tính gốc, chỉ muốn làm một danh sách để tiện cho việc báo cáo nên ta sẽ đặt danh sách đó ở 1 sheet khác.

Tại Sheet mới, ta sẽ thấy như hình sau:

Ta chọn vùng dữ liệu A1:B29, sau đó chọn thẻ Data trên thanh Ribbon và chọn Remove Duplicates như hình dưới.

Và kết quả là cửa sổ Remove Duplicates hiện ra như sau:

Nếu các bạn chọn cả bảng tính để thực hiện thao tác Remove Duplicates, cửa sổ hiện ra như trên.

Ở cửa sổ trên, nếu các bạn lựa chọn My data has headers như trong hình vuông màu xanh thì có nghĩa rằng, Excel sẽ lấy ô đầu tiên trong cột dữ liệu được lựa chọn để làm đầu cột (headers)

Và kết quả sẽ được như hình sau:

Chúng ta thấy, cả mã khách hàng và tên khách hàng đã được loại bỏ giá trị trùng lặp.

Chúng ta cũng có một lưu ý khi sử dụng công cụ này như sau:

Lưu ý:

Nếu Excel phát hiện còn có vùng chưa được chọn (chưa được bôi đen select), nó sẽ hiện ra cửa sổ hỏi bạn có muốn mở rộng vùng thao tác hay không – như hình sau:

Nếu bạn chọn Expand the selection thì Excel đưa bạn trở về cửa sổ Remove Duplicates như hình trên để bạn lựa chọn thêm cột cần thao tác. Nếu chọn Continue with the current selection – tức là tiếp tục thao tác trên lựa chọn hiện tại – thì cửa sổ Remove Duplicates hiện ra với một cột thao tác như sau:

Và kết quả thu được sẽ là: chỉ 1 cột được loại bỏ phần trùng lặp, các cột khác không đổi. (Xem hình dưới)

The tutorial explains how to search for duplicates in Excel. You will learn a few formulas to identify duplicate values or find duplicate rows with or without first occurrences. You will also learn how to count instances of each duplicate record individually and find the total number of dupes in a column, how to filter out duplicates, and more.

And today, I’d like to share a few quick and effective methods to identify duplicates in a single list. These solutions work in all versions of Excel 365, Excel 2019, Excel 2016, Excel 2013 and lower.

How to identify duplicates in Excel

The easiest way to detect duplicates in Excel is using the COUNTIF function. Depending on whether you want to find duplicate values with or without first occurrences, there’s going to be a slight variation in the formula as shown in the following examples.

How to find duplicate records including 1st occurrences

Supposing you have a list of items in column A that you want to check for duplicates. These can be invoices, product Id’s, names or any other data.

Here’s a formula to find duplicates in Excel including first occurrences (where A2 is the topmost cell):

Input the above formula in B2, then select B2 and drag the fill handle to copy the formula down to other cells:

As you can see in the screenshot above, the formula returns TRUE for duplicate values and FALSE for unique values.

Note. If you need to find duplicates in a range of cells rather than in an entire column, remember to lock that range with the $ sign. For example, to search for duplicates in cells A2:A8, use this formula:

For a duplicate formula to return something more meaningful than the Boolean values of TRUE and FALSE, enclose it in the IF function and type any labels you want for duplicate and unique values:

In case, you want an Excel formula to find duplicates only, replace “Unique” with an empty string (“”) like this:

The formula will return “Duplicates” for duplicate records, and a blank cell for unique records:

How to search for duplicates in Excel without 1st occurrences

In case you plan to filter or remove duplicates after finding them, using the above formula is not safe because it marks all identical records as duplicates. And if you want to keep the unique values in your list, then you cannot delete all duplicate records, you need to only delete the 2nd and all subsequent instances.

So, let’s modify our Excel duplicate formula by using absolute and relative cell references where appropriate:

As you can see in the following screenshot, this formula does not identity the first occurrence of “Apples” as duplicate:

How to find case-sensitive duplicates in Excel

In situations when you need to identify exact duplicates including the text case, use this generic array formula (entered by pressing Ctrl + Shift + Enter):

IF( SUM(( –EXACT(range, uppermost _cell)))<=1, “”, “Duplicate”)

At the heart of the formula, you use the EXACT function to compare the target cell with each cell in the specified range exactly. The result of this operation is an array of TRUE (match) and FALSE (not match), which is coerced to an array of 1’s and 0’s by the unary operator (–). After that, the SUM function adds up the numbers, and if the sum is greater than 1, the IF function reports a “Duplicate”.

For our sample dataset, the formula goes as follows:

=IF(SUM((--EXACT($A$2:$A$8,A2)))<=1,"","Duplicate")

As shown in the screenshot below, it treats lowercase and uppercase as different characters (APPLES is not identified as a duplicate):

How to find duplicate rows in Excel

If your aim is to dedupe a table consisting of several columns, then you need a formula that can check each column and identify only absolute duplicate rows, i.e. rows that have completely equal values in all columns.

Let’s consider the following example. Supposing, you have order numbers in column A, dates in column B, and ordered items in column C, and you want to find duplicate rows with the same order number, date and item. For this, we are going to create a duplicate formula based on the COUNTIFS function that allows checking multiple criteria at a time:

To search for duplicate rows with 1st occurrences, use this formula:

The following screenshot demonstrates that the formula really locates only the rows that have identical values in all 3 columns. For example, row 8 has the same order number and date as rows 2 and 5, but a different item in column C, and therefore it is not marked as duplicate row:

To show duplicate rows without 1st occurrences, make a little adjustment to the above formula:

How to count duplicates in Excel

If you want to know the exact number of identical records in your Excel sheet, use one of the following formulas to count duplicates.

Count instances of each duplicate record individually

When you have a column with duplicated values, you may often need to know how many duplicates are there for each of those values.

=COUNTIF($A$2:$A$8, $A2)

As demonstrated in the following screenshot, the formula counts the occurrences of each item: “Apples” occurs 3 times, “Green bananas” – 2 times, “Bananas” and “Oranges” only once.

If you want to identify 1st, 2nd, 3rd, etc. occurrences of each item, use the following formula:

=COUNTIF($A$2:$A2, $A2)

In a similar manner, you can count the occurrences of duplicated rows. The only difference is that you will need to use the COUNTIFS function instead of COUNTIF. For example:

=COUNTIFS($A$2:$A$8, $A2, $B$2:$B$8, $B2)

Once the duplicate values are counted, you can hide unique values and only view duplicates, or vice versa. To do this, apply Excel’s auto-filter as demonstrated in the following example: How to filter out duplicates in Excel.

Count the total number of duplicates in a column(s)

The easiest way to count duplicates in a column is to employ any of the formulas we used to identify duplicates in Excel (with or without first occurrences). And then you can count duplicate values by using the following COUNTIF formula:

=COUNTIF(range, "duplicate")

Where “duplicate” is the label you used in the formula that locates duplicates.

In this example, our duplicate formula takes the following shape:

=COUNTIF(B2:B8, "duplicate")

=ROWS($A$2:$A$8)-SUM(IF( COUNTIF($A$2:$A$8,$A$2:$A$8)=1,1,0))

Because it’s an array formula, remember to press Ctrl + Shift + Enter to complete it. Also, please keep in mind that this formula counts all duplicate records, including first occurrences:

To find the total number of duplicate rows, embed the COUNTIFS function instead of COUNTIF in the above formula, and specify all of the columns you want to check for duplicates. For example, to count duplicate rows based on columns A and B, enter the following formula in your Excel sheet:

=ROWS($A$2:$A$8)-SUM(IF( COUNTIFS($A$2:$A$8,$A$2:$A$8, $B$2:$B$8,$B$2:$B$8)=1,1,0))

How to filter duplicates in Excel

For easier data analysis, you may want to filter your data to only display duplicates. In other situations, you may need the opposite – hide duplicates and view unique records. Below you will find solutions for both scenarios.

How to show and hide duplicates in Excel

Tip. To have filtering enabled automatically, convert your data to a fully-functional Excel table. Just select all data and press the

Ctrl + T

shortcut.

And now, you can sort duplicates by the key column to group them for easier analysis. In this example, we can sort duplicate rows by the Order number column:

How to filter duplicates by their occurrences

If you want to show 2nd, 3rd, or Nth occurrences of duplicate values, use the formula to count duplicate instances we discussed earlier:

=COUNTIF($A$2:$A2, $A2)

Then apply filtering to your table and select only the occurrence(s) you want to view. For example, you can filter the 2nd occurrences like in the following screenshot:

In a similar manner, you can show 2nd, 3rd and all subsequent duplicate occurrences. Just type the required number in the box next to “is greater than”.

Highlight, select, clear, delete, copy or move duplicates

After you’ve filtered duplicates like demonstrated above, you have a variety of choices to deal with them.

How to select duplicates in Excel

To select duplicate records without column headers, select the first (upper-left) cell, and press Ctrl + Shift + End to extend the selection to the last cell.

Tip. In most cases, the above shortcuts work fine and select filtered (visible) rows only. In some rare cases, mostly on very large workbooks, both visible and invisible cells may get selected. To fix this, use one of the above shortcuts first, and then press

Alt + ;

to select only visible cells, ignoring hidden rows.

How to clear or remove duplicates in Excel

How to highlight duplicates in Excel

Another way to highlight duplicates in Excel is using a built-in conditional formatting rule for duplicates, or creating a custom rule specially tailored for your sheet. Experienced Excel users won’t have any problem with creating such a rule based on the formulas we used to check duplicates in Excel. If you are not very comfortable with Excel formulas or rules yet, you will find the detailed steps in this tutorial: How to highlight duplicates in Excel.

How to copy or move duplicates to another sheet

To copy duplicates, select them, press Ctrl + C, then open another sheet (a new or existing one), select the upper-left cell of the range where you want to copy the duplicates, and press Ctrl + V to paste them.

To move duplicates to another sheet, perform the same steps with the only difference that you press Ctrl + X (cut) instead of Ctrl + C (copy).

This is how you can identify duplicates in Excel using functions and built-in features. To better understand the formulas discussed in this tutorial, feel free to download Identify Duplicates Sample Workbook.

Duplicate Remover – fast and efficient way to locate duplicates in Excel

Now that you know how to use duplicate formulas in Excel, let me demonstrate you another quick, efficient and formula-free way – Duplicate Remover for Excel.

This all-in-one tool can search for duplicate or unique values in a single column or compare two columns. It can find, select and highlight duplicate records or entire duplicate rows, remove found dupes, copy or move them to another sheet. I think an example of practical use is worth many words, so let’s get to it.

How to find duplicate rows in Excel in 2 quick steps

To test the capabilities of our Duplicate Remover add-in, I’ve created a table with a few hundred rows that looks like follows:

As you see, the table has a few columns. The first 3 columns contain the most relevant information, so we are going to search for duplicate rows based solely on the data in columns A – C. To find duplicate records in these columns, just do the following:

The smart add-in will pick up the entire table and ask you to specify the following two things:

Select the columns to check for duplicates (in this example, these are the Order no., Order date and Item columns).

Choose an action to perform on duplicates. Because our purpose is to identify duplicate rows, I’ve selected the Add a status column

Apart from adding a status column, an array of other options are available to you:

Delete duplicates

Color (highlight) duplicates

Select duplicates

Copy duplicates to a new worksheet

Move duplicates to a new worksheet

As you can see in the below screenshot, all of the rows that have identical values in the first 3 columns have been located (first occurrences are not identified as duplicates).

If you want more options to dedupe your worksheets, use the Duplicate Remover wizard that can find duplicates with or without first occurrences as well as unique values. The detailed steps follow below.

Duplicate Remover wizard – more options to search for duplicates in Excel

Depending on a particular sheet you are working with, you may or may not want to treat the first instances of identical records as duplicates. One possible solution is using a different formula for each scenario, as we discussed in How to identify duplicates in Excel. If you are looking for a fast, accurate and formula-free method, try the Duplicate Remover wizard:

On the next step, you are presented with the 4 options to check duplicates in your Excel sheet:

Duplicates without 1st occurrences

Duplicates with 1st occurrences

Unique values

Unique values and 1st duplicate occurrences

For this example, let’s go with the second option, i.e. Duplicates + 1st occurrences:

Now, select the columns where you want to check duplicates. Like in the previous example, we are selecting the first 3 columns:

Finally, choose an action you want to perform on duplicates. As is the case with the Dedupe Table tool, the Duplicate Remover wizard can identify, select, highlight, delete, copy or move duplicates.

It only takes a fraction of a second for the Duplicate Remover wizard to check hundreds of rows, and deliver the following result:

No formulas, no stress, no errors – always swift and impeccable results 🙂

Identify Duplicates – formula examples (.xlsx file) Ultimate Suite – trial version (.zip file)

You may also be interested in

You can remove duplicates in Excel in a few steps; duplicates can create problems when you’re dealing with data.

Excel provides an easy tool that removes duplicate values for you, but it can only remove exact duplicates.

Visit Business Insider’s homepage for more stories.

10 Things in Tech: Get the latest tech trends & innovations

Loading Something is loading.

Email address

Duplicate values can be a problem, especially if you’re dealing with a large dataset. 

What looks like a list of 100 items might only be 70 once you remove the items that are on the list more than once. 

Excel comes with a built-in tool that makes cleaning your data quick and easy. Here’s how it works.

Check out the products mentioned in this article:

Laura McCamy/Business Insider

5. Excel will only remove exact duplicates. In this example, the second Square is misspelled as Squarre, so after duplicates are removed, both spellings of Square remain.

6. You can also highlight a range of cells and remove duplicates.

7. When you dedupe a range, only duplicates within the range are removed. In this case, Square is removed, but the duplicates of Circle and Triangle remain.

You can also remove duplicates in more than one column. 

Uncheck the box next to Column A.

Laura McCamy/Business Insider

6. In addition to the three rows that had the same shape/color combination, this time you also removed the value “Rectangle” from Column A because it was the second row that included “blue” in Column B. Now there are no duplicate shapes or colors. 

“Remove Duplicates” is a powerful tool. Use it with care. As noted above, you may miss duplicates if they aren’t exact or you could delete values you want to keep. However, if you make a mistake, you can quickly restore your data by holding “Control” + “Z” on your PC or “command” + “Z” on your Mac keyboard to undo the operation.

Related coverage from How To Do Everything: Tech: