Jonayed
Sometimes we need to create a list from the range in Excel. In this article, we are going to show you how to create list from range in Excel with 3 easy approaches. We have also included some relevant concerns at the end of this article. So, let’s get started.
📂 Download Excel File
Download the free workbook from here:`
Learn to Create List from Range in Excel with 3 Approaches
While writing this article, “Excel Create List from Range ”, we’ve considered a dataset that contains approximately 3 columns and 15 rows. In this dataset, we added Brand and Famous for to make you better understand. But if you want, you can change the entities and put the values of your own.
1. Creating Drop Down–List
Applying the Drop Down list to Excel create a list from the range is a very easy and useful method. You can do this very easily. In Excel create a list from range, there is a feature that we know as an independent Drop Down list. You just have to maintain the following steps.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cell E6.
- Secondly, go to the Data tab.
- Now click on the Data Validation option.
- Now you will get a dialogue box named Data Validation.
- Select List to Allow and for Source you need to select
=$B$6:$B$13
- Thirdly, tap the OK button.
- Finally, you will get the results as well.
- Again you need to select cell F6.
- Secondly, go to the Data tab.
- Now click on the Data Validation option.
- Again you will get a similar dialogue box named Data Validation.
- This time select List to Allow and for Source you need to select
=$C$6:$C$13
- Thirdly, press the OK button.
- Consequently, you get a similar result for cell F6.
Undoubtedly this technique is very user-friendly to work on.
2. Creating List from Range Established by Criteria
Creating a list from a range established by criteria using INDEX, MATCH, and COUNTIF functions is another method to Excel create list from range. You just need to follow the given steps.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cell D6.
- Secondly, write the given formula on that particular cell.
=INDEX(B6:B21,MATCH(0,COUNTIF($D$5:D5,B6:B21),0))
🔨 Formula Breakdown
Here-
👉 $D$5:D5 is the criteria and,
👉 MATCH(0,COUNTIF($D$5:D5,B6:B21),0) means that the criteria will match the values from cells B6 to B21.
- Now press Ctrl+Shift+Enter.
- Finally, you will see the obtained result.
Truly this is a very handy and easy procedure for Excel to create a list from range.
📕 Read More: 5 Ways to Link a Cell Value with a Drop-Down List in Excel
3. Using VBA Macro
You can also create a list from the range in Excel by using VBA Macro. Follow the given steps and you will easily achieve this.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, go to the Excel worksheet.
- Secondly, tap Alt+F11.
- Then a VBA window will be opened.
- Now click on Sheet5 (VBA).
- Then click on Insert and select Module.
- After that, you will get the window of Module.
- Now paste the given code on that window and click Run.
Code
Sub UniqueList()
Dim InputVal As Range, OutVal As Range
xTitleId = "Brand & Famous for"
Set InputVal = Application.Selection
Set InputVal = Application.InputBox("Range:", xTitleId, InputVal.Address, Type:=8)
Set OutVal = Application.InputBox("OutPut to (single cell):", xTitleId, Type:=8)
For i = 1 To InputVal.Rows.Count
For j = 1 To InputVal.Columns.Count
OutVal.Value = InputVal.Cells(i, j).Value
Set OutVal = OutVal.Offset(1, 0)
Next
Next
End Sub
- Then you will get a dialogue box named Brand & Famous for.
- Select the range from $B$6:$C$13.
- Now press OK.
- Again you will get the dialogue box named Brand & Famous for.
- This time select $E$6 as Output to (single cell).
- Now tap OK.
- Consequently, you will get the desired result.
Finally, this is a straightforward method to create list from range in Excel.
How to Create a List from a Table in Excel
If you want to create a list from the table in Excel, then you are in the exact place. We are going to show you how to create list from the table in Excel with the following steps using the Power Query Editor.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, select cells B5 to C13.
- Secondly, go to the Data tab and select From Table/Range.
- Thirdly you will get a dialogue box named Create Table.
- Now press the OK.
- After that, the Power Query editor table will appear to you.
- Press Ctrl and then Right-Click on your mouse on the Famous for the column.
- Then go to the Transform menu and click on the Unpivot Columns.
- Now press the Close & load option.
- Finally, you will get the required result to create a list from the table.
Truly, using the Power Query Editor to create list from table in Excel is a very simple method to work on.
📕 Read More: How to Create Drop-Down List in Multiple Columns in Excel
How to Create Drop Down List with Multiple Selections in Excel
If you intend to create a drop down list with multiple selections in Excel, then this portion is for you. You can easily do it by following some steps.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, move to the Excel worksheet.
- Secondly, press Alt+F11.
- Then VBA window will appear to you.
- Double-Click on Sheet7(Multiple selections).
- Now a module will be opened.
- Thirdly, paste the given code there.
Code
Private Sub WS_Change(ByVal Target As Range)
Dim Oldnum As String
Dim Newnum As String
On Error GoTo Finishsub
If Target.Address = "$D$6" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Finishsub
Else: If Target.Value = "" Then GoTo Finishsub Else
Application.EnableEvents = False
Newnum = Target.Value
Application.Undo
Oldnum = Target.Value
If Oldnum = "" Then
Target.Value = Newnum
Else
Target.Value = Oldnum & ", " & Newnum
End If
End If
End If
Application.EnableEvents = True
Finishsub:
Application.EnableEvents = True
End Sub
- Now go back to the worksheet.
- Finally, you will find the list with multiple selections.
Undoubtedly, using VBA code to create list with multiple selections in Excel is a straightforward and easy method.
📄 Important Notes
While performing the processes mentioned above, you need to be aware of these things:
🖊️ While applying Drop Down List, you need to be careful about selecting the cells.
🖊️ You should be careful when you intend to use the Excel functions.
🖊️ You must be conscious while applying the VBA code.
🖊️ You need to be careful of selecting the column while using the Power Query Editor.
📝 Takeaways from This Article
If we summarize the whole article, we have got some points.
📌 Firstly, we’ve applied the Drop Down List to create a list from a range in Excel.
📌 Secondly, we established criteria for Excel to create a list from a range
📌 Thirdly, we applied the VBA code.
📌 Fourthly, we tried to show you how to create a list from the table in Excel.
📌 finally, we’ve tried to show you how to create a drop down list with multiple selections in Excel using VBA code.
Conclusion
We wish that you would be able to create list from range in Excel using this article. As several methods have been shown here, you can easily follow which one is best suited for you. If there is any skepticism in your mind, feel free to ask me in the comment. In the end, to learn more about Excel tactics, we recommend you to visit our website www.ExcelDen.com.
