Mind. Blown.

Stop Doing Manual Tasks Excel can do Faster

Imagine this scenario: There are 120 CSV files in a folder on your network. Each CSV export contains the transactions for a single month, and there are 10 years’ worth of files. Your mission, should you choose to accept it, is to combine all of the transactions in these 120 files into a single Excel worksheet.

That’s a lot of copy/paste. Imagine how long it would take for you to open 120 files, copy the contents of each and paste/append the transactions into a combined file. Say you could process one file per minute; that would be around 120 minutes. And, these are monthly files. What if they were weekly files? That would be about 520 files. Or if they were daily exports?

Often in Excel, there are multiple ways to accomplish any given task. Some ways are faster than others. Let’s call the copy/paste approach the slow way. The fast way: We can have Excel combine these for us in less than a minute. No formulas. No macros. Just click a few buttons.

Note: These steps use the most current version of Excel as of the date of this writing (Excel 2016) for Windows (Version 1703). If you are using a previous version, you may not have these features. You can determine your version by selecting File > Account, and viewing the product info.

Hop in the Fast Lane

In any blank workbook, select Data > New Query > From File > From Folder. Excel displays the Folder dialog, where you can enter or browse to the folder that stores all the CSV files. They can all be in that folder or organized in subfolders. After identifying the folder and clicking “OK,” Excel displays a dialog allowing you to preview the list of files it found in the folder, as seen in Figure 1:

Figure 1

At this point, click the Combine > Combine & Load button, which opens a dialog that allows you to specify additional settings if needed. Then simply click “OK.” What happens next will blow your mind: Within moments, you’ll see the transactions from all the files flow into your worksheet (Figure 2).

Figure 2

Wow, Right!?

Out of curiosity, I timed this process from start to finish. I had the transactions from all 120 files in my Excel worksheet in about 20 seconds. And, here is the best part: Not only is this faster right now, it is also easy to update next period. You can simply add new CSV files to the folder, right-click the results table and select Refresh. Any new files in the folder will automatically be included.

In another experiment, the process to combine 720 files took about the same
amount of time, around 20 seconds. Then, I wondered how long it would take Excel to process thousands of files. Excel combined more than 7,000 CSV files in about 40 seconds. Assuming you manually process one file per minute, this would take around 7,000 minutes—more than 100 hours.

Excel combined all these files in about 40 seconds. Always remember, Excel rules!

Further Info

• Your performance times may vary depending on the number of transactions per file, computer speed, network speed and so on. But, even if your computer takes more than 20 seconds, I’ll bet it still beats copy/paste.
• You need the most current version of Excel, with updates installed. If not, you may not have the Combine Binaries button on the dialog shown in Figure 1. If you are using a previous version of Excel 2016, you may be able to accomplish this using the Combine Binaries icon in the column label.
• For more, visit www.excel-university.com/get-transform-analternative-to-copy-paste-append.
• If you are using Excel 2013, you may be able to accomplish this by downloading and installing the PowerQuery add-in from the Microsoft website.
• If the folder contains files types, such as PDF, that need to be excluded, click the Edit button from Figure 1, then use Extension column filters to exclude files based on extension, then click the Combine Binaries icon in the Content column.
• If you need to do something similar with Excel files that contain multiple worksheets, you can use a variation described at www.excel-university.com/retrieve-values-from-many-workbooks.