Consolidate CSV Files in One Excel Workbook

Yes. In Excel for Microsoft 365 on Windows, use Power Query to combine multiple CSV files into one table in a workbook: choose Data > Get Data > From File > From Folder, select the folder, then combine and load the files. This is usually the easiest repeatable method when the CSVs have the same column layout; for a one-time job, you can also copy their rows into a single worksheet.

What consolidating CSV files means

A CSV file stores plain-text values in rows and columns, but it does not store Excel workbook features such as multiple worksheets, formulas, formatting, or charts. Consolidating CSV files therefore usually means bringing their records into one Excel workbook, either as one combined table or as separate worksheets. The right result depends on what you need to do with the data afterward.

For example, imagine you receive a January, February, and March sales CSV, each with columns for Date, Region, Product, and Amount. Appending the records creates one table with all three months of sales. By contrast, putting each monthly file on its own worksheet keeps them separate inside one workbook. A third option is to combine files that contain different kinds of information, such as customers and orders, on separate tabs rather than stacking their rows together.

In everyday use, “consolidate” often means append rows from files that share a schema. The column names and meanings should match. If one file has Amount while another has Sales, or if one file puts Date in a different column, the results may need cleanup before the data can be compared reliably.

Best method for matching CSV files: Power Query

Power Query is Excel’s built-in tool for importing and reshaping data. It is a good choice when you have several files with the same columns, and especially when you will receive updated or additional files later. Instead of manually copying rows each time, you create a query that reads the files and can be refreshed.

Put the CSV files you want to combine in one folder. Keep unrelated files out of it, or be prepared to filter them from the query.
In Excel, open a blank workbook and choose Data > Get Data > From File > From Folder.
Browse to the folder and select it. In the file list preview, choose Combine and then Combine & Transform Data. The wording or placement can vary slightly with Excel updates.
Review the sample file and delimiter settings in the combine dialog. For ordinary comma-separated files, confirm that the columns are split as expected and that the first row is treated as headers when appropriate.
In the Power Query Editor, check the column names, data types, and rows. Remove unwanted columns or filter out files if necessary.
Choose Home > Close & Load to put the combined result into Excel. To choose a destination or load as a connection, use the load options available from Home > Close & Load > Close & Load To.

Power Query generally uses one file as a sample to interpret the structure of the others. That makes consistent headers and column order important. If each file has the same fields but the columns appear in a different order, inspect the result rather than assuming the combine operation aligned everything by meaning. Correct the source files or adjust the query so the final columns are mapped consistently.

Prepare the folder and source files

A little preparation prevents common import errors. Give the files clear names, store only the intended inputs in the source folder, and check that each file uses the same delimiter and header row. A folder might contain Sales_2026_01.csv, Sales_2026_02.csv, and Sales_2026_03.csv. Descriptive filenames also make it easier to identify a bad or missing month if the combined result looks wrong.

Check the headers: Column names should be consistent, including spelling and spacing. Decide whether the first row contains headers in every file.
Check the delimiter: CSV most commonly means comma-separated values, but exports can use semicolons or other separators depending on the program and regional settings.
Look for totals and notes: A summary row at the bottom of one file can become an ordinary record in the combined table. Remove it or filter it out deliberately.
Keep the output elsewhere: Save the finished workbook outside the input folder where practical. This reduces the chance of mixing generated files with source files.
Check for duplicates: Combining files appends records; it does not automatically know whether two identical-looking rows are duplicates.

CSV files do not have a single universal character encoding or guaranteed regional format. If names show garbled characters, or dates and decimals appear incorrectly, revisit the import settings in Power Query. The preview is useful: confirm that accented names, dates, and numbers look right before loading the result.

Keep data types and leading zeros intact

Excel may interpret values while importing. That can be helpful for dates and numbers, but it can also change data that is meant to remain text. Postal codes such as 02139, product codes such as 000784, and account identifiers can lose leading zeros if treated as numbers. Long identifiers may also be displayed or stored in a way that is unsuitable for exact matching.

In Power Query, select the relevant column and set its data type to Text when the value is an identifier rather than a quantity. Set genuine date and numeric columns to suitable types, and verify that decimal separators were read correctly. If a CSV has already been opened directly and Excel changed values, importing the original CSV again with the correct type is safer than trying to reconstruct lost zeros from the altered workbook.

Another frequent issue is mixed content in a column: some rows may contain dates while others contain labels such as Unknown. A type conversion can turn incompatible values into errors. Review error indicators in the Power Query Editor and decide whether to correct the source, keep the column as text, or handle exceptional values separately. Do not treat a successful load as proof that every value was interpreted correctly.

One workbook, one sheet, or separate sheets?

If the goal is one analysis-ready dataset, load the appended records into one worksheet as a table. This makes filtering, sorting, PivotTables, and charts more straightforward. A single table also provides a consistent place for later additions after the query is refreshed.

Use separate worksheets when files represent different subjects or have incompatible layouts. For instance, a workbook could have a Customers tab and an Orders tab, with each CSV imported to its corresponding sheet. Appending customer rows to order rows would not create a useful dataset because the columns describe different entities.

For a small, one-off task, manual copying can work: open each CSV, copy only its data rows, and paste them beneath the existing rows in a new workbook. Include the header once, not again above every pasted batch. This method is easy to understand but becomes error-prone as file counts grow, and the workbook will not automatically incorporate new source files.

When using manual copy and paste, check that the pasted columns line up, that no rows were skipped, and that the last row of one file is followed by the first data row of the next. Save the result as an Excel workbook, such as .xlsx, if you want to retain multiple sheets, formatting, or other workbook features.

Refresh, update, and save the combined workbook

A Power Query result is tied to its source files and their location. When more CSV files are added to the source folder, or existing files are replaced, return to the workbook and choose Data > Refresh All. Excel reruns the query and updates its output. If the source folder has moved or been renamed, the query may need its folder path updated before it can refresh.

Keep the original CSV files if you may need to verify or reprocess the source data. The combined worksheet is a result, not a substitute for keeping the inputs. Save the workbook as .xlsx to retain the query and its loaded results; saving as CSV would not preserve a multi-sheet workbook or the full Power Query setup. If you share the workbook, remember that another person may not have access to your local source folder, so the query may not refresh on their computer without an accessible shared location or a changed source path.

On Excel for Mac, Power Query and its connectors can differ by version and update channel. Look under Data > Get Data to see which import options are available; do not assume that every Windows connector or command is present. Excel for the web can open and edit many workbooks, but creating or refreshing a particular external data connection may require the desktop app. If the folder-import route is unavailable, use a supported desktop version or a manual import approach.

Limits, checks, and common mistakes

An Excel worksheet holds at most 1,048,576 rows and 16,384 columns. The header row counts toward the row limit, so a combined dataset that exceeds the worksheet capacity cannot all be loaded to one sheet. For data larger than that, consider loading to the Excel Data Model or using a database or other data-analysis tool; the Data Model has different capacity considerations and is not the same as displaying every record on a sheet.

Before relying on the combined data, compare the source and output. Check a few records from the first and last files, confirm expected row counts, and scan for blank rows, repeated headers, unexpected totals, conversion errors, and duplicate records. A simple test is to filter the source-file name column, if included in the query, and confirm that records from each expected file appear. The filename can be retained as a useful provenance field so a row can be traced to its source.

Repeated header rows: If headers are included inside the data as well as at the top, remove those rows during transformation.
Different schemas: Missing or renamed columns may produce blanks or separate fields. Standardize headers before combining or explicitly transform them.
Wrong dates or numbers: Check locale and type settings instead of assuming Excel interpreted the text as intended.
Unexpected files: Filter the folder listing to include only the intended CSV extension and files, especially when the folder is reused.
Duplicate records: Decide what counts as a duplicate using the business key or record identifiers; identical values are not always evidence that a row should be deleted.

Frequently Asked Questions

Can Excel combine CSV files automatically?
Yes. Power Query can combine files from a folder and refresh the result later. The files should usually have the same column structure, and the source folder must remain accessible to the workbook.

Can I combine CSV files with different column names?
Yes, but first decide which columns represent the same information. Standardize the headers or transform the columns in Power Query. If the files describe different kinds of records, keep them on separate sheets or in separate tables instead of appending them.

Will adding another CSV to the folder update the workbook?
After the query is set up to read that folder, add the file there and choose Data > Refresh All. Check that the new file meets the same structure and is not excluded by a filter.

How do I combine CSV files without losing leading zeros?
Import the identifier column as Text in Power Query before loading it. Opening a CSV directly may cause Excel to interpret codes as numbers, so start again from the original CSV if zeros have already been removed.

Can I put every CSV on its own worksheet?
Yes. Import each file separately and load it to a different worksheet, or copy each file’s contents to a separate tab. That keeps the files together in one workbook but does not append them into one combined table.

Is a CSV file itself an Excel workbook?
No. CSV is a plain-text, single-table format. You can open it with Excel, but to save multiple worksheets, queries, and workbook formatting, save the finished work as an .xlsx file.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.