How to Unpivot General Ledger Data in Excel Using Power Query
Question details
The user needs to convert a cross-tab general ledger report into a normalized, three-column list containing account, entity, and amount.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Transforming financial data from a matrix or cross-tab format into a flat, tabular format for database storage or PivotTable analysis.
- Observed behavior
- The current dataset displays general ledger accounts in rows and entity codes in columns, which needs to be transformed into a standardized list.
Ensure your general ledger data is formatted as an official Excel Table (using the Ctrl+T shortcut) and that your column headers clearly represent the entity codes before loading it into Power Query.
Use Power Query's 'Unpivot Other Columns' Feature
This is the most efficient and dynamic method to normalize cross-tab data in Excel, allowing for automatic updates when new entity columns are added to the source data.
By utilizing the 'Unpivot Other Columns' command, Power Query anchors your selected row identifiers (like Account Names) and melts all other columns into rows. This guarantees that future entity columns added to the right of your table are automatically picked up during a data refresh.
Click anywhere inside your general ledger table, navigate to the 'Data' tab on the Excel ribbon, and select 'From Table/Range'. This action will open the Power Query Editor window.
Locate the column that contains your general ledger accounts (the data you want to remain in rows). Right-click directly on the header of this account column.
From the right-click context menu, choose 'Unpivot Other Columns'. Power Query will immediately transform all the unselected entity columns into two new columns.
Double-click the new column headers to rename them appropriately. Change the 'Attribute' column header to 'Entity' and the 'Value' column header to 'Amount'.
Click the 'Close & Load' button located on the Home tab of the Power Query Editor. Excel will generate a new worksheet containing your transformed, three-column normalized list.
Try WPS Office for Your Data Analysis Needs
If you are looking for a fast, lightweight, and cost-effective office suite, WPS Office provides robust spreadsheet capabilities. While advanced Power Query transformation is specific to Microsoft Excel, WPS Spreadsheet handles large datasets, complex formulas, and advanced PivotTables seamlessly—all within a highly compatible and familiar interface.
Frequently Asked Questions
What does it mean to unpivot data in an Excel spreadsheet?
Unpivoting transforms data from a wide matrix format (where categories are spread across multiple column headers) into a tall, normalized format. It consolidates multiple column headers into a single 'Attribute' column and their corresponding numbers into a single 'Value' column.
Will 'Unpivot Other Columns' work if I add new entity columns next month?
Yes. By selecting the Account column and choosing 'Unpivot Other Columns', Power Query dynamically processes all remaining columns. Any new columns added to the source table will be automatically unpivoted when you refresh the query.
Can I unpivot multiple row identifier columns at once?
Absolutely. If your general ledger report has multiple fixed columns (such as Account Number, Account Name, and Department), hold the Ctrl key to select all of them, right-click any selected header, and choose 'Unpivot Other Columns'.
Why can't I just use a regular PivotTable to normalize the data?
Standard PivotTables are designed to aggregate and summarize normalized flat data into cross-tab reports, not the other way around. Power Query is the dedicated tool built specifically to transform and reverse-engineer cross-tab reports back into a flat data structure.
