How to Convert Table to List in Excel (3 Quick Ways)

While we are working in excel with a data table, we may need to turn an Excel table (with row and column headings) into a list for some reason. The following methods can give you a proper guideline to convert a cross table to a list easily and quickly.


Download Practice Book

You can download the free Excel template from here and practice on your own.


3 Quick Ways to Convert Table to List in Excel

Method 1: Create PivotTable And PivotChart Wizard to Convert Table to List in Excel

Let’s get introduced to our dataset first. I have placed some salespersons’ sales according to three weeks. You will notice that there are different headings towards Row 2 and Column B. We want to make a list that will have all headings of weeks along a column and the sales amounts along another column. So now we’ll convert this data table to a list by using PivotTable And PivotChart Wizard. It’s a pretty lengthy procedure but easy.

PivotTable And PivotChart Wizard to Convert Table to List in Excel

First, we’ll bring the PivotTable And PivotChart Wizard on the top menu bar in Excel.

Step 1:

Click on File and you’ll see different options.

PivotTable And PivotChart Wizard to Convert Table to List in Excel

Step 2:

Then press the “Options” menu.

Excel Options window will open up.

PivotTable And PivotChart Wizard to Convert Table to List in Excel

Step 3:

Then click as follows: Customize Ribbon > Commands Not in the Ribbon > PivotTable And PivotChart Wizard.

 

PivotTable And PivotChart Wizard to Convert Table to List in Excel

Step 4:

After that, press successively: Data > New Group > New Group (Custom) >  Add

 

Soon later you will see an option of “PivotTable And PivotChart Wizard” like the image below.

Step 5:

Now just press OK and you will get the PivotTable And PivotChart Wizard option in the top menu bar.

Step 6:

Click serially: Data > PivotTable And PivotChart Wizard

A dialog box will appear.

Step 7:

Put marks on Multiple consolidation ranges and PivotTable

Then press Next

Step 8:

Later, click on the radio button named Create a single page field for me and press Next.

 

PivotTable And PivotChart Wizard to Convert Table to List in Excel

Step 9:

Now select the data range and press Next again.

Step 10:

Choose your desired worksheet. I have chosen New worksheet.

Click Finish.

A PivotTable Fields will appear at the right side of your Excel window.

PivotTable And PivotChart Wizard to Convert Table to List in Excel

Step 11:

Deselect the Row and Column option

Finally just double-click on the Sum of Value.

Now you notice that we have found our desired list in a new sheet.

Read more: How to Insert A Pivot Table in Excel


Similar Readings:


Method 2: Open Power Query to Switch Table to List in Excel

Power Query is such a tool that is used to simplify the process of collecting data from different sources and can be sorted into an Excel sheet in a convenient and usable format. Here, we’ll use it to convert an excel table to a list.

Step 1:

Select the data range

Then click as follows: Data > From Table/Range

A dialog box named Create Table will open up.

Power Query to Switch Table to List in Excel

Step 2:

Just press OK now.

And you will get a new window named “Power Query Editor”.

Power Query to Switch Table to List in Excel

Step 3:

Press Ctrl and hold it then using left-click of your mouse select the three columns of the week from this new window.

Then click: Transform > Unpivot Columns

Power Query to Switch Table to List in Excel

Soon after you will find a list like the image below.

Step 4:

Now just press Close & Load.

Then you will observe that we have got our expected list in a new worksheet.

Read more: Convert Range to Table in Excel


Method 3: Embed Excel VBA to Transform Table to List in Excel

If you like to code then it is possible to do the task using the Excel VBA. Let’s see how to do it.

Step 1:

Right-click your mouse on the sheet title.

Select View Code from the context menu.

A VBA window will open up.

Excel VBA to Transform Table to List in Excel

Step 2:

Write the codes given below-

Sub TransposeThis()
Set TheRange = Sheets("VBA").Range("B5:B11")
Set TheRange_output = Sheets("sheet4").Range("B2")
For i = 1 To TheRange.Cells.Count
    Set range_values = Range(TheRange.Cells(i).Offset(0, 1), TheRange.Cells(i).End(xlToRight))
    If range_values.Cells.Count < 15000 Then
        For j = 1 To range_values.Cells.Count
                TheRange_output.Value = TheRange.Cells(i).Value
                TheRange_output.Offset(0, 1).Value = range_values.Cells(j).Value
                Set TheRange_output = TheRange_output.Offset(1, 0)
        Next j
    End If
Next i
End Sub

Then press the Play icon to run the codes.

Soon after you will get your desired list in a new sheet.

Read more: How to Use an Excel Table with VBA


Conclusion

I hope all of the methods described above will be well enough to convert table to list. Feel free to ask any questions in the comment section and please give me feedback.


Further Readings

Tags:

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

2 Comments
  1. Hi, tried the first 2 methods, really useful thank you. Would it be possible to include an additional category on the left, eg the region of the salesperson?

  2. Hello Jane, thanks for your feedback.
    Yes, it’s possible, just add the column on the left and apply the commands as I applied.

Leave a reply

ExcelDemy
Logo