Mind. blown. stop doing manual tasks excel can do faster.

• Author: Lenning, Jeff
• Position: Tech Talk

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.

[ILLUSTRATION OMITTED]

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 (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:

[ILLUSTRATION OMITTED]

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, as shown in Figure 2:

[ILLUSTRATION OMITTED]

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...