Design an Excel Summary Workbook for Large Monthly Datasets with Power Query
Question details
Create an efficient workbook structure for large monthly datasets, consolidate the data into a summary, and use the summary for a bar chart.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- The user needs to combine recurring monthly datasets while keeping the reporting workbook responsive and easy to update.
- Observed behavior
- Large source files and formula-heavy workbooks can become slow when they contain hundreds of thousands of rows.
Store each monthly source file in one folder and make sure every file uses the same column headers, data types, and category names. Remove merged cells, subtotal rows, and blank header rows before connecting the folder to Power Query.
Consolidate monthly source files with Power Query and load a summarized table
Use this method when each month is saved as a separate file with the same layout and you need a refreshable summary without extensive worksheet formulas.
Power Query can append the monthly files into one query, then group the records by category and month. Load only the final summary to the reporting sheet to reduce workbook calculation load.
Open a new Excel workbook and add a worksheet named Summary. In Column A, reserve space for chart categories or groups, and use Columns B onward for monthly values if you want a month-by-month matrix.
Select Data > Get Data > From File > From Folder, choose the folder containing the monthly files, and select Combine & Transform Data.
In the Power Query Editor, confirm that the first row is used as headers, set the correct data type for each column, and remove columns that are not needed for the report. Make sure category and date fields are consistently formatted.
Select Home > Group By in Power Query. Group by the category field and month field, then choose Sum for the value or amount column. If needed, select Transform > Pivot Column to place each month in its own column.
Select Home > Close & Load To, choose Table, and load the result to the Summary worksheet. Click inside the loaded table, then select Insert > Bar Chart and choose the preferred bar chart type.

Use a compact summary table instead of formulas across large raw datasets
Use this design when the source data is already available in one workbook or when you need a simple reporting layout alongside Power Query output.
Build a lightweight monthly summary and bar chart in WPS Spreadsheet
WPS Spreadsheet helps you keep large monthly reports manageable by separating raw data from a compact summary table. Use its data consolidation tools to combine values by category, then create a bar chart directly from the summarized results.
- 1. Create a Summary worksheet: In WPS Spreadsheet, select the plus button beside the sheet tabs and rename the new worksheet Summary. Enter categories in Column A and month headings across Row 1.
- 2. Prepare matching monthly ranges: Place each monthly dataset in a consistent tabular layout with the same category and value columns, or open each monthly workbook and identify the matching data range.
- 3. Consolidate the monthly values: On the Summary worksheet, select the destination cell, then choose Data > Consolidate. Select a consolidation function such as Sum, add each monthly source range, and confirm the consolidation.
- 4. Convert the output into a chart source: Select the completed summary range, including the category labels and month headings, then choose Insert > Chart.
- 5. Insert and format a bar chart: Choose a Bar chart type, then use the chart options to add a title, adjust the legend, and confirm that categories appear on the correct axis.

Frequently Asked Questions
Should I store every monthly dataset on a separate Excel worksheet?
For very large datasets, keeping monthly files in one folder is usually more efficient than placing every raw dataset in one workbook. Use Data > Get Data > From File > From Folder to combine the files through Power Query.
What must match before I combine monthly files in Power Query?
Each file should use the same headers, column order where possible, data types, and category naming. Power Query can append files more reliably when their source tables have the same structure.
How do I add a new month to an existing Power Query report?
Save the new file in the same source folder, use the established headers and format, then open the summary workbook and select Data > Refresh All.
Why is my large Excel workbook slow after adding formulas?
Formulas such as VLOOKUP, INDEX/MATCH, SUMIFS, and volatile functions can recalculate across large ranges. Use Power Query to transform and aggregate the raw data, then keep formulas limited to the small summary table.
How can I make the bar chart update after monthly data changes?
Create the chart from the table loaded by Power Query. After selecting Data > Refresh All, the loaded table updates; a chart linked to that table updates with the refreshed values.




