How to convert matrix style table to three columns in Excel?
When working with data in Excel, you may frequently encounter matrix-style tables that present information in a grid, where both the rows and columns serve as headings. While this format is visually useful for some analyses, you may need to transform this matrix into a "list" or three-column table for tasks such as database import, data normalization, charting, or advanced analysis. Converting the matrix to a three-column list (sometimes referred to as "unpivoted" data) allows for easier filtering, aggregation, and integration with other data tools. See the example below illustrating the transformation:
➤ Convert matrix style table to list with PivotTable
➤ Convert matrix style table to list with VBA code
➤ Convert matrix style table to list with Kutools for Excel
➤ Convert matrix style table to list with Excel Formula
Convert matrix style table to list with PivotTable
In Excel, there isn’t a direct built-in command to convert a matrix-style table into a three-column list. However, using the PivotTable Wizard, you can efficiently transform a cross-tabulated matrix into flat, tabular data suitable for further analysis. This approach is ideal for small to medium datasets and especially useful when you want to flatten a complex report structure. It's less suited for large datasets or users unfamiliar with PivotTable operations.
1. Open the worksheet containing your matrix. Press Alt + D, then P to open the PivotTable and PivotChart Wizard. In the wizard:
- Under Where is the data that you want to analyze, choose Multiple consolidation ranges.
- Under What kind of report do you want to create, select PivotTable.
2. Click Next. In the Step2a of 3 dialog, select I will create the page fields:
3. Click Next. In Step2b of 3, click the 
button and select the matrix's full data range, including row and column headers. Click Add to insert the range into the All ranges list. Confirm your range selection covers the entire matrix.
4. Click Next. In Step3 of 3, choose whether to place the PivotTable in a new worksheet or in a specific cell:
5. Click Finish. Excel generates a PivotTable summarizing the matrix. By default, it displays aggregated totals at the intersections. You don’t need to adjust the PivotTable structure for this task—just proceed to the next step:
6. Double-click the cell where the row and column Grand Total intersect (e.g., cell F22). Excel will create a new sheet containing a three-column list, where each row shows a unique row-header and column-header combination with the corresponding value.
7. To finalize, select the new table, right-click, then choose Table > Convert to Range. This removes the table formatting, leaving a plain, editable list:
Tip: If your matrix changes frequently, you’ll need to repeat this process to refresh the three-column list. This method is best for static data. Additionally, if your matrix contains blank or merged cells, some cleanup may be required before using this technique.
Convert matrix style table to list with VBA code
If you prefer automation or want to apply this transformation repeatedly, using a VBA macro can quickly convert any matrix-style table into a structured three-column list. This method is especially effective for large datasets or diverse layouts and eliminates the need for manual formatting. It's ideal for users familiar with running VBA scripts.
1. Press Alt + F11 to open the Microsoft Visual Basic for Applications editor.
2. In the editor, click Insert > Module to create a new module. Then paste the following code into the module window:
📜 VBA code: Convert matrix to list
Sub ConvertTable()
' Updated by Extendoffice
Dim Rng As Range
Dim cRng As Range
Dim rRng As Range
Dim xOutRng As Range
xTitleId = "KutoolsforExcel"
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 Sub3. Press F5 or click Run to execute the macro. A series of prompts will guide you through the required selections:
Step 1: Select the column labels (usually the top row of your matrix):
Step 2: Select the row labels (usually the first column of your matrix):
Step 3: Select the actual matrix data range (excluding row and column headings):
Step 4: Choose the output cell where the converted three-column list should begin. It’s recommended to use a blank cell or a new worksheet:
Step 5: Click OK. Your matrix will now be converted into a flat three-column list.
⚠️ Notes & Tips:
• Ensure you don’t include column or row headings in the matrix data range.
• If your matrix has merged cells, unmerge them before running the macro to avoid errors.
• If you encounter errors, double-check the ranges you selected and ensure they align correctly.
Convert matrix style table to list with Kutools for Excel
While the above methods are effective, they may seem tedious or intimidating for less experienced users. If you're looking for a quick, user-friendly solution, Kutools for Excel offers a dedicated utility called Transpose Table Dimensions, specifically designed for this purpose.
This tool is ideal for users who frequently convert matrix-style tables or need to perform batch processing. It can preserve original formatting—such as font, fill color, and formulas—if needed. One potential downside is that Kutools is a third-party add-in that needs to be installed, but it's a powerful option for anyone who regularly reshapes data in Excel.
Steps:
1. After installing Kutools, go to the Kutools tab, click Range, and then select Transpose Table Dimensions:
2. In the Transpose Table Dimensions dialog box:
- (1) Choose Cross table to list under the Transpose type section.
- (2) Click the
button next to Source range to select your matrix table. - (3) Click the button next to Results range to specify where to place the output.
Ensure you select the entire matrix (including both headers and data) to avoid partial conversion or incorrect results.
3. Click OK. The matrix will be instantly converted into a three-column list, preserving original cell formatting where possible:
Tip: This feature also supports the reverse operation—converting a flat list into a two-dimensional matrix. It's helpful for rebuilding reports or preparing data for cross-tab analysis. See: how to convert a list to a 2D cross table.
➤ Learn more about the Transpose Table Dimensions feature
⏬ Download and try Kutools for Excel for free now!
Convert matrix style table to list with Excel Formula
If you prefer a formula-based approach—especially useful if you want your three-column list to update dynamically as the matrix changes—you can use a combination of the INDEX, ROW, COLUMN, and COUNTA functions to manually unpivot the data. This solution does not require VBA or add-ins and is ideal for those who wish to avoid using macros or external tools. However, it does require careful attention to formula references as formulas must typically be array-filled or systematically dragged down/across. It is most practical for moderate-sized matrices and situations where the list needs to remain live and responsive to source data changes.
Suppose your data range is as follows:
- The row labels are in cells A2:A10.
- The column labels are in cells B1:J1.
- The matrix values are in cells B2:J10.
1. Create a new worksheet or start in an empty area of your existing sheet. In cell L2, enter the following formula to extract the row label:
=INDEX($A$2:$A$10,INT((ROW(A1)-1)/COUNTA($B$1:$J$1))+1)2. In cell M2, enter this formula to extract the corresponding column label:
=INDEX($B$1:$J$1,MOD(ROW(A1)-1,COUNTA($B$1:$J$1))+1)3. In cell N2, extract the value from the matrix with:
=INDEX($B$2:$J$10,INT((ROW(A1)-1)/COUNTA($B$1:$J$1))+1,MOD(ROW(A1)-1,COUNTA($B$1:$J$1))+1)4. Select the cells L2:N2 and drag the fill handle downward. The stopping point should be number of rows × number of columns (in this sample, that’s 9 rows × 9 columns = 81 rows total).
✅ Tips:
- Adjust all ranges to fit your actual data structure.
- Use
IFandISBLANKto filter out empty rows if needed. - For auto-expanding behavior, consider using
OFFSETor dynamic named ranges. - This method is most practical for static matrix size and moderate data volume.
ℹ️ Additional Notes:
- Advantages: Formulas stay live and reflect matrix changes automatically. No VBA or add-ins required.
- Disadvantages: May slow down workbook performance for large matrices. Requires careful setup.
Demo: Convert matrix style table to list with Kutools for Excel
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in