Convert a Crosstab (2D) Table to a Flat List in Excel: A Full Guide
When dealing with Excel data, it is common to encounter crosstab tables (also known as pivot tables or matrix tables) where data is arranged in a two-dimensional format. However, many data analysis and reporting tasks require data to be in a normalized list format (a "long format" table). This article explains various efficient methods to transform a crosstab table into a list, making it easier to analyze, filter, and use in databases.
Convert a crosstab table (2-dimensional table) to list in Excel
Convert a crosstab table to list with POWER QUERY
In Excel, Power Query is the most efficient way to transform data, especially when handling large datasets.
- Select your data and go to "Data" tab, then, click "From Table/Range", see screenshot:
- In the popped out "Create Table" dialog box, click OK button.
- Then, a "Power Query Editor" window is opened. In the window, select all the column headers except the first one (Category).
- And then, click on "Transform" > "Unpivot Columns". The Power Query will create two new columns: "Attribute" (your former column headers) and "Value" (the cell values).
- Rename these columns appropriately (e.g., "Month" and "Sales") to your need.
- Then, Click "Home" > "Close & Load" to return the transformed list to Excel. The flat list will appear in a new sheet.
Convert a crosstab table to list with Kutools for Excel
While Power Query can achieve this, Kutools for Excel provides the fastest and easiest solution with just a few clicks. With Kutools for Excel’s "Transpose Table Dimensions" feature, you can quickly convert complex crosstab tables into structured lists or vice versa without writing formulas or going through multiple transformation steps.
After installing Kutools for Excel, please do with the following steps:
- Select the table which you will convert to a list. Then, click "Kutools" > "Range" > "Transpose Table Dimensions".
- In the "Transpose Table Dimensions" dialog box, check the "Cross table to list" option, in the "Results range" section, select a cell to put the result, see screenshot:
- Finally, click OK button, you will see the original table is converted into a flat list.
Convert a crosstab table to list by uisng formulas
If you're not yet comfortable using Power Query or need a solution that works in older Excel versions, these powerful formulas will effectively convert your crosstab data into a structured list format.
For list the category column, please apply the following formula:
=INDEX($A$2:$A$4, INT((ROW(A1)-1)/COLUMNS($B$1:$D$1))+1)Drag the formula down to get all items of the category until error values are displayed. See screenshot:
For list the month column, please apply the following formula:
=INDEX($B$1:$D$1, MOD(ROW(A1)-1, COLUMNS($B$1:$D$1))+1)Auto-fill the formula downward to populate all required rows, ensuring the range matches your original category column's row count.
For list all other data, please apply the following formula:
=INDEX($B$2:$D$4, INT((ROW(A1)-1)/COLUMNS($B$1:$D$1))+1, MOD(ROW(A1)-1, COLUMNS($B$1:$D$1))+1)Drag the formula down until all data is extracted. See screenshot:
Conclusion
For most users, Power Query provides the ideal balance of power, flexibility, and maintainability for converting crosstab tables to lists. Kutools offers the simplest solution for those who have it installed, while formulas remain a viable option when no other tools are available.
Choose the method that best fits your technical comfort level, Excel version, and the frequency with which you need to perform this transformation. For recurring reports or dashboards, Power Query's refresh capability makes it the clear winner in most scenarios. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials to help you master Excel.
Related articles:
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