Unique Records in Excel
Excel has a way to show you individual unique values that are in a table or list. This feature can be helpful when you have repeated values, and you want to view the specific unique values. For example, if you have a table of dates when sales were recorded, those dates are probably repeated.
Or if you want a list of all your sales reps for the last five years, a sales file is a good place to look, but most of the names are likely to appear more than just once.
To get a list of unique individual values, follow these steps:
- Select the cells in your existing table or list, or, if no other data is directly adjacent to the data, just select any cell in your table or list.
- Go to the Ribbon’s Data tab and choose Advanced from the Sort & Filter group.
The Advanced Filter dialog box appears.
- Select the Unique Records Only check box.
- Click OK.
Excel hides any rows with duplicate records. If you don’t care for that behavior, use the Copy to Another Location option so your original data is left as is — but be careful if you do so. Suppose you choose to copy to another location, and you specify, say, cell F1. If there’s already data in column F, it can be overwritten by the new filtered list — and you can’t undo it. You’ll find a good alternative to this approach, using FREQUENCY.