Microsoft Excel is widely used around the world to compile data in the most efficient manner. However, there are situations when data is entered into the sheet multiple times. When you share a sheet, someone else may add the same data, or when you join tables, this data may be duplicated. When you have to eliminate these duplicates one by one, the process becomes more tedious and time-consuming. The more data there is, the more likely there are to be duplicates. But don’t worry; there are few quick techniques available to get rid of these duplicates in your Excel Sheet. These time-saving techniques do not necessitate a lot of work. Let’s learn the most effective methods for how to remove duplicates in MS Excel sheets quickly. You’ll also get to know when you need to remove duplicates and other important factors associated with it.
When Do We Need to Remove Duplicates in MS Excel?
♦ Duplicate values and rows are a common problem as the database increases. This may cause your calculations to be wrong, and it may cause others to question your competence. You can end yourself handing out a summary report with duplicate values or even mailing the identical letter to the same individual twice. To avoid these minor errors with major consequences, you’ll need to discover and remove duplicates.
Different Methods to Remove Duplicates in MS Excel
Let’s check out the various ways to remove duplicates in Microsoft Excel one after one:
Total Time: 20 minutes
Using Remove Duplicates Option on Data Tab
♦ ‘Data Tab’ is a built-in feature in Microsoft Excel. This tool assists you in deleting duplicate entries from your table. To use the Data Tab to remove duplicates in Excel sheets, follow these steps:-
✔ To begin, select a cell or a range of cells from which you want to remove duplicates
✔ Now, go to the Data Tab and click Remove Duplicates in the Data Tools group
✔ When a dialogue box appears, check all of the boxes and click on the Ok tab
✔ Excel will delete the duplicates and display a dialogue box
✔ It will display the number of duplicates that were removed from the datasheet
It will also display the total number of unique values.
Using the Advanced Filter Option
♦ You can use Microsoft Excel’s Advanced Filter to filter duplicate data and copy the unique values to a different location. Take a look at the steps you’ll need to take to use the Advanced Filter option to get rid of duplicates.
✔ Duplicates can be removed from a cell or a range of cells
✔ Go to the Data Tab and select the “Advanced Filter” option. The Advanced Filter window will appear
✔ To replicate the unique value in your excel sheet to different locations, select the “Copy to another location” option
✔ Select and tick the “List Range” option, then double-check that it is the range you specified
✔ Enter the range where you wish to duplicate your unique values in the “Copy to” option
✔ Make sure the “Unique Records Only” box is checked before clicking Ok tab
The cells that are unique will be copied to another cell.
Using Formulas to Remove Duplicates in Excel
♦ This method entails utilising an Excel formula to combine the columns and calculating the count. The duplicate values will then be filtered out (ones that have a count greater than 1).
✔ Let’s use the concatenation operator “&” to join the columns A, B, and C together. =A2&B2&C2 would be the Excel formula.
✔ This formula is typed into cell D2 and then copied down the rows
✔ To locate the duplicates in Column D, we’ll need a new column called “Count”
✔ As a result, on cell E2, we employ the COUNTIF function. The formula will be as follows:
✔ =COUNTIF($D$2:D2,D2)
✔ This formula counts the number of times each value in column D appears
✔ If Count is “1”, that means it has only appeared once and is unique
✔ It’s deemed a duplicate value if the value is “2” or higher
✔ Select the Filter option to apply a filter to the Count column
✔ It’s under the DATA tab, in the Sort & Filter section, Filter
✔ At the top of Column E, select the filter
✔ Select “1” to maintain only unique values and discard duplicates
✔ The duplicate values will be removed from the table when you click Ok
You can copy and paste the resulting unique records wherever you want.
Using the Power Query Tool to Remove the Duplicates in Excel
♦ Power Query is a data transformation capability in Microsoft Excel. It lets you import data from a variety of sources, clean it up, and change it. It is one of the most straightforward methods for removing duplicates from an excel sheet.
∗ Follow the steps below to get started:-
✔ Choose a cell and navigate to the Data Tab, then to the Get & Transform Data Section, then to the From Table/Range option
✔ You will be prompted to create a power query table in a dialogue box
✔ Select the value range and click Ok
✔ The editor windows for Power Query will emerge
✔ There will be two tabs displayed
✔ Duplicates can be removed from a table based on one or more columns or the entire table
✔ Do a right-click on a column header to delete duplicates based on one or more columns
✔ Remove numerous columns by holding down the CTRL key and deleting the duplicates
✔ Click the button in the top left corner of the data preview to delete duplicate records throughout the entire table
✔ After that, select the “Remove Duplicates” from the drop-down menu
When you select “Close & Load”, the data will be loaded into your spreadsheet.
Read More: How to Calculate Percentage in Microsoft -Excel
Click Here: How to Import the General Ledger in Excel Format to QuickBooks
➤ Conclusion
♦ Removing duplicates in MS Excel is pretty simple with the help of above discussed methods. Hopefully, the highlighted methods will assist in easy removal of the duplicates in your Microsoft Excel sheet. In case, if you come across any challenges, you can connect with our Team of Experts via QuickBooks Helpdesk Team.
⟴ FAQs:
How Do I Remove Shading/Highlight Duplicate Rows in Excel?
♦ The conditional formatting tool can be used to indicate duplicates. Take the following steps:
✔ Double-click the Excel document to open it. This will open the spreadsheet in Excel, allowing you to use conditional formatting to locate cells with duplicate values. This approach can be used to just see duplicate values without deleting them. From the Recently Opened excel Documents area of the Open tab, you may also open an existing excel document or a data containing headers excel file.
✔ Select the cell in the upper left corner of your data set. The cell will be selected at this step. If you’re working with data that has headers, leave the Column headers option (e.g., “date,” “time,” etc.) out of the selection process. If you just have one row selected, click the value in the top left corner. If you only have one column selected, click the value at the top end.
✔ Click the cell in the lower right corner of the data preview while holding down the Shift key on your keyboard. This will select all of the original data from the data lists on the top left and bottom right. Next, choose one full row by clicking on the single data cell on the far right. If you just want to choose one column, click the bottom-most data cell.
✔ Select Conditional Formatting from the drop-down menu. This option may be found in the Styles part of the main bar, where you can select Conditional Formatting from a drop-down menu. To make a Conditional Formatting display, you may need to first click the main button at the top of the Excel Microsoft window.
✔ Select “Highlight Cells Rules” from the drop-down menu. A popup will appear as a result of this step.
✔ Select the option for Duplicate Values. This option sits at the bottom of the box, and selecting it will choose duplicate values across the whole range of the selected data.
✔ We may utilise the sort and filter tool in Excel to remove duplicates using this way! Simply select your dataset (including the header this time), then click Sort and Filter in the Home Tab’s Editing Group.
✔ When you choose Filter, a filter button appears on the data set’s header.
✔ There isn’t a delete duplicates dialogue box here, but it’s quite similar. To access the sort and filter options, click the button. Choose Filter by colour and the colour you assigned to the duplicate cells.
✔ It will only display the rows that are that colour (which are also the duplicate rows), and you can simply pick and delete the cells using the keyboard shortcut Ctrl + – from here.
✔ Remove the Clear Filter from the Sort and Filter dialogue box.
✔ What’s left are the one-of-a-unique values.
What Happens If We Face Some Issues with Removing Duplicates From Excel From an Outline or Grouped Data Tab?
♦ In the Unique Values or Unique Records Filter, you can’t delete duplicate values from an outline or filter group data (containing subtotals). The outline and subtotals must be removed to remove duplicates.
What is the Difference Between Filtering Unique Values and Removing Duplicate Values?
♦ There is a significant difference: When filtering out unique values, only duplicate values are temporarily hidden. Removing duplicate values, on the other hand, means that duplicates will be permanently removed from the Microsoft Excel spreadsheet.
A duplicate value is one in which all of the data in at least the first row matches all of the values in the following row. The items that occur in all of the cells, not the base value recorded in that single cell, are used to compare duplicate values.
Read More Information:
QuickBooks 2016 or 2017 Unable to Export to Excel
