Creating a tree diagram from a 5-column matrix

**phrankndonna** · 05-07-2014, 03:09 PM

Matrix to Tree Diagram.xlsx

Hi. I'm hoping someone can help me with figuring out how to convert a 5-column matrix spreadsheet into a tree diagram. I've attached an example of the format for our spreadsheet. There is a main category and 4 potential sub-categories. I've looked into this, and it seems to be along the lines of an org chart type thing, but I can't seem to figure out how to set up the spreadsheet, or if I can just take what's there and do what I need. This spreadsheet will be updated fairly often, so I'll want to be able to convert it to a tree diagram pretty much at will. Is there an embedded Excel feature that might do this that I'm not aware of, or would this possibly take a VBA approach to complete? Thanks for any advice.

Frank

**natefarm** · 05-08-2014, 05:56 PM

Create another sheet called "Diagram" and run the following code. It should work with any number of category columns.

Please Login or Register  to view this content.

**phrankndonna** · 05-12-2014, 02:01 PM

Hi! Thank you for this! It does indeed work to put the data into a heirarchial format within Excel. However, what I'm trying to achieve is the ability to take this data and turn it into a more graphical 'organizational chart' type tree diagram. From what I've been able to find, I can do this using Visio with an Excel workbook, but the data needs to be formatted just so. Please see attached. Our data starts out in the format that I attached in my initial posting. So, is there a way to do a graphical tree diagram presentation in Excel using the data in its initial format?

Thanks.

**natefarm** · 05-12-2014, 02:35 PM

I don't think I can help you in that direction. I'm sure code could be written to use borders to create the lines and boxes, but it would take more time than I have available. Maybe someone else knows of some tools available. Sorry.