Excel to Tree template for Excel


This article aims to explain how to take a hierarchy that is stored in an Excel table and convert it into a mindmap.

There is a video version of this article here:

I’m going to show you how to take a hierarchy from a spreadsheet and display it as a mind map using an Excel template that I have prepared for you. Please download the template and install a copy of FreeMind.

Scenario

Before we begin, let’s consider where and when this technique may be useful.

This technique is best applied to data that is stored in an Excel sheet where a single column in each row refers to another row as a parent, or superior, or boss, or superset.

Many structures can be described in this manner, for example:

  • The folder structure on your computer
  • The structure of a website
  • The reporting structure at your workplace

In these examples there are two common points that we are interested in:

  • Each example describes a collection of items
  • Each item is part of a tree-like structure with only one parent

If these common points also apply to your data, then you may be dealing with a hierarchy that we can transform into a mindmap using the technique described in this tutorial.

Use Case

In contrast to other methods that achieve the same outcome, this template allows you to achieve a graphical representation of your hierarchy data, without:

  • Complicated database queries
  • Extensive training
  • Costly program licenses

1. Let’s get started

Let’s open this template and look at the list of managers on the first sheet.

You’ll notice three columns:

  • ID – Contains the manager’s employee number
  • Name – Contains the data we would like to see in our mind map (in this case the manager’s name)
  • Parent ID – Declares which parent each item belongs to

To further understand the Parent ID column, look at the first two items and consider how Ronald Snyder has listed EMP2300 as his manager in the Parent ID column, if you look up EMP2300 in the ID column you’ll find that William Spencer is that manager.

The same concept is applied across the rest of the data to find each item’s parent and trace the whole manager hierarchy.

2. Resolve any errors in your hierarchy

Now that you know that the first sheet is used to input your data, open the second sheet. We cannot move forward until we know that the hierarchy is intact and described absolutely correctly so we use this sheet to validate the input.

You’ll notice two buttons at the top of the spreadsheet, click “Make Report” and the data from the first sheet will be copied over and any issues with the data will be highlighted immediately using conditional formatting.

Since the sample data is accurate, you won’t see any issues. However, if we introduce some inaccuracies and re-run the report you will see something like this:

Each colour refers to a type of error listed at the top of the sheet.

  • Orange is telling us, that we have duplicates and should probably fix that.
  • Blue, green and pink highlight that we are missing required values.
  • Yellow highlight is letting us know that any given item cannot refer to itself as its own parent. That’s just silly.
  • Purple is saying that the given Parent ID actually doesn’t exist.

These are the kind of errors you need to resolve to continue. Be sure to only **fix your data in the first sheet**, the second sheet is a **reflection** of your data only.

One thing you may notice is that if you sort by colour on the Parent ID column, we have one manager, Julie Riley, who has no manager; this is because she is at the very top of the tree. Since this is what we would expect, we can continue.

3. Let the template build the hierarchy for you

Open the third and final tab, and click “Make tree” and you should see the following when the process completes.

This cool little feature has done a couple of things for us:

  • Firstly, the manager names have been indented and ordered according to who they report to
  • Secondly, the full list has been selected and copied automagically in preparation for our next step

4. Prepare the result for FreeMind

There is one more step before we paste our data into the mindmapping program, FreeMind. We need to strip out all of the Excel nonsense that was also copied by pasting into Notepad first.

We need to paste into Notepad to ensure that we are working with a pure text representation of our manager list, so that we are only pasting the simplest possible version of our work into FreeMind.

5. Paste into FreeMind and style as you see fit

Copy all of this text from Notepad and into a new Freemind document and apply styles as you see fit.

Depending on your list size, the resulting hierarchy may appear awkward; in this case simply select “Fold All”, from the “Navigate” menu. This way all items appear collapsed until you open them.

Congratulations, we have successfully transformed our flat uninteresting data into a fun visual mind map to share and explain to others.

Thanks

I would like to thank the following people:

PGC’s sample code

I revised some of this template’s programming logic after reading PGC’s forum post on MrExcel.com which described a more efficient way to traverse a generic hierarchy using VBA programming.

Alex Goodin

Alex published an alternative method of creating mindmaps from an Excel spreadsheet using PivotTables on the MindJet blog. While my technique works for relational structured data, I recommend Alex’s technique if you are transforming a table that has repeating values and you don’t need to maintain duplicate values that share the same parent.