How to create list from range in Excel?
You may need to create a list by a range of data frequently, for instance, a range of data on 24 solar terms as shown as below. Of course, you can copy and paste them into a list manually, but it seems somewhat tedious if there are many columns in the range. Actually, there are a couple of tricks to create a list from specified range in Microsoft Excel easily.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
In Excel, you also can use VBA code to quickly import a list or range.
1. Hold ALT button and press F11 on the keyboard to open a Microsoft Visual Basic for Application window.
2. Click Insert > Module, and copy the VBA into the module.
VBA: Create list from range
Sub UniqueList() 'Updateby20140304 Dim InputRng As Range, OutRng As Range xTitleId = "KutoolsforExcel" 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
3. Click Run button or press F5 to run the VBA. A dialog displayed on the screen, and select the range of data that you will create a list with. See screenshot:
5. Click OK, select a cell to output the list from the pop-up dialog, then click OK. See screenshot:
Then the range of data has been created as a list in the selected cell.
The handy tool – Kutools for Excel's Transform Range function also can quickly and easily create a list from a range data.
1. Select the data range you want to create it as a list, and click Kutools > Range Converter > Transform Range. See screenshot:
2. In the pop-up Transform Range dialog, check Range to single column, and click OK, another dialog displayed for you to select a blank cell to output the list, click OK. See screenshot:
3. Then the list is created.