How to Export Folder and Subfolder Structure to Excel

Written by

Mel Jenkins

Reviewed by

Laura Tsitlidze

Last updated on June 5, 2023

This tutorial demonstrates how to export the folder and subfolder structure to Excel.

ImportFolder Intro

Import List of File Names and Folder Locations with Power Query

Using VBA to list the files in a folder is a great solution. However, Excel has introduced a way to list files using Power Query, where VBA is not required.

  1. In the Ribbon, go to Data > Get Data > From File > From Folder.

ImportFolder Ribbon

  1. Browse to the folder that contains the folders you want a list of, and then click Open.

ImportFolder Open

  1. When you see the list of files, click Transform Data.

ImportFolder Transform

This opens the Power Query Editor.

ImportFolder PowerQuery

  1. Using the Power Query Editor, remove columns you don’t want to import and/or merge columns together, depending on your needs and preferences.
    To remove a column, select that column and in the Ribbon, go to Home > Remove Columns > Remove Columns.

ImportFolder RemoveColumns

  1. To remove more than one column at once, in the Ribbon, go to Home > Choose Columns > Choose Columns.

ImportFolder Choose Columns

  1. Untick the columns you don’t need and click OK.

ImportFolder Load

  1. In the Ribbon, go to Home > Close & Load to load the data into Excel.

ImportFolder Loaded data

  1. The data comes into Excel as a data table that is connected to the source folder. This means that, if you add another file to any of the folders selected and click Refresh, the Queries & Connections pane shows an updated number of files.

ImportFolder Refresh

  1. Return to Power Query if you need to edit the information shown. In the Ribbon, go to Query > Edit.

ImportFolder PowerQuery Edit

  1. In the Ribbon, go to Home > Query > Manage Columns > Manage Columns, and then add a few more columns back into the query.

ImportFolder-PowerQuery AddColumns

  1. Each column can be filtered. In the Extension column, for example, filter by .xlsx to show only Excel files.
    Click Select All to remove checkmarks from all extensions, and then tick the .xlsx extension only.

ImportFolder SelectExcelOnly

  1. Click OK to apply the filter.

ImportFolder FilterXL

Tip: You could also filter by Attribute – in the case below, by size.

ImportFolder SelectSizeOnly

  1. If you applied any steps to the query that you no longer need, delete them from the Applied Steps list in the Query Settings pane on the right side of the screen.

ImportFolder ShowSteps

  1. Select the step you wish to remove.

ImportFolder RemoveStep

If you remove anything but the last step, you get a warning. However, you may delete the steps in the order you applied them without getting a warning.

ImportFolder Warning

  1. Once you have adjusted your data, click Close and Load.

Other tutorials that use Power Query

AI Formula Generator

Try for Free

See all How-To Articles