1. TreeTABLE overview
TreeTABLE is an Add-in (integrated extension software) for Microsoft Excel to create and edit tree structure tables (tree tables). After successful installation of TreeTABLE, TreeTABLE tab displays on the Excel ribbon.
1.2 Columns Manager
Columns Manager is a tool of TreeTABLE, displayed in Excel as a task pane. It allows users to add, remove, change the column or set linked columns of a tree table.
1.3 Tree Table
Tree table is an Excel’s table that displays data in a tree hierarchy structure across the Excel’s columns. In some cases, Tree tables make data easy to read because it not only display data but also express relations in one single table. Some tree tables have become industry standards like Control Plan, FMEA, DRBFM, FMECA, HAZOP.
Note: tree tables is referred to as relation tables, hierarchy table, merged table.
1.4 Tree Sheet
Tree sheet is a worksheet that has a tree table on it.
2. Tree Table Structure
Tree table also has columns, header and data item.
A tree table column is equivalent to a single worksheet column that includes a header and data items below its header.
2.2. Column Relation
There are two types of column relationship.
• 1-n relationship: Two columns have a 1-many (n) relationship means one item of the left column corresponds to many (n) items of the right column. The right column also called child column of the left column like one father/month can have many children but one child has only one father/mother.
• 1-1 relationship: Two column have a 1-1 relationship means one item of the left column corresponds to one item of the right column. The right column is also called a sub column of the left column.
2.3. Column Type
• Root column: the top-level column in the hierarchy of column relationship.
• Master column: Root column, child column of Root column, child column of a Master column. A master column is marked with a green checkmark.
The remaining columns are sub columns of master columns. They are not master columns.
Top cell of a column that has the label for the column.
Header requirements: Not all range can be a header of a tree table column. Please follow below conditions when you setup column relation for your tree tables.
• A header must be single column range.
As below figure, Header 1 is single column range, so it can be a Header of a tree table; Header 2 spreads in 2 columns, so it cannot be a header of a tree table.
• A Header must be a single cell or merged cells.
As below figure, Header 1 is a merge range so that it can be a Header of a tree table; Header 2 is not a merged range, so it cannot be a header of a tree table.
2.5. Data item
Cells under column headers
3. Set up tree table column
Before you can use the quick editing features of tree table, you need to set up its columns. This setting helps TreeTABLE recognize the columns and relation between the columns. Follow the below steps to complete setup columns of a tree table, remember you only have to do this one time only.
3.1. Convert to Tree Sheet
Before creating a tree table in a worksheet, you must convert the worksheet to tree sheet. To convert a normal worksheet to a tree sheet:
• Open the worksheet you want to convert.
• Click Convert To Tree Sheet.
Note: Each tree sheet has only one tree table.
3.2. Set Root Column
To set the root column of a tree table, please follow the steps below:
• In the Columns Manager, click Set Root.
• When Column Header Selector appears, select the header of the column and click OK. Please note that the header must satisfy Header requirements.
• Columns Manager displays the root column in the tree view.
Note: A tree table has only one root column.
3.3. Add Child Column
To add child columns to an existing master column, please follow the steps below:
• In Columns Manager, select given master column and click Add Child button.
• An Column Header Selector appears. Select the header of child column you want to add and click OK. Please take note that the header you select must satisfy Header requirements and it is not another column header.
• If nothing goes wrong, Columns Manager will display a new child column.
Note: To prevent complicated structure of a tree table, you need to keep each master column has one child column only.
3.4. Add Sub Column
To add a sub column to an existing master column, please follow the steps below:
• In Columns Manager, select the master column and click Add Sub button.
• An Input Box appears. Select the header of sub column you want to add and click OK. Please take note that the header you select must satisfy Header requirements and it is not another column header.
• If nothing goes wrong, Columns Manager will display a new sub column.
3.5. Exchange Column
You may want to change the column of a tree table to another column of the worksheet. To do that, please follow the steps below:
• In Columns Manager, select the column you want to change, then click Change Column Header.
• When Input Box appears, select the header of the new column and click OK. Please take note that the header you select must meet Header requirements and it is not another column header.
• If nothing goes wrong, Columns Manager will update with a new column.
3.6. Remove Column
You may want to remove a column out of the tree table’s columns. Remove column allow you to remove tree table column but still maintain all content (header and content)
To remove a column, please follow the steps below:
• In Columns Manager, select the column you want to remove and click Remove button.
Note: when you remove a column, its child columns and sub columns are also removed.
3.7. Convert Tree Sheet to Normal Worksheet
You may want to convert your tree table to a normal table but keep all content without change. Convert To Normal Sheet allows you to do it with a click.
• Open worksheet of tree table that you want to convert.
• On the TreeTABLE tab, in the Setting group then click Convert To Normal Sheet.
Note: After converting, you cannot use the editing functions of TreeTABLE
3.8. Importance note to maintain a Tree table
TreeTABLE uses Names of Excel to define its columns’ Headers; these Names start with “QATLN” and set hidden to the user in default. You should not unhide or change it and define any new Name that starts with “QATLN”. If you do that, the tree table will be corrupted.
Before you start to convert your Excel Worksheet to a tree table, please make sure that there is no existing Name whose name begins with “QATLN”.
TreeTABLE also uses Custom Properties of Excel to store it setting data. These Custom Properties has name start with “QAT”, those are hidden to the user in default and does not affect to your regular use. So please do not change or delete them.
4. Editing a Tree tables
Before you start editing a tree table, you need to set up tree table column for it.
4.1. Insert New Item
You can insert a blank item below the active item on a tree table. When you insert a blank item, TreeTABLE will create a new row for the new item but keep all another non-related item
To insert a new item below a given item:
• Select the given item.
• On the TreeTABLE tab, in the Editing group then click Insert Item.
You can also right the selected item and then click TreeTABLE: Insert Item
Tip: For quick inserting in action: On the TreeTABLE tab, in the Editing group, select Enter To Insert. Now whenever you press Enter key in the keyboard, a new item will be inserted under the current item.
Note: Please note that TreeTABLE will not insert new item when Excel is in edit mode. Example: when you are typing cell content.
4.2. Delete Item
You can delete an active item on a tree table. When you delete an item, TreeTABLE will delete the item you selected and its master item. Note that when a master item is deleted, its child items and sub items also are deleted. The non-related items in the tree table will not be affected.
To delete an item:
• Select the item you want to delete.
• On the TreeTABLE tab, in the Edit group then click Delete Item.
Tip: You can also right the selected item and then click TreeTABLE: Delete Item
Note: Pressing DELETE only deletes selected item’s contents, does not delete the item itself,
4.4. Fit Content of items
Excel also has an autofit function. However, it only works with single cells but does not work with merged cells. TreeTABLE adds auto fit features for all items in the tree table. When use fit function, TreeTABLE will change the rows’ height to fit contents of selected items.
To fit contents of given items:
• Select the items that you want to apply fit contents.
• On the TreeTABLE tab, in the Edit group then click Fit Items. You can also right-click the selected item and then click TreeTABLE: Fit Item
Tip: You can also item content fit whenever you change it by select Auto Fit on the TreeTABLE tab.
4.5. Copy Formula in a column
Excel copy function also works with formula with normal table, however with merged cells as tree table, this copy function does not work as we expected. TreeTABLE allows copying the formula of the top item in the table to all other items below it in the column of a tree table.
To copy the formula in a column:
• Select the first item in the column. This item should have a formula.
• On the Tree tables tab, in the Edit group then click Copy Formula.
Note: The first item in a column is the item just below the header of the column.
4.6. Search item in a column
This function looks up all item in a column and displays only the match items in a column. When you search items, TreeTABLE will hide the item that does not include the text you search and unhide all items that include the text you search
To search item in a column:
• Select the Header of the column you want to search.
• On the TreeTABLE tab, in the Edit group then click Search.
• An Search Text dialog box opens and requests you to input a search text, please type the search text, then click OK.
Tip: To clear the search, click Clear Search on the TreeTABLE tab.
To show the items that are partly hidden by the Search function, you can right-click the item and then click TreeTABLE: Show Item.