How to Combine CSV Files into One Excel Workbook, One Sheet Each

Twelve monthly exports arrive as twelve CSV files, and someone wants them as one Excel file with a tab for each month. It sounds like a single command, but Excel has none for it, and the obvious route of opening each CSV and copying the sheet across quietly changes values on the way.

Here are three methods that work, what each one does to your data, and the limits all of them run into. We tested them in Excel 2021 on Windows 11 with files containing SKUs such as 00123, ZIP codes and long file names.

First: Sheets or One Table?

A workbook with a sheet per file is good for reading one month at a time. It is poor for analysis: a total across the year means a formula that visits twelve sheets, and a filter only works on one sheet at a time. If the point is to compare or total the files, merge them into one table instead, with a column recording the source file. The merge tool does that, and merging CSV files with different columns covers the details. If you do want one sheet per file, read on.

Whichever method you use, the result must be saved as an Excel workbook (.xlsx). A CSV holds a single table, and when we saved a two-sheet workbook as CSV, only the active sheet was written.

Method 1: Move or Copy Each Sheet

Fine for a handful of files whose values Excel will not change. In Excel for Windows:

1. Create a blank workbook and save it as .xlsx. This is the destination.
2. Open the first CSV. Excel shows it as a workbook with one sheet named after the file.
3. Right-click the sheet tab and choose Move or Copy. In To book, pick the destination workbook; in Before sheet, choose (move to end). Click OK.
4. Repeat for each CSV, then delete the empty sheet the destination started with, and save.

Both workbooks need to be open for the destination to appear in the list. On a Mac, the same dialog is under Edit › Sheet › Move or Copy Sheet.

Two things go wrong with this method. First, values change when each CSV is opened, before you copy anything: in our test 00123 arrived on the combined sheet as 123, and elsewhere Excel turned 4111111111111111 into 4.11E+15 and MAR-1 into a date. If your files have IDs, codes or leading zeros, use Method 2 or 3. Second, sheet names are cut to 31 characters. A file called store-4471-daily-sales-export-2026-08-31.csv became a sheet called store-4471-daily-sales-export-2, which is exactly the part every file in the series has in common.

Method 2: Import Each File with Power Query

Importing instead of opening lets you stop Excel converting values. In Excel for Windows, starting from the destination workbook:

1. Go to Data › Get Data › From File › From Text/CSV and choose the first CSV.
2. In the preview, check that File Origin and Delimiter match the file, and set Data Type Detection to Do not detect data types. Microsoft describes the effect: every column then defaults to Text, so 00123 stays 00123.
3. Click the arrow next to Load, choose Load To…, select Table and New worksheet.
4. Rename the new sheet tab, then repeat for each file, and save.

Each file becomes a table on its own sheet, and because each one is a query, it can be refreshed when a new version of that file replaces the old one. It is still one import per file, which gets tedious beyond a dozen.

Do not reach for Get Data › From Folder for this job. It is Power Query’s tool for many files, but it combines files “as long as they have the same file type and structure (including the same columns)” into one table. That is the right answer if you wanted one table, and not the one if you wanted a sheet per file.

Method 3: A Python Script for Many Files

For dozens or hundreds of files, or a job you repeat every month, a script is faster. This one needs Python and two packages, installed with pip install pandas openpyxl. Put the CSVs in a folder called exports and run the script from the folder above it:

import glob, os, re
import pandas as pd

used = set()
with pd.ExcelWriter("combined.xlsx") as book:
    for path in sorted(glob.glob("exports/*.csv")):
        name = os.path.splitext(os.path.basename(path))[0]
        name = re.sub(r"[\\/?*:\[\]]", "_", name)[:31]
        base, n = name, 2
        while name.lower() in used:
            suffix = f" ({n})"
            name = base[: 31 - len(suffix)] + suffix
            n += 1
        used.add(name.lower())
        table = pd.read_csv(path, dtype=str, keep_default_na=False, encoding="utf-8-sig")
        table.to_excel(book, sheet_name=name, index=False)

It writes combined.xlsx with one sheet per file, named after the file. Most of its lines deal with problems we hit while testing a shorter version:

• Two long file names wiped out a sheet. With names cut to 31 characters, the files for 30 and 31 August both became store-4471-daily-sales-export-2. The shorter script wrote both to the same sheet without an error, and only the 31 August data survived. This script names the second one store-4471-daily-sales-expo (2) instead.
• A bracket in a file name stopped the script. sales [final].csv raised ValueError: Invalid character [ found in sheet title, because Excel does not allow : \ / ? * [ ] in sheet names. This script replaces them with underscores.
• Leading zeros vanished without dtype=str. Without it, pandas read ZIP codes as numbers and 00742 arrived as 742. With it, every value stays text. keep_default_na=False stops pandas treating text such as NA or null as a missing value, which would arrive in Excel as an empty cell.

encoding="utf-8-sig" reads UTF-8 files with or without a byte order mark. A file from older Windows software may be Windows-1252 instead, and then the script stops: ours raised UnicodeDecodeError: 'utf-8' codec can't decode byte 0xeb on the ë in Zoë. Change the encoding to cp1252 for such files, which read them correctly, or convert them first with the encoding fixer.

Limits All Three Methods Share

When we tried to rename a sheet to a 48-character name, and then to sales/2026, Excel refused both with the same message, “You typed an invalid name for a sheet or chart”, followed by the three rules: no more than 31 characters, none of : \ / ? * [ ], and not blank.

If a CSV is too big for one sheet, split it first and give each part its own sheet, or keep it out of the grid entirely: opening a CSV that is too large for Excel covers the Data Model route. And if the workbook is going back out as CSV files later, CSV UTF-8 vs CSV explains which format to save each sheet in.