How to see the folder structure when you export a SharePoint Document Library to Excel

(Forward by Ben Cardonick)

I wrote this article three years ago and I'm glad to see how many people it's helped. It quickly moved up the ranks in Google and other search engines, until it reached the number one spot. I still get comments and messages from people all over the world, thanking me for helping them sort their data quickly and easily. If you've visited before, and are just looking for the script, I'm happy to report that I've made an improvement that removes the generic SharePoint site/Document Library info. The improvement over the original formula was inspired by a suggestion from a LinkedIn contact - thank you for the idea, Brian Farquhar ! If anyone has any suggestions for further improvements or would like to collaborate on any idea, please reach out to me via LinkedIn. Now, on to the article.

Short version (If none of this makes sense to you, jump down to the long version to see detailed info. If you need help, feel free to reach out to me in LinkedIn)

As a courtesy to save you from having to read this whole article (you're welcome!) - If you're familiar with how tables in Excel work, and you already exported from SharePoint to Excel, the short version of the solution is to make a new column to the right of table, use this formula and sort by that column.

=IF(AND(LEN([@Path])-LEN(SUBSTITUTE([@Path],"/",""))=2,[@[Item Type]]="Folder"),[@Name],IF(AND(LEN([@Path])-LEN(SUBSTITUTE([@Path],"/",""))=2,[@[Item Type]]="Item"),"*File at root",IF([@[Item Type]]="Folder",TEXTAFTER([@Path], "/", 3)&"/"&[@Name],TEXTAFTER([@Path], "/", 3))))

New!!! If you've used this before, you'll be happy that I've made an another formula that you'll use in addition to the one above. It shows the folder path of the folder, without including the name of the folder. It's very imporant that you use the first formula and sort by that column. Now make another folder (I called it "Folder / File Location") and use the formula below. If that's confusing, don't worry, you don't have to use it; but if you get used to doing it, you'll see it comes in handy.

One caveat - make sure you name the column in the first step "Index". I'm working on a better formula that will make this work without referencing that first column

=IF([@[Item Type]]="Item",[@Index],IF(AND([@[Item Type]]="Folder",[@Name]=[@Index]),"",TEXTBEFORE([@Index], "/", -1)))

Note - if the first formula above doesn't work, there may be an issue with your original export. In this case, try one of these two alternate formulas:

My original formula:

=IF([@[Item Type]]="Folder",[@Path]&"/"&[@Name],[@Path])

Improved formula that allows you to strip out the site name and document library, provided by Brian Farquhar . You just need to replace the two instances of 38 with the number of characters you're trying to remove. It'll probably be different based on the name of your document library.

=IF([@[Item Type]]="Folder",RIGHT([@Path],LEN([@Path])-38)&"/"&[@Name],RIGHT([@Path],LEN([@Path])-38))

If that doesn't mean anything to you, follow the detailed steps below. I also give tips at the end to troubleshoot any issues that may arise. If you have any problems, or you'd like to collaborate on creative solutions for anything at all, feel free to reach out to me in LinkedIn.

(Extended version)

Are you looking for a quick and easy way to view the folder structure when you export a SharePoint Document Library to Excel? You're not alone. I had also been frustrated by this for a long time. The experience is that I would do an export from SharePoint to Excel, assuming the table should sort by an index showing the folder structure; instead, it just shows all the folders, then all the files; in alphabetical order.

After a whole lot of research, I found many great posts from other people asking if anyone knows of a good way to make it work; but no easy solutions. I did find some excellent posts that explain some technical ways to get close to an index. Some are much better than others, but they all require technical skills that an average user would find too difficult, and / or require a good bit of data cleanup to get any benefit. I actually know of a user who planned to painstakingly cut and paste rows to put them in order because a client needed the index. That's not a good plan because you're likely to make mistakes. I couldn't have my users doing that that, so I had to find or invent a better solution.

After a lot of trial and error I invented a great way anyone can create the index very quickly and easily.

First, export to Excel.

You'll see the exported table will look something like the image below. In the "Item Type" column, you'll see what you've probably come to expect: all of the folders are displayed first, followed by all of the files. If you look a the Path column you can get an idea of the location where each subfolder and file reside, but there's no intuitive way to sort the rows to show the actual folder structure.

I then took a closer look at the Path column, and I realized that with some creative modification, I could leverage that column for sorting. For example in the image below, look at the folder named "Folder 1" in Cell A2, and the file "File in 1.txt" in cell A8. If you look at the end of the path in E8, you can tell that this file would be found in the folder named "Folder 1". In those cells' current state, they can't be used for grouping them together, but if the Paths in cells E2 and E8 were the same thing, then they could be grouped together. That's what the next step does.

Confused? I get it! But stay with me; once you do this a couple times you'll totally understand.

The way to make this work is to add a new column and use a formula I invented. To make a new column, click into the top cell of the column to the right of the last column in your table. In my case, I clicked into F1 and typed the word "Index". You can call it whatever you want. Once you do that, you'll see that that column becomes a part of the table.

In the next cell down, in my case F2, enter this formula:

=IF(AND(LEN([@Path])-LEN(SUBSTITUTE([@Path],"/",""))=3,[@[Item Type]]="Folder"),[@Name],IF(AND(LEN([@Path])-LEN(SUBSTITUTE([@Path],"/",""))=3,[@[Item Type]]="Item"),"*File at root",IF([@[Item Type]]="Folder",TEXTAFTER([@Path], "/", 4)&"/"&[@Name],TEXTAFTER([@Path], "/", 4))))

New!!! If you've used this before, you'll be happy that I've made an another formula that you'll use in addition to the one above. It shows the folder path of the folder, without including the name of the folder. It's very imporant that you use the first formula and sort by that column. Now make another column (I called it "Folder / File Location") and use the formula below. If that's confusing, don't worry, you don't have to use it; but if you get used to doing it, you'll see it comes in handy.

One caveat - make sure you name the column in the first step "Index". I'm working on a better formula that will make this work without referencing that first column

=IF([@[Item Type]]="Item",[@Index],IF(AND([@[Item Type]]="Folder",[@Name]=[@Index]),"",TEXTBEFORE([@Index], "/", -1)))

Note - if the first formula above doesn't work, there may be an issue with your original export. In this case, try one of these two alternate formulas:

My original formula:

=IF([@[Item Type]]="Folder",[@Path]&"/"&[@Name],[@Path])

Improved formula that allows you to strip out the site name and document library, provided by Brian Farquhar . You just need to replace the two instances of 38 with the number of characters you're trying to remove. It'll probably be different based on the name of your document library.

=IF([@[Item Type]]="Folder",RIGHT([@Path],LEN([@Path])-38)&"/"&[@Name],RIGHT([@Path],LEN([@Path])-38))

Tip: When copying / pasting, I find it works better if you click that cell and enter the formula into the formula bar instead of directly in the cell. Then press enter). The rest of the columns will automatically populate. I've put some more helpful tips at the bottom of the article.

If you're familiar with Excel formulas, you might notice that the formula doesn't reference cells, but instead references columns in the table; and since it's a table, all of the rows below will automatically populate. Now that they have this new value, take a look at what it does. For folders, it appends the name of the folder to the end of the path, in the format of "path/name". For items, it just retains the original path (that path is the folder in which the item resides). This allows you to group folders along with the files that are in that folder.

Now sort by that new column A to Z

The result is an index that shows the folder structure. You'll see that any files that are at the root level of your library are listed before any folders, removing any confusion that they might reside in the last folder that's listed.

At this point, you can get creative. You could do conditional formatting to make the folders and items show as a different color so they stand out, etc.

Some helpful tips for entering the formula:

• You might think you could insert a column to the left of the first column, but it won't be part of the table, and it doesn't work as well. Simply putting it on the right side adds the column to the table. You'll see what I mean if you try it.
• Sometimes Excel doesn't play nice and won't change the formula to a value, and doesn't populate the rest of the cells. If this happens, delete the formula from that cell. Right click the letter at the top of the column - in my case, F. Choose Format Cells, and on the Number tab choose General. Then click the top cell and enter the formula into the formula bar.)
• The formula can also be used if you copy the table and paste the values. You'll just need to replace the info in the brackets (including the brackets) with the cell reference. This tip might not mean anything to you if you're not a frequent / advanced Excel user.