- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Convert Matrix Style Table to Three Columns in Excel
Sometimes in Excel, you may have needed to convert a matrix-style table to a column-style table. Any table with m rows and n columns is referred to as a matrix style table, and any table with m rows and three columns is referred to as a three column table in Excel. Read this tutorial to learn how you can convert a matrix-style table to a three-column table in Excel. We can complete this process with the help of a VBA application, as it can’t be completed directly in Excel.
Converting a Matrix Style Table to Three Columns in Excel
Here, we will first create a VBA module and then run the code to complete the task. Let's look at a simple procedure for converting a matrix-style table to a three-column table in Excel.
Step 1
Let us consider any Excel sheet that contains a matrix-style table similar to the below image.
Then right-click on the sheet name and select view code to open the VBA application, then click on insert and select module.
Right click > View code > Insert > Module
Step 2
Then type the below-mentioned programme into the text box, as shown in the below image.
Program
Sub ConvertTable() 'Updated By Nirmal Dim Rng As Range Dim cRng As Range Dim rRng As Range Dim xOutRng As Range xTitleId = "Convert to Column" Set cRng = Application.InputBox("Select your Column labels", xTitleId, Type:=8) Set rRng = Application.InputBox("Select Your Row Labels", xTitleId, Type:=8) Set Rng = Application.InputBox("Select your data", xTitleId, Type:=8) Set outRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8) Set xWs = Rng.Worksheet k = 1 xColumns = rRng.Column xRow = cRng.Row For i = Rng.Rows(1).Row To Rng.Rows(1).Row + Rng.Rows.Count - 1 For j = Rng.Columns(1).Column To Rng.Columns(1).Column + Rng.Columns.Count - 1 outRng.Cells(k, 1) = xWs.Cells(i, xColumns) outRng.Cells(k, 2) = xWs.Cells(xRow, j) outRng.Cells(k, 3) = xWs.Cells(i, j) k = k + 1 Next j Next i End Sub
Step 3
Now save the sheet as a macro-enabled workbook and click F5 to run the code, then select your column label and click OK.
Step 4
Then select your row label and click OK.
Step 5
Now select your data, excluding the row and column labels, and click OK.
Step 6
Finally, click OK after selecting the single cell from which you want to begin your table.
Conclusion
In this tutorial, we used a simple example to demonstrate how we can convert a matrix-style table to three columns in Excel.