If you’re working with a Table object that includes a totals row, or you’re using a SUBTOTAL() function to evaluate columns (such as Commission), the function will evaluate only the visible records. This feature always evaluates the entire record. You can’t use the advanced options to hide duplicate values in a column, but you probably wouldn’t want to anyway.
Figure E The copied data set doesn’t include the duplicate record in row 11. The resulting data set, shown in Figure E, isn’t hiding a record that record wasn’t copied.įigure D Identify the copy range. Identify the copy range by clicking inside Copy To Range and then clicking an anchor cell in the sheet ( Figure D) or by entering a range reference.In the resulting dialog, click Copy To Another Location.In the Sort & Filter group, click Advanced.You could copy a unique set to another location and work with it, independent of the source data set as follows: In this case, you’re hiding duplicates records so you can temporarily work with a unique data set. Figure C Excel hides the duplicate record in row 11. Figure B Choose the Unique Records Only option. As you can see in Figure C, row 11 is now hidden.įigure A You can hide duplicate records. Check the Unique Records Only option ( Figure B) and click OK.If you click a single cell before engaging the feature, Excel defaults to the contiguous content to determine this range. The resulting dialog will suggest the range. Click anywhere inside the data set and then click the Data tab.Now, let’s filter out the duplicate record as follows: If you look closely, you’ll also see that an entire record is duplicated the records in row 4 and 11 are the same. Some of the column values are duplicates, which is okay. Let’s begin by filtering the data set shown in Figure A in-place. You can filter the records in-place or you can copy a set of unique records to another range. However, you can use the feature’s more advanced options to hide a duplicate so you can work with a unique set of records. It won’t find duplicates–filtering doesn’t work that way. Sometimes, you might want to work around them.
SEE: Windows 10 Anniversary Update: Watch out for these nasty surprises Filtering out duplicatesĭeleting might be the primary reason for finding duplicates, but that won’t always be the case. Your definition of duplicate will depend on the business rule you’re applying. However, two records that apply the same assignment, date, and grade to the same student might be considered a duplicate and if so, should be accommodated (or deleted) to avoid incorrect analysis and reporting. For instance, a student database might have a date and grade column where lots of values occur more than once in both columns. The term can also refer to multiple columns or even all columns for a given record. In an Excel data set, duplicates in a single column are common and seldom incorrect, but they can be. Defining the term duplicateĪ duplicate can be confined to a single column any value that occurs more than once in that column is a duplicate.
You can work with your own data or download the demonstration. You can apply the same techniques to earlier Ribbon versions of Excel. I’m using Excel 2016 on a Windows 10 64-bit system.
How to create a fun Fly In effect in PowerPoint Learn advanced Excel skills for less than $35 Then, we’ll use Excel’s built-in features to find duplicates. First, we’ll define the term duplicate–it isn’t ambiguous, but context determines its meaning. In this article, I’ll show you easy ways to find duplicates by applying advanced filtering options and conditional formatting rules. Whether you’re finding duplicates in a single column or looking for duplicate records, Excel can do most of the work for you. However, duplicate records can skew reporting and analysis. This comprehensive review shows various ways to use advanced filtering options and conditional formatting to locate duplicates in Excel.ĭuplicate values aren’t bad. How to use built-in Excel features to find duplicates