Combining Spreadsheets

Combining Spreadsheets: a reader asked…

It’s tax season and I’m trying to combine multiple spreadsheet files (saved as .csv) which I’ve downloaded from my bank (12 monthly activity spreadsheets). I’d like to create one Excel file on my PC that contains all the data from all 12 of those spreadsheets. Is there an easy way to do this? I’ve tried instructions from https://www.ablebits.com/office-addins-blog/merge-multiple-csv-files-excel/ but they don’t work.

I’m guessing you are dealing with 12 of the same type of data sets, e.g., where row 1 of the spreadsheet has the field names, and each subsequent row is a record of a transaction with information in each cell corresponding to the field name in row 1. And let’s assume the 12 spreadsheets all have the same structure, just different data in the rows.

The low-tech way to do this would be to simply open up the first spreadsheet and place your cursor on the A column, at the empty row directly underneath the last entry. Then open the 2nd spreadsheet, highlight all the data in row 2 and below, copy and paste that in. Repeat till you’ve added all 11 spreadsheets’ data into the first spreadsheet, and save that file.

But there may be a more efficient way to do this, using Excel’s built in Power Query tools.

Take your 12 .csv files and place them in their own folder with nothing else inside that folder. Now you can open Excel to a blank workbook, and follow these instructions:

1. click the menu item Data > Get Data > From File > From Folder. This opens a File Explorer window where you can select the folder you created with the 12 .csv files. Click the file folder name to select it, then click the Open button.
2. That opens a dialog box listing the 12 files with buttons at the bottom. Click the Combine button which expands, then click the “Combine & Load to” menu option.
3. The dialog box changes to show you the settings you can specify for each file. You should be able to just click the OK button and a smaller box will open with options on how you want to view the data. Click the “Existing worksheet” radio button so that the data will load into the empty spreadsheet tab you already have. Click the OK button.

You’ll now see a formatted table in your blank workbook with an extra field at the beginning in the A column – this shows the name of the source file for that record. The data will start in column B and continue across for all the fields, and you’ll see all the records from all 12 .csv files in that one table. You can delete Column A if you don’t need to save the source file information, and you’ll have the 12 separate tables from the .csv files in one table, with some nice color formatting. If you don’t want that formatting, save the file as a .csv and close it, then re-open it and you’ll have the data just as it appears in those 12 .csv files but without formatting.

This method is going to be a lot faster than the old-school copy-and-paste method, and you’ll still have all the data in one table, suitable for further work. You can use Excel’s sorting to put the records in whatever order you want, date order, amount, etc., and add column totals at the bottom if you’d like.

On that note, if your .csv files weren’t simple tables of transactions and have extraneous info in them like column totals, etc., you can either delete those from the .csv files before you do the data combine work, or do it after the combine work by sorting the table so those entries are all together and deleting the unneeded rows.