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

<div class="code-mirror  language-vb" contenteditable="plaintext-only" spellcheck="false" style="outline: none; overflow-wrap: break-word; overflow-y: auto; white-space: pre-wrap;"><span class="token keyword">Sub</span> ConvertTable<span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token comment">'Updated By Nirmal</span>
<span class="token keyword">Dim</span> Rng <span class="token keyword">As</span> Range
<span class="token keyword">Dim</span> cRng <span class="token keyword">As</span> Range
<span class="token keyword">Dim</span> rRng <span class="token keyword">As</span> Range
<span class="token keyword">Dim</span> xOutRng <span class="token keyword">As</span> Range
xTitleId <span class="token operator">=</span> <span class="token string">"Convert to Column"</span>
<span class="token keyword">Set</span> cRng <span class="token operator">=</span> Application<span class="token punctuation">.</span>InputBox<span class="token punctuation">(</span><span class="token string">"Select your Column labels"</span><span class="token punctuation">,</span> xTitleId<span class="token punctuation">,</span> <span class="token keyword">Type</span><span class="token punctuation">:</span><span class="token operator">=</span><span class="token number">8</span><span class="token punctuation">)</span>
<span class="token keyword">Set</span> rRng <span class="token operator">=</span> Application<span class="token punctuation">.</span>InputBox<span class="token punctuation">(</span><span class="token string">"Select Your Row Labels"</span><span class="token punctuation">,</span> xTitleId<span class="token punctuation">,</span> <span class="token keyword">Type</span><span class="token punctuation">:</span><span class="token operator">=</span><span class="token number">8</span><span class="token punctuation">)</span>
<span class="token keyword">Set</span> Rng <span class="token operator">=</span> Application<span class="token punctuation">.</span>InputBox<span class="token punctuation">(</span><span class="token string">"Select your data"</span><span class="token punctuation">,</span> xTitleId<span class="token punctuation">,</span> <span class="token keyword">Type</span><span class="token punctuation">:</span><span class="token operator">=</span><span class="token number">8</span><span class="token punctuation">)</span>
<span class="token keyword">Set</span> outRng <span class="token operator">=</span> Application<span class="token punctuation">.</span>InputBox<span class="token punctuation">(</span><span class="token string">"Out put to (single cell):"</span><span class="token punctuation">,</span> xTitleId<span class="token punctuation">,</span> <span class="token keyword">Type</span><span class="token punctuation">:</span><span class="token operator">=</span><span class="token number">8</span><span class="token punctuation">)</span>
<span class="token keyword">Set</span> xWs <span class="token operator">=</span> Rng<span class="token punctuation">.</span>Worksheet
k <span class="token operator">=</span> <span class="token number">1</span>
xColumns <span class="token operator">=</span> rRng<span class="token punctuation">.</span>Column
xRow <span class="token operator">=</span> cRng<span class="token punctuation">.</span>Row
<span class="token keyword">For</span> i <span class="token operator">=</span> Rng<span class="token punctuation">.</span>Rows<span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">.</span>Row <span class="token keyword">To</span> Rng<span class="token punctuation">.</span>Rows<span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">.</span>Row <span class="token operator">+</span> Rng<span class="token punctuation">.</span>Rows<span class="token punctuation">.</span>Count <span class="token operator">-</span> <span class="token number">1</span>
    <span class="token keyword">For</span> j <span class="token operator">=</span> Rng<span class="token punctuation">.</span>Columns<span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">.</span>Column <span class="token keyword">To</span> Rng<span class="token punctuation">.</span>Columns<span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">.</span>Column <span class="token operator">+</span> Rng<span class="token punctuation">.</span>Columns<span class="token punctuation">.</span>Count <span class="token operator">-</span> <span class="token number">1</span>
        outRng<span class="token punctuation">.</span>Cells<span class="token punctuation">(</span>k<span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span> <span class="token operator">=</span> xWs<span class="token punctuation">.</span>Cells<span class="token punctuation">(</span>i<span class="token punctuation">,</span> xColumns<span class="token punctuation">)</span>
        outRng<span class="token punctuation">.</span>Cells<span class="token punctuation">(</span>k<span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">)</span> <span class="token operator">=</span> xWs<span class="token punctuation">.</span>Cells<span class="token punctuation">(</span>xRow<span class="token punctuation">,</span> j<span class="token punctuation">)</span>
        outRng<span class="token punctuation">.</span>Cells<span class="token punctuation">(</span>k<span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">)</span> <span class="token operator">=</span> xWs<span class="token punctuation">.</span>Cells<span class="token punctuation">(</span>i<span class="token punctuation">,</span> j<span class="token punctuation">)</span>
        k <span class="token operator">=</span> k <span class="token operator">+</span> <span class="token number">1</span>
    <span class="token keyword">Next</span> j
<span class="token keyword">Next</span> i
<span class="token keyword">End</span> <span class="token keyword">Sub</span>
</div>

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.