Power Query Problems

How to Combine Multiple CSV Files into an Excel Summary Table

Steven Parker Oct 14, 2026 872 views

Question details

The user needs to import several CSV files into Excel and combine selected fields from them into a single summary table.

How to Combine Multiple CSV Files into an Excel Summary Table

Product: Microsoft Excel
Device & OS: not provided
Scenario: Consolidating specific data fields from multiple CSV sources into a single, unified report worksheet.
Observed behavior: The user requires a structured method to extract, transform, and load specific columns from multiple CSV files into one combined worksheet without manual copying and pasting.

Before you start

Gather all your source CSV files into a single folder on your computer to streamline the import process, and verify that they share common column headers for the fields you intend to combine.

Solution 1Recommended

Combine CSV Files Using Excel Power Query

Power Query is the most efficient and robust method to import, transform, and append multiple CSV files from a folder into a single summary table.

By pointing Power Query to a specific folder, Excel will automatically append all compatible CSV files found inside. This allows you to filter out unnecessary columns and standardize data formats before loading the final dataset into your worksheet.

Get Data from Folder

Open Excel, navigate to the 'Data' tab on the ribbon, and click on 'Get Data'. Choose 'From File', and then select 'From Folder'. Browse your computer to select the folder containing your CSV files and click 'Open'.

Combine and Transform

A preview window will appear listing the files. Click the 'Combine' dropdown button at the bottom and select 'Combine & Transform Data'. Excel will evaluate the files and open the Power Query Editor.

Select Required Columns

In the Power Query Editor, hold down the 'Ctrl' key and click the headers of the specific columns you want to keep in your summary. Right-click on any of the selected headers and choose 'Remove Other Columns'.

Standardize Data Types

Check the data type icon located on the left side of each column header (such as ABC for text, 123 for whole numbers). Click the icon to change the data type if necessary, ensuring consistency across your summary.

Load to Worksheet

Once your data is cleaned and selected, click the 'Close & Load' button in the top left corner of the Home tab. The combined data will be automatically loaded into a new Excel worksheet as a formatted summary table.

Combine CSV Files Using Excel Power Query

Automated Updates: If you add new CSV files to the designated folder in the future, you do not need to repeat these steps. Simply click 'Refresh All' on the Data tab to instantly update your summary table.

Free Microsoft Office alternative

Manage and Analyze Spreadsheet Data Easily with WPS Office

While Power Query is a specific feature built into Microsoft Excel, WPS Office provides a highly compatible, lightweight, and free alternative for handling your daily spreadsheet and data consolidation tasks.

1. Download and Install: Visit the official WPS website to download and install the free WPS Office suite on your device.
2. Open Your Files: Launch WPS Spreadsheets and open your CSV or XLSX files instantly with zero format loss.
3. Consolidate Data: Use the Data tab to access intuitive built-in tools for sorting, filtering, and summarizing your information.

Seamlessly open, edit, and save in standard Microsoft Excel formats, including .xlsx, .xls, and .csv.Utilize built-in Data Consolidation and PivotTable tools to summarize multiple datasets easily.Enjoy a lightweight installation with a familiar, tabbed user interface that requires no learning curve.Access completely free core spreadsheet functions across Windows, Mac, Linux, and mobile devices.

microsoft office alternative - wps office

Frequently Asked Questions

Can I combine CSV files with different column headers using Power Query?

Yes, Power Query can append files with different headers. Columns with matching names will be aligned automatically, while unmatched columns will produce 'null' (blank) values for the files that do not contain those specific headers.

How do I refresh the combined summary table when source files change?

Navigate to the 'Data' tab on the Excel ribbon and click 'Refresh All'. Power Query will automatically re-run your exact transformation steps, pulling in any new or modified data from the source CSV folder.

Why are some of my dates or numbers formatted incorrectly after importing the CSV files?

CSV files are plain text and do not store formatting information. In the Power Query Editor, you must manually set the correct data types (such as Date, Currency, or Whole Number) by clicking the icon on the left side of the column header before loading the data.

Is there a limit to how many CSV files I can combine this way?

While Power Query does not have a strict limit on the number of files it can process, Excel worksheets are limited to 1,048,576 rows. If your combined data exceeds this limit, you must choose to load the query into the Data Model instead of a traditional worksheet.

Excel & Data Problems