How to Create List from Range in Excel (4 Easy Ways)

While working with a large database, you may need to create a list of data or text from the given database. If you do it manually, it will cost you a great deal of time. Excel has some features and formulas by which you can easily make your list from the data range. Today in this article, we will demonstrate four handy methods to create a list from range in Excel.

How to Create List from Range in Excel: 4 Simple Methods

In this section of the article, we will learn four simple methods to create a list from range in Excel. Not to mention, we used the Microsoft 365 version for this article; however, you can use any version according to your preference.

1. Creating a Drop-Down List from Range

Making a drop-down list is a great way to create lists from a given range. Using a drop-down list, you can select any specific data in seconds. Here in this section, we will discuss two types of drop-down lists i.e. independent and dynamic drop-down lists using the Data Validation tool and the rest deals with VBA code to create a drop-down list with multiple selections. Let’s say, we have the following dataset. In the dataset, we have two columns named Book Name and Movie Name. Our goal is to create a drop-down list using this dataset.

1.1 Independent Drop-Down List

In the first method, we will create an independent drop-down list. So, let’s follow the steps mentioned below to do this.

Steps:

Firstly, select the cell where you want to create the drop-down list. Here, we selected cell B17.
Then, go to the Data tab from ribbon.
After that, choose the Data Validation option from the Data Tools group.

As a result, the Data Validation dialog box will open on your worksheet.

Now, in the Data Validation dialogue box, choose the List option in the Allow field.
Following that, in the Source field select the range of cells B5:B14.
Then, click OK.

Consequently, a drop-down icon will appear beside cell B17, as shown in the following image.

Now, click on the drop-down icon.
After that, select any name of the book from the drop-down. In this case, we selected the book The Road Back.

As a result, you will have the following output on your worksheet.

Now, follow the same procedure for creating a drop-down list for the Movie Names and you will get the following output on your worksheet.

1.2 Dynamic Drop-Down List

A dynamic drop-down list will auto-update your data. That means, if we delete some of the data from the dataset, the drop-down list will automatically update according to the change in the dataset. Now, let’s use the steps outlined below to do this.

Steps:

Firstly, select the cell where you want to create the drop-down list. Here, we selected cell B17.
Following that, go to the Data tab from Ribbon.
Now, choose the Data Validation option from the Data Tools group.

Subsequently, the Data Validation dialog box will open on your worksheet.

Now, in the Data Validation dialogue box, choose the List option in the Allow field.
Following that, in the Source field, type in the following formula.

=OFFSET($B$5,0,0,COUNTIF($B$5:$B$14,"<>"))

Here, cell $B$5 indicates the first cell of the Book Name column, and the range of cells $B$5:$B$14 denotes all of the cells of the Book Name column.

Then, click OK.

Consequently, a drop-down icon will appear beside cell B17, as shown in the following image.

Now, delete a cell from the Book Name column. In this case, we deleted the last cell of the Book Name column which is A Study in Scarlet.

As a result, you will see that A Study in Scarlet is no longer available in the drop-down list.

Now, click on the drop-down icon.
After that, select any name of the book from the drop-down. In this case, we selected the book The Road Back.

Subsequently, you will have the following output on your worksheet.

Now, follow the same procedure for creating a drop-down list for the Movie Names and you will get the following output on your worksheet.

1.3 Drop-Down List with Multiple Selections

In Excel, generally, we can’t select multiple options from a drop-down list. But, using the VBA macro option, we can achieve this without any hassle. In the following section, we will discuss the detailed steps to do this along with the respective VBA code.

Steps:

Firstly, select the cell where you want to create the drop-down list. In this case, we selected cell B17.
Afterward, go to the Data tab from ribbon.
Then, choose the Data Validation option from the Data Tools group.

As a result, the Data Validation dialogue box will open on your worksheet.

Following that, in the Data Validation dialogue box, choose the List option in the Allow field.
Then, in the Source field select the range of cells B5:B14.
Next, click OK.

Subsequently, a drop-down icon will appear beside cell B17, as shown in the following image.

Now, use the keyboard shortcut ALT + F11 to open the Microsoft Visual Basic window.
In the Microsoft Visual Basic window, find the name of the worksheet that you are currently using. In this case, it is Multiple Sections.
After that, double-click on the name of the worksheet.

Following that, write the following code.

Option Explicit
Private Sub Worksheet_Change(ByVal target_value As Range)
Dim prev_value As String
Dim current_value As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Not Intersect(target_value, Range("B17")) Is Nothing Then
  If target_value.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If target_value.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    current_value = target_value.Value
    Application.Undo
    prev_value = target_value.Value
      If prev_value = "" Then
        target_value.Value = current_value
      Else
        If InStr(1, prev_value, current_value) = 0 Then
            target_value.Value = prev_value & vbNewLine & current_value
      Else:
        target_value.Value = prev_value
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

Code Breakdown

Firstly, we initiated a sub-routine named Worksheet_Change.
Inside the function argument, we specified the target_value as Range.
Then, we declared two variables named prev_value and current_value as String.
Following that, we used an IF statement to specify the output cell.
Then, we used another IF statement to make sure that this VBA code only works for those cells which have data validation enabled in them.
Now, we assigned the value of the target_value variable to the current_value variable.
After that, we used another IF statement to check if the value of the prev_value argument is “” or not.
If this condition is satisfied, then the value of the current_value variable will be assigned to the target_value variable.
Following that, we used the VBA InStr function in another IF statement to check whether the output of the InStr function in 0 or not.
If the output is 0, then both the prev_value and current_value will be assigned to the target_value variable with a line break.
Otherwise, prev_value will be assigned to the target_value variable.
Then, we closed the IF statements.
Finally, we ended the sub-routine.

After writing the code, click on the Save option.

Following that, click on the drop-down icon beside cell B17.
Then, choose a Book Name form the drop-down list. In this case, we selected the book named The Road Back.

As a result, the name of the book will appear in cell B17 as shown in the following image.

Similarly, add more Book Names to the list as many as you want.

Consequently, you will have the following outputs on your worksheet, as demonstrated in the following image.

2. Using Named Range

Using Named Ranges is another smart way to create a list from range in Excel. A named range is an array of cells that is assigned a name defined by the user. Once, you define a named range, you can call it from anywhere in a worksheet. You can also use the named range from another worksheet also. Now, let’s follow the instructions outlined below.

Steps:

Firstly, select the range of cells that you want to include in the drop-down list. Here, we selected cells B5:B14.
After that, define a suitable name inside the marked box of the following image. In this case, we used the name Books to name our range.

Note: While naming ranges, spaces are not allowed between multiple words.

Similarly, name the cells of range C5:C14. Here, we used the name Movies to name this range.

After that, select the cell where you want to create the drop-down list. In this case, we selected cell B17.
Now, go to the Data tab from Ribbon.
Following that, choose the Data Validation option from the Data Tools group.

Subsequently, the Data Validation dialogue box will open on your worksheet.

Following that, in the Data Validation dialogue box, choose the List option in the Allow field.
Now, in the Source field select the range of cells B5:B14.
Afterward, click OK.

As a result, a drop-down icon will be available beside cell B17 as shown in the following image.

Now, click on the drop-down icon.
After that, select any name of the book from the drop-down. In this case, we selected the book The Road Back.

Similarly, follow the same steps to create a list from the range of cells C5:C14 and you will have the following outputs.

3. Creating List from Range Based on Criteria

In this section of the article, we will discuss how we can create a list from range based on criteria in Excel. Here, we will cover creating a list based on both single and multiple criteria.

3.1 Creating List Based on a Single Criterion

First, let’s learn the steps to create a list based on a single criterion. Let’s say we have the List of Teams with Team Members as our dataset. We will create a list of Team Members based on the selected Team. Now, let’s follow the steps mentioned below.

Steps:

Firstly, enter the following formula in cell C16.

=IFERROR(INDEX($B$2:$C$13,SMALL(IF($C$5:$C$13=$B$16,ROW($B$5:$B$13)),ROW(1:1))-1,1),"")

Here, the range of cells $B$2:$C$13 indicates the entire dataset, range $C$5:$C$13 refers to the cells of the column named “Team” cell $B$16 represents the selected “Team”, and range $B$5:$B$13 refers to the cells of the column named “Members”.

Formula Breakdown

In the first ROW function ROW($B$5:$B$13),
- $B$5:$B$13 → It is the [reference] argument.
- Output → {5;6;7;8;9;10;11;12;13}.
Now, in the IF function IF($C$5:$C$13=$B$16,ROW($B$5:$B$13)),
- $C$5:$C$13=$B$16 → It denotes the logical_test argument.
- ROW($B$5:$B$13) → This represents the [value_if_true] argument.
- Output → {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;11;12;13}.
Next, the SMALL function becomes → SMALL({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;11;12;13},ROW(1:1))
- Here, {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;11;12;13} → This is the array argument.
- ROW(1:1)) → This indicates the k argument.
- Output → {11}.
After that, the INDEX function, INDEX($B$2:$C$13,SMALL(IF($C$5:$C$13=$B$16,ROW($B$5:$B$13)),ROW(1:1))-1,1) becomes → INDEX($B$2:$C$13,{11}-1,1).
- Here, $B$2:$C$13 → It is the array argument.
- {11}-1 → This denotes the row_num argument.
- 1 → It is the column_num argument.
- Output → “James”.
Now, the IFERROR function becomes =IFERROR(“James”,””).
- Here, “James” → It indicates the value argument.
- “” → This refers to the [value_if_error] argument.
- Output → James.

After that, press ENTER.

As a result, you will have the following output in cell C16.

Finally, you can use the AutoFill option to get the remaining outputs as demonstrated in the following image.

You can change the name of the Team from C to A and the outputs will be changed automatically.

3.2 Creating List Based on Multiple Criteria

Now, we will use multiple criteria to create a list from a range in Excel. In the following dataset, we have the names of Team Members with Team names along with their Roles. Our goal is to create a list based on the selected Team name and Role.

Steps:

Firstly, use the following formula in cell D16.

=IFERROR(INDEX($B$2:$D$13,SMALL(IF(($C$5:$C$13=$B$16)*($D$5:$D$13=$C$16),ROW($B$5:$B$13)),ROW(1:1))-1,1),"")

Then, hit ENTER.

As a result, you will have the name of a team member from Team A and the Role of Executive.

Finally, drag the Fill Handle to obtain the rest of the outputs as shown in the following image.

You can change the criteria according to your need and the outputs will be adjusted automatically as demonstrated in the following picture.

4. Generating List Using VBA Macro Feature

Using the VBA Macro feature, we can easily make a list from a given range of data. To learn it, let’s follow the procedures mentioned below.

Steps:

Firstly, press ALT + F11 to open the Microsoft Visual Basic window.

In the Microsoft Visual Basic window, find the name of the worksheet that you are currently using. In this case, it is VBA Macro.
Following that, double-click on the name of the worksheet.

After that, write the following code.

Sub UniqueList()
Dim InputRng As Range, OutRng As Range
xTitleId = "Book & Movie Name"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range:", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("OutPut to (single cell):", xTitleId, Type:=8)
For I = 1 To InputRng.Rows.Count
    For j = 1 To InputRng.Columns.Count
        OutRng.Value = InputRng.Cells(I, j).Value
        Set OutRng = OutRng.Offset(1, 0)
    Next
Next
End Sub

Code Breakdown

Firstly, we created a sub-routine named UniqueList.
After that, we introduced two variables named InputRng, and OutRng as Range.
Then, we specified the title for the message box that will appear when running the macro.
Following that, we used Set statements to define the InputRng and the OutRng.
Then, we initiated a For Next loop from 1 to the number of rows in the input range.
Now, we started another For Next loop from 1 to the number of columns in the input range.
Then, we assigned the cells of the InputRng to the OutRng.
Now, we used the OFFSET function with a Set statement to define the OutRng variable.
Next, we ended the For Next loops.
Lastly, we ended the sub-routine.

After writing the code, click on the Save icon as marked in the following image.

Now, select the cell where you want to create the list. Here, we selected cell E5.
Afterward, go to the Developer tab from Ribbon.
After that, choose the Macros option from the Code group.

As a result, the Macro dialogue box will open on your worksheet.

Now, in the Macro dialogue box, click on Sheet7.UniqueList option.
Then, click on Run.

After that, a dialogue box will pop up named Book & Movie Name.

Now, select the entire dataset and click OK.

Following that, select the destination cell of the outputs. In this case, we selected cell E5.
Then, click OK.

Consequently, the list of Book Name and Movie Name will appear in the Book & Movie Name column as shown in the following image.

How to Create a Unique List from Range in Excel

In this section of the article, we will discuss how we can create a unique list based on criteria from the range in Excel. To do this, we are going to use the UNIQUE function of Excel.

Note: The UNIQUE function is available in Excel 2021 and in Excel 365.

Steps:

Firstly, apply the formula given below in cell D5.

=UNIQUE(B5:B25)

Here, the range of cells B5:B25 indicates the cells of columns Book and Movie Name.

Following that, press ENTER.

Consequently, you will have a list of unique cells from the range B5:B25 as shown in the following image.

Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it yourself.

Download Practice Workbook

Download this practice sheet to practice while you are reading this article.

Creating List from Range.xlsm

Conclusion

So, these are the most common and effective methods you can use anytime while working with your Excel datasheet to create list from range in Excel. If you have any questions, suggestions, or feedback related to this article, you can comment below.

How to Create List from Range in Excel (4 Easy Ways)

How to Create List from Range in Excel: 4 Simple Methods

1. Creating a Drop-Down List from Range

1.1 Independent Drop-Down List

1.2 Dynamic Drop-Down List

1.3 Drop-Down List with Multiple Selections

2. Using Named Range

3. Creating List from Range Based on Criteria

3.1 Creating List Based on a Single Criterion

3.2 Creating List Based on Multiple Criteria

4. Generating List Using VBA Macro Feature

How to Create a Unique List from Range in Excel

Practice Section

Conclusion

Similar Articles for You to Explore

Leave a reply Cancel reply