An alternative method of creating mindmaps from an Excel spreadsheet using pivotables

  • 4
  • Idea
  • Updated 2 years ago
Recently I posted a response to a question on how to create a map from Excel, outlining a method using MS Access to link to or import a spreadsheet and then creating a report with an outline format which is then imported:

https://community.mindjet.com/mindjet...

Earlier I had also posted a method using Word heading styles to import spreadsheets via a Word table to create maps.

As I noted in post on Access, part of the problem with importing spreadsheets into MM is actually at the Excel end. Typical Excel tables (and many Word tables) involve repetition of the higher-order elements mostly in the first column or two; for example, a set of entries for an organisation's workforce will involve the section name being repeated for each person in that section, usually in the first column.

Faced with a table like this, MM has no way of knowing when each section begins or ends; it needs some sort of outline structure, Word heading styles or a combination of both for the import process to be able to work out which items are main topics, sub-topics, sub-subtopics etc. In effect the methods I proposed in the other post suggested two ways around this problem - either edit the spreadsheet to look more like an outline, or import it first to Word, adjust the table and apply Word heading styles.

Previously I had looked at Excel's pivotable feature but I didn't think it would be able to create an outline to match MM's requirements without a lot of work, given its primary focus on statistical analysis. I've had a closer look and happily it turns out I was wrong; in fact, pivotables are probably the easiest way to turn an Excel spreadsheet or for that matter a Word table into a map.

Method

Consider a simple Excel 2010 spreadsheet table laid out as follows:



Using this as an example, follow these steps:

1. In the Insert tab on the ribbon, go to Tables and create a Pivotable either on this worksheet or another one using the source table. Tick all three fields to add to the pivotable as row labels in the order that they appear in the first table. The pivotable should now appear.

2. Highlight the pivotable and click on the pivotable options and then the Tables and Filters tab. Untick the boxes under Grand Totals. The pivotable should look like this:



3. Click on the first entry in the first field (in this case, "Central" in Branch). Go to the Pivotable Tools tab; "Branch" should be highlighted as the Active field. Click on Field Settings and when the dialogue box appears, click on the Subtotals and Filters tab and select None. Then go to the Layout and Print tab, select Show items in outline form and untick the Display labels form the next field and Display subtotals boxes.

4. Repeat step 3 for the first entry in each of the other fields. The pivotable should look like this:



5. Highlight all of the pivotable except the header row, switch to a new map in MM and paste. The resulting map should look like this:



To import a Word table, simply import it to Excel and follow the steps above.

Uses and limitations

This method is best suited to creating maps of tables of lists and other descriptive rather than statistical information, for example organisation charts from personnel lists, summary lists of report outcomes and recommendations, etc.

The table needs to be well structured, ideally with no blank cells. Obviously topics which are meant to be grouped must be absolutely identical; while the pivotable process can cope with unsorted data, sorting it will help to reveal any discrepancies.

This approach is not ideal for importing a table of tasks, as everything is turned into a topic; it does not provide a facility to import data as task or resourcing information, or text as topic notes.
Photo of Alex Gooding

Alex Gooding, Champion

  • 817 Posts
  • 207 Reply Likes

Posted 6 years ago

  • 4
Photo of Patrick Baker

Patrick Baker

  • 45 Posts
  • 5 Reply Likes
Hi Alex

Great post and I like the logic behind what you have achieved. Do make contact with me as I would like to discuss this approach further with you
Photo of Alex Gooding

Alex Gooding, Champion

  • 817 Posts
  • 207 Reply Likes
Thanks Patrick - as you probably have guessed, I spoke to you a little while ago about ExcelMapUpdater. Anyway, I'll drop you an email.

Cheers

Alex
Photo of Andrew Wilcox

Andrew Wilcox, Champion

  • 1029 Posts
  • 177 Reply Likes
Neat.
Photo of Alex Gooding

Alex Gooding, Champion

  • 817 Posts
  • 207 Reply Likes
Thanks Andrew. I haven't tested this approach with Mindjet 11 yet, but I can't see why it wouldn't work.
Photo of Andrea Cesari

Andrea Cesari

  • 1 Post
  • 0 Reply Likes
Great topic, nice clear instructions, and easy to execute. I've used this to present a visualization of educational standards.
Photo of Alex Gooding

Alex Gooding, Champion

  • 817 Posts
  • 207 Reply Likes
Hi Andrea,

Thanks for the feedback - it seems that there is a wide range of uses for this approach.
Photo of tanmay.bhide

tanmay.bhide

  • 0 Posts
  • 0 Reply Likes
This also works if you have say a tabbed layout.
Col A Col B Col C
Category 1
Topic 1
Item 1
Item 2
Item 3
Topic 2
Item 1
Category 2
Topic 1
Item 1
Topic 2
Item 1
Photo of tanmay.bhide

tanmay.bhide

  • 0 Posts
  • 0 Reply Likes
Aaah, the comment box took out the spacings and tabs :-(
Photo of Patrick Baker

Patrick Baker

  • 45 Posts
  • 5 Reply Likes
Hi Alex - I'll send you the latest version of ExcelMapUpdater. The latest version allows you to work from any Mindjet Map to Excel. We have also integrated the process so that you now work with both your map and Excel data within the same Mindjet Map/ Excel Window. Two new tabs are incorporated in the Excel Ribbon for a-synchronous updating of excel spreadsheet data.
Photo of Aleksei

Aleksei

  • 1 Post
  • 0 Reply Likes
Patrik, ExcelMapUpdater doesn't work under Windows8.1+Mindjet14+Excel2013: when trying to export to excel gives out popup window with the error "The assosiated COM server does not support ActiveX Document embedding". What can be done?
Photo of Patrick Baker

Patrick Baker

  • 45 Posts
  • 5 Reply Likes
Hi Aleksie - Thank you for the heads up. We have tested and found there is  a compatibility issue with MS Office. We are working on this and will post a tweet once an update is released. Best regards Patrick 
Photo of Ewa Mago Lachowicz

Ewa Mago Lachowicz

  • 1 Post
  • 0 Reply Likes
Am I being silly, do I need add-in to be able to import simple excel table ? I've followed all above but when copy and paste into MM I only get a picture of the excel table

Please help

Photo of Alex Gooding

Alex Gooding, Champion

  • 817 Posts
  • 207 Reply Likes

Hi Ewa, are you using MindManager for Windows and are you following all the steps in the process I described in my first post?

I just tested it again and it works fine for me.

Photo of Cain Hill

Cain Hill

  • 1 Post
  • 0 Reply Likes
Hello Alex, this is a fantastic technique for giving more meaning to hierarchical data. Thank you for documenting this for the community.

I used another technique to convert an Excel document to a tree mindmap. I've shared the method here for anyone who wants to check it out:
http://www.cainhill.com.au/blog/excel-to-tree
Photo of Davis Rogers Jr.

Davis Rogers Jr.

  • 3 Posts
  • 0 Reply Likes
Very nice Puzzle solved. I much appreciate you sharing. I think it opens up a new way of creating Dashboards.
Photo of Davis Rogers Jr.

Davis Rogers Jr.

  • 3 Posts
  • 0 Reply Likes
Question? "This approach is not ideal for importing a table of tasks, as everything is turned into a topic" Why do you feel this way, should the "Task Topics" be handled in a different environment? Curious to hear your thoughts. Thanks
Photo of Davis Rogers Jr.

Davis Rogers Jr.

  • 3 Posts
  • 0 Reply Likes
I think what I am envisioning is that you put the "Task Topics" into a Bubble or a Collapsible node., and allow for only single outline be toggled on to reduce clutter. You can then light up a Dashboard for finding things that would be really dynamic.
Photo of Alex Gooding

Alex Gooding, Champion

  • 817 Posts
  • 207 Reply Likes
Hi Davis, I think what I meant in my original post is that if you have a table with the task name, start date, end date etc in different columns MM can't recognise that the data in the date and subsequent columns is task information relating to the task name. Consequently it turns these cells into sub-topics.

However in the couple of years since I posted this I have refined the process considerably and found an approach that forces MindManager to recognise these cells as task information. This involves using MindManager's own Word export styles to format the material you want to import. I describe this approach in the final part of a three-part series on importing from Word which I recently posted on my Sociamind blog: https://sociamind.com/2016/04/21/the-...

This approach should provide a map which shows each row on the imported task table as a task with appropriate date, resource and other fields. You should also have a look at part B in the series which deals with importing non-task-related lists but which also includes some macros and other techniques you will need to set up task reporting as described in part C (part A deals with importing from paragraph-based Word documents).