For example, say your worksheet lists attendee registration information for your company’s recent training session, and you’d like to know how many states (or countries) were represented at your training. A quick Excel filter will provide a list of every unique entry.
First, you’ll need to ensure that all locations used the same spelling or abbreviation. When collecting data, ensure that attendees select from an exclusive list of options rather than typing in their own answers for their home state.
Once your data is ready, select the range of cells, including the column heading. In our example, the heading might be “State.” Select the Data tab from the main ribbon, and in the Sort And Filter group, select Advanced. In the resulting Advanced Filter dialog box, select the Copy To Another Location check box. Click in the Copy To box, and then select a range of cells into which you’ll copy the results (the Advanced Filter dialog box automatically collapses as you perform this step). Click on the Expand Dialog button to return to the Advanced Filter dialog box. Select the Unique Records Only check box, then click OK. Each state name is listed in the location you selected. Click in the first empty cell below your new filtered list of states, and type =ROWS(I2: I29), where I2:I29 represents the entire range of your filtered list. This ROWS function returns the number of unique states.