[Tutorial] Create a folder structure in Excel

I am creating a knowledge repository for my team and I wanted to create a neat index file. The aim is to have a tree structure and the end result to be a HTML page. I have started with the HTML Kickstart framework and with some help from the internet, I am in the progress of creating a nice intranet page for the repository (I will probably add a post on my experience once complete). In the meanwhile, I need a temporary index that I can share with others so that they can access documents right away.

I decided to go the Excel way, but without macros or other complex steps. You too can create a basic tree structure for any folder to help you keep track of its contents. So if you have the folder ready and Microsoft Excel is just a double-click away, let’s get started.
1 As always start with a blank new workbook. The screenshots are from Excel 2013, but would work on pretty much every version from 2003.

2 Hide Gridlines. From the ribbon, goto View and uncheck the Gridlines box to remove the grid and be left with a blank white worksheet.
0002-Hide-Gridlines-Excel

3 Adjust Column Widths. Select fifteen columns from C through Q to 4. Here are the screenshots on how to do it.
0003-adjust-column-widths-excel

4 Navigate to the folder of choice and with the Shift Key pressed, right click on an empty area of the folder. In the contextual menu, select Open command window here.
0004-folder-right-click-windows

5 List the contents of the directory (including sub-directories) into a text file by issuing a dir /s /b > tree.txt command from the command window.
0005-capture-dir-command

6 Open the text file tree.txt (it will now be visible in the Explorer window) and copy the entire content. Paste the content into columns A & columns C of the excel worksheet.
0005-dir-result-in-excel

7 Select contents of Column A & Column C and sort them on Column C by alphabetical order.
0007-sorted-data-in-excel

8 Select all cells of Column A and change the font color to White (or the background color of the sheet if you have at some point changed it).
0009-font-color-excel

9 Split data into columns with text to columns by selecting all data in column C and the Text to Columns option on screen. The delimiter is \.
0008-delimited-text-in-excel

10 Select all the cells from column C to Q with data and we shall apply some conditional formatting to make the content look better. From the Home tab in the ribbon, select Conditional Formatting and click on New Rule. From the popup window, select “Use a Formula to determine which cells to format” and enter the following formula =AND(NOT(ISBLANK(C2)),C2=C1) Please note that I have filled data from row 2 and cell selection has started from C2
After entering the formula, click on Format and make the following changes. First, within the Font tab, set Color as White (or the worksheet background color). Next, in the border tab, give a right border of your choice. I have chosen a dotted grey line.
0010-conditional-formatting

11 Here is the resultant screen after conditional formatting.
0012-final-columns-after-formatting

12 You can hide (or even delete) the columns which contain the information upto the current folder. In my example, I was in the FirefoxPortable folder and I could hide/delete all columns that contained D: or Program Files or FirefoxPortable. It reduces clutter and makes the sheet more presentable.
0013-hide-columns-in-excel

13 Create hyperlinks in column B to navigate to the directory/file within seconds by entering the formula =HYPERLINK(A2,8) in cell B2. There is nothing magical about the number 8, it is just that it represents a mouse icon in Wingdings. You can choose your own font and your own text instead of 8
0014-make-hyperlink-in-excel

14 Finally, drag the bottom right corner of cell B2 till the last row with data. This copies the formula in cell B2 making all other cells in Column B a hyperlink.
0015-drag-and-fill-formula

There you go, a navigable Folder structure right out of Excel.

Leave a Reply

Your email address will not be published. Required fields are marked *