I know what you’re thinking: “Another blog post about importing all excel files in a folder”, but this couldn’t be farther from the truth. As the title says, this is the ULTIMATE guide to import all files from a folder and I’ll cover from very simple scenarios to dealing with dynamic starting rows or importing specific cells.

The tool that I’ll be using for this is called Power Query (or Get and Transform in Excel 2016). If you have Excel 2010 or 2013, you can download Power Query from this link. If you have Excel 2016 it is already embedded in Excel.

Note: For all these examples, I’ll use Excel 2016, but you can do it with Excel 2010/2013.

In Excel 2016, Power Query is in the Data Tab as shown below:

In Excel 2010/2013, Power Query is in a separate tab as shown below:

Therefore, if you have Excel 2010/2013 when I say Data New Query, you should go to the Power Query tab.

I created a separate post for each import scenarios. Use the links below to a specific scenario: 

Import scenarios

• Import all csv files in a folder
• Import all sheets from all excel files in a folder
• Import and clean each file from a folder
• Import files with a dynamic starting row
• Import specific cells from Excel files

NEWS: Microsoft has updated the combine file experience for Office 365 subscribers, stay tuned for a post about this. Subscribe to the website to be notified.

I tried to cover as much as possible about this topic but if you encounter any other challenges please let me know.  This will be a live post that I’ll modify as new Power Query updates come up.

Subscribe to the website if you want to be notified when this post is updated.