Power Query Problems

How to Combine Excel Workbooks and Create Trend Comparison Charts

WPS Editor Sep 30, 2026 871 views

Question details

The user needs to consolidate project and expense data scattered across multiple contractor workbooks into a single, automated table to generate trend comparison charts.

How to Combine Excel Workbooks and Create Trend Comparison Charts

Product: Excel
Device & OS: not provided
Scenario: Merging financial or project data from various external contractor files into one centralized worksheet for pivot charting and trend analysis.
Observed behavior: Data is currently stored in separate workbooks, requiring an automated consolidation method to build reliable PivotTables and PivotCharts.

Before you start

Ensure all contractor workbooks are saved in a single folder and that each source table uses identically spelled column headings, such as Contractor, Project, Date, and Expense.

Solution 1Recommended

Use Power Query to Import and Append Data

Power Query is the most efficient and robust method to extract and append data from multiple workbooks into a single, refreshable table.

This method treats a specific folder as a data source. Any new contractor file dropped into this folder will automatically be included in your data model upon refreshing.

Connect to the Folder

Go to the Data tab, click 'Get Data', select 'From File', and choose 'From Folder'. Browse to the directory containing your contractor workbooks and click Open.

Combine the Files

In the preview window, click the 'Combine' dropdown at the bottom and select 'Combine & Transform Data'. Choose the specific sheet or table format that applies to all files and click OK.

Clean Data in Power Query Editor

Verify that your columns align correctly. Remove any unnecessary columns and ensure data types are correct (e.g., formatting the Expense column as Currency).

Load the Consolidated Table

Click 'Close & Load' in the top left corner. Excel will import the appended data into a new worksheet as a unified table ready for analysis.

Use Power Query to Import and Append Data

Automated Updates: When contractors send updated files, simply save them into the source folder, open your master workbook, and click 'Refresh All' on the Data tab to instantly update the table.

Solution 2

Create PivotCharts to Compare Trends

Once your data is consolidated, use a PivotChart to visually analyze and compare project expenses and trends over time.

Solution 3

Use the Data Consolidate Feature

A quick alternative if you only need to sum or average static, identically structured data ranges without using Power Query.

Consolidate Data with WPS Spreadsheet

Combine Workbooks and Analyze Trends in WPS Office

WPS Spreadsheet provides powerful data consolidation tools and robust PivotChart capabilities, allowing you to merge multiple data sources and visualize reporting trends with ease.

1. Access Data Tools: Open WPS Spreadsheet and navigate to the 'Data' tab on the top ribbon.
2. Consolidate Data: Click on 'Consolidate' to merge selected ranges from your various contractor workbooks into a single master sheet.
3. Insert a PivotTable: Highlight the newly combined data, go to the 'Insert' tab, and select 'PivotTable' to structure your analysis.
4. Generate PivotCharts: With the PivotTable selected, click 'PivotChart' under the Insert tab and drag your Date and Expense fields to generate visual trend comparisons.

Seamlessly consolidate data from multiple worksheets and external workbooks.Create dynamic PivotTables and PivotCharts to instantly analyze project trends.High compatibility with Microsoft Excel (.xlsx) formats and legacy files.Lightweight software with a familiar, user-friendly interface that requires no steep learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why aren't my columns aligning correctly when I append workbooks using Power Query?

This usually happens if the source tables have inconsistent column headings, mismatched casing, or hidden trailing spaces. Power Query is case-sensitive. Ensure all contractor workbooks use the exact same header names, such as Contractor, Project, Date, and Expense.

Do I need to manually update the combined table when a contractor sends a new file?

No. If you set up Power Query to pull data 'From Folder', you simply need to save the new contractor file into that designated folder. Afterward, click 'Refresh All' on the Data tab to pull in the new data automatically.

What happens if a contractor's workbook contains blank rows?

Power Query will import the blank rows as null values. You can easily remove them before loading the final table by opening the Power Query Editor, clicking the filter dropdown on a key column (like Date or Project), and unchecking 'null' or '(Blank)'.

Can I use different chart types for my trend comparison?

Yes. While line charts are generally best for showing trends over time, you can customize your PivotChart easily. Simply right-click the existing chart, select 'Change Chart Type', and choose alternatives like clustered column or area charts.

Excel & Data Problems