Excel Formula for Unique Values in Ascending Order

Formula Overview

To extract unique values from a dataset in Excel and display them in ascending order, you can utilize the combination of the UNIQUE and SORT functions.

Formula Structure

=SORT(UNIQUE(range))

Parameters

• range: This is the range of cells from which you want to extract unique values.
• UNIQUE: This function filters the specified range to return only the unique values.
• SORT: This function arranges the resulting unique values in ascending order.

Example

Dataset

Assume you have a list of values in cells A2:A10:

Application of Formula

To get the unique fruits in ascending order, enter the following formula in any other cell, for example, in cell B2:

=SORT(UNIQUE(A2:A10))

Explanation of the Formula

1. UNIQUE(A2:A10): This function evaluates the range from cells A2 to A10 and produces a list of distinct values. From our dataset, it will return:

   • Apple
   • Banana
   • Orange
   • Grape
   • Kiwi
   • Mango

2. SORT(…): Once the unique values are identified by the UNIQUE function, the SORT function will then arrange them in ascending order. The final output you will see in cells B2:B7 will be:

   • Apple
   • Banana
   • Grape
   • Kiwi
   • Mango
   • Orange

Conclusion

Utilizing the UNIQUE and SORT functions together in Excel allows you to efficiently extract and organize unique values from a dataset. This method simplifies data analysis tasks requiring distinct entries and ordered presentation. For further mastery of Excel functions and data analysis, consider exploring training options available through the Enterprise DNA Platform.