Microsoft Office Tutorials and References
In Depth Information
In the List Range field, type the reference of the cell range you want to examine for
unique values, select the Unique Records Only check box, and then click OK to have
Excel display the row that contains the first occurrence of each value in the column.
Important Excel treats the first cell in the data range as a header cell, so it doesn’t consider the
cell as it builds the list of unique values. Be sure to include the header cell in your data range!
In this exercise, you’ll select random rows from a list of exceptions to identify package
delivery misadventures to investigate, create an AGGREGATE formula to summarize the
visible cells in a filtered worksheet, and find the unique values in one column of data.
SET UP You need the ForFollowUp_start workbook located in your Chapter05 practice
file folder to complete this exercise. Open the ForFollowUp_start workbook, and save it
as ForFollowUp . Then follow the steps.
1. Select cells G3:G27 .
The average of the values in the selected cells, the number of cells selected, and
the total of the values in the selected cells appear in the AutoCalculate area of the
2. In cell J3 , enter the formula =AGGREGATE(1,1,G3:G27) .
The value $15.76 appears in cell J3.
3. On the Data tab, in the Sort & Filter group, click Advanced .
The Advanced Filter dialog box opens.
4. In the List range field, type E2:E27 .
5. Select the Unique records only check box, and then click OK .
Excel displays the rows that contain the first occurrence of each different value in
the selected range.