The list will only show unique values from the original selected range and as it is a Dynamic Array Formula, you cannot amend or alter the formula in this spill range.īy combining the SORT and UNIQUE Functions together, you can obtain a list that only shows unique values and is sorted into alphabetical order. In a blank cell to the right of the data, type the following formula: =UNIQUE(B3:B13)Īs with the SORT Function, as soon as you press the ENTER key, the UNIQUE Function will spill over to the spill range and fill in the column below the cell where you have entered the formula. The UNIQUE Function is another Dynamic Array Function that allows you to extract unique values from a list. The spill range is identifiable by the thin blue line that appears around it. If you were to delete the formula in cell G2 for example, then the spill range would be cleared as well. Notice that in the formula bar, the formula is grayed out in this spill range due to the fact that it is a Dynamic Array Formula. The spill range automatically outputs all the unique values that are contained in the selected range. When you press ENTER, or click the check mark to enter the formula into Excel, a list of sorted values from the selected range will appear beneath the cell where you entered the formula. In a blank cell to the right of the data, type the following formula: =SORT(B3:B13) The SORT Function allows you to sort a list of data into alphabetical order. Note: It’s also possible to sort alphabetically using VBA. You can then use Data Validation to create a drop-down list from the data returned by these functions. To obtain a drop-down list of unique values sorted into alphabetical order, use two of these new functions, namely the UNIQUE and SORT Functions.
A powerful new feature of Excel 365 is the ability to sort data and only show unique data in a list due to a new feature called Dynamic Array Functions. When you enter repetitive data into Excel, it can sometimes be useful to have a drop-down list of options to select from. This tutorial will demonstrate how to alphabetize a drop-down list in Excel and Google Sheets.
0 kommentar(er)
