How to Create a Dynamic GL Code and Entity Report in Excel
Question details
The user needs to transform a large financial dataset containing GL codes in columns and entity codes in rows into a clean, dynamically refreshable report.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Summarizing cross-tabular financial data into a monthly refreshable summary report.
- Observed behavior
- The raw data requires reshaping from a wide layout to a normalized tabular format to allow for accurate PivotTable summarization.
Ensure your raw financial data is formatted as an official Excel Table (select the data and press Ctrl+T). This ensures that any new rows or columns added in the future will automatically be captured in your report updates.
Use Power Query to Unpivot Data and Generate a PivotTable
Transform your cross-tabular GL data into a normalized layout using Power Query, then summarize it dynamically with a PivotTable.
Power Query is the ideal tool for reshaping large financial datasets where General Ledger (GL) codes are spread across multiple columns. By unpivoting these columns, you convert the data into a flat, tabular format that easily feeds into a refreshable PivotTable.
Select any cell within your financial data table, navigate to the 'Data' tab on the Excel ribbon, and click 'From Table/Range' to open the Power Query Editor.
In the Power Query Editor, hold down the 'Ctrl' key to select all columns representing your entity codes. Right-click any of the selected column headers and choose 'Unpivot Other Columns'. This action shifts your GL codes into a single, combined column.
To keep your resulting report clean, click the filter dropdown arrow on your newly created 'Value' column header, uncheck the '0' option, and click 'OK'.
Click 'Close & Load' from the Home tab, then select 'Close & Load To...'. Choose 'PivotTable Report' to directly create a dynamic summary based on your restructured dataset.
In the PivotTable Fields pane, drag Entity codes to the Rows area, GL codes to the Columns area, and your amounts to the Values area. Whenever you add new monthly data to the source table, simply right-click the PivotTable and select 'Refresh'.

Easily Manage Financial Reports with WPS Office
While advanced Power Query unpivoting is a feature specific to Microsoft Excel, WPS Spreadsheet offers a robust, free alternative for standard financial data management. You can seamlessly open your Excel files, utilize complex formulas, and create dynamic PivotTables to summarize your data without paying costly subscription fees.

Frequently Asked Questions
Can I add new GL codes to the source data without rebuilding the entire report?
Yes, as long as your original source data is formatted as an Excel Table. When you add new columns for new GL codes, Power Query automatically includes them the next time you right-click your resulting PivotTable and select 'Refresh'.
Why do my entity codes show up multiple times after unpivoting?
Unpivoting transforms the cross-tabular layout into a flat dataset where every single intersection of an entity and a GL code gets its own individual row. Summarizing this flattened data later with a PivotTable will group them back together logically.
How do I remove empty or zero-value rows to keep the GL report clean?
While inside the Power Query Editor, click the dropdown arrow on the header of your generated 'Value' column and uncheck the box next to '0'. This filters out all unnecessary zero values before loading the clean data back into your Excel sheet.




