Excel Yourself: List the unique entries in a column

Excel Yourself

When you review data tables in Excel for the first time, it is often useful to see what entries are in each field.

When you use the filter, Excel lists the unique entries in the field.

To extract those unique entries from the field you can use the Advanced Filter option.

In the Data ribbon tab there is an Advanced option. See Figure 1

Figure 1

Figure 1

In the Advanced Filter dialog shown in Figure 2, you need to tick the Unique record only option at the bottom of the dialog.

Click the option Copy to another location. Then change the List range reference to the column to be processed (column D is shown in Figure 2). 

Click inside the Copy to box and select a cell to paste the unique range (cell R1 in Figure 2). Click OK to create the unique list.

Figure 2

Figure 2

Figure 3 shows the result.

There are only 4 customer categories used in column D.

For more, check out the video.

Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services.



Figure 3

Figure 3


How-to articles, sample spreadsheets and more

Excel yourself

Combine annual and monthly data in Excel with these simple steps


Making data relationships work

Easy steps to make data Relationships work in Excel


Excel Yourself

Easy tips for matching salary to job class in Excel