tree hierarachy chart

[C V Horie]

I want to create an hierarchy chart, comparable to the 1752 version below.

I have a growing collection of data points which can each categorised into an hierarchy position and level.  Is there a tool in Excel, or elsewhere, that can create such a tree from data in a database?  This is a common format in biological genetics, but I cannot find an MS program that achieves this.

This will be used to define, structure and access a web based data set.  At this stage, I want to use the visualisation to test and refine the data structure.

Top level: one point

2nd level: ca 4 points

3rd level: ca 3 points per level

4th level: up to 900 points per level (though this could be divided into 2 levels of ca 80 and 200 points per level)

5th level: up to 10 points per level

6th level: up to 20 points per level

Thank you

[#2]

Do you have some sample data for us to go on please? Preferably with a picture of the desired outcome. We don't need much, just data for three levels of items with at least one item for each level (preferably more than one).

[#3]

Thank you for your interest.
This is sample of my data and suggested output
"Studiorum proposed draft structure

1. Home page

1.1 Data Standards
1.2 About
1.3 Acknowledgements
1.4 Search

2.1 Catalogues
2.1.1 Liber Studiorum (Finberg)
2.1.1.1 Frontispiece (82 at this level)
2.1.1.1.1 Etching (6 at this level)
2.1.1.1.1.1 Engraver (ca 20 at this level)
2.1.1.1.1.1.15 Digital image ref (ca 2 at this level)
2.1.1.1.2 1st state
2.1.1.2 Bridge and cows
2.1.2 Engraved Works (Rawlinson) (87 at this level)
2.1.2.1 The Copper plate Magazine (80 at this level)
2.1.2.1.1 Rochester (16 at this level)
2.1.2.2 The Pocket Magazine
2.1.2.2.1 The Tower of London (16 at this level)"

An example of the formatting of a tree (I cannot add a screen shot) is at
https://www.ncbi.nlm.nih.gov/Taxonomy/Browser/wwwtax.cgi?mode=Undef&name=Eukaryota&lvl=3&srchmode=1&...
Velson

[#4]

@C V Horie  Possibly you could use the Outline feature with Grouping.  It isn't visually that same tree structure but it does create the grouping/tree branching and even allows you to expand and collapse sections to focus on different areas.  In this thread I also posted a macro that can automatically create that grouping based on a list of tier levels if that helps:

https://techcommunity.microsoft.com/t5/excel/how-to-group-automatically-in-excel/m-p/3411297 

I also created a macro that creates a vertical flow chart/tree structure (part of a safety assurance case I'm working on) based on a pivot table.  It is a bit more complicated but does demonstrate feasibility:

That is actually only the 'top' level and all the bottom boxes link to other tabs with their additional branches.  But based on your link I really think the Outline feature is much closed to what you need and more functional.

[#5]

@C V Horie Does the attached come close? It was created using Insert, SmartArt, horizontal hierarchy.

You change the level of an item by using Tab (indent) or Shift-tab (outdent)

[#6]

@Jan Karel Pieterse

Thank you.  I was out at meetings yesterday.

The Smart looks useful, the Art is fairly redundant.

As far as I can see from the Smart Art help page, all the text needs to be typed into the text pane.

However, I am editing the databases daily (with > 2 million data points) which I should like the hierarchy to reflect.

Can the smart art tool import live data from, say, a spreadsheet? If so, what format and links does that database need to have?

Velson

[#7 · Accepted solution]

You'd need VBA for that. I found a discussion here: https://www.mrexcel.com/board/threads/using-vba-for-smart-art.554224/
But if you're OK with VBA, perhaps you need a more sophisticated implementation, like a treeview?
https://jkp-ads.com/articles/treeview.asp

[#8]

I am very grateful sharing for your wealth of knowledge. It looks as though I need to go off-line for an intensive sharp learning curve of VBA, before my spreadsheets become too difficult to re-jig to link efficiently with the VBA methods.

Thank you
Velson

[#9]

@C V Horie 

The conditional formatting is not the greatest but the basic tree is in place

[I ignored the request for multiple rows because there were no section headings and there was a clash with subsequent numbering]

= LET(
    level, 1 + QUOTIENT(LEN(section),2),
    prior, DROP(VSTACK(0, level),-1),
    row, SCAN(1, level>prior, LAMBDA(acc,s, acc+1-s)),
    location, row & "," & level,
    tree, MAKEARRAY(9,7, LAMBDA(r,lv, XLOOKUP(r&","&lv, location, LEFT(heading,30),""))),
    tree
  )

though in use it would be

= Treeλ(section, heading)