Excel Treemap Add-In – Documentation

Quick Links

Treemap Installation – Excel 2007 and Later
Treemap Installation – Excel 2003 and Earlier
Usage – How to use the Treemap Utility
Adding Reference to Treemap Library From Other Functions in a VBA Project
Syntax for Calling the Treemap from Other Functions
Easy ways to Treemap Utility with VBA
Change Log
Treemap Examples

treemap_colored_with_node_label

Installation – Excel 2007 and Later

Here are the steps to set-up the treemap maker in Excel 2007 and later.

zipped-treemap-add-in

Step 1 – Extract the contents of the zipped file to a permanent Folder – Once you have downloaded the excel treemap and the ribbon add-ins, extract both the files to a permanent folder.

Step 2 – Open a Blank Workbook and Attach the Add-In – Click on the ‘File’ tab on the Ribbon

Step 3 – Click on the ‘Options’ under the ‘File’ tab.

Step 4 – Click on Add-Ins
how-to-use-the-treemap-as-an-excel-add-in-part-3

Step 5 – Click on the ‘Go’ button

Step 6 – Click on the ‘Browse’ button – Browse to the folder where the Treemap Add-In was extracted.
how-to-use-the-treemap-as-an-excel-add-in-part-5

Step 7 – Select the file and click ‘OK’ – Enable the option that reads ‘Treemap’.

Step 8 – Click ‘Ok’ twice

Step 9 – A new tab has been added to the ribbon
how-to-use-the-treemap-as-an-excel-add-in-part-8

Installation – Excel 2003 and Earlier

Here are the steps to set-up the treemap maker in Excel 2003 and earlier.

zipped-treemap-add-in

Step 1 – Extract the contents of the zipped file to a permanent Folder – Once you have downloaded the excel treemap and the ribbon add-ins, extract both the files to a permanent folder. If you are only going to use this add-in with the Excel 2003 or earlier versions, you may ignore the ribbon add-in.)

how-to-use-the-treemap-as-an-excel-2003-add-in-part-1

Step 2 – Open a Blank Workbook and Attach the Add-In – Click on the ‘Tools’ -> ‘Add-Ins’ option on the toolbar.

Step 3 -Click on Browse and navigate to the folder where the add-in was extracted.
how-to-use-the-treemap-as-an-excel-2003-add-in-part-3

Step 4 – Select the add-in and click ‘OK’ twice
how-to-use-the-treemap-as-an-excel-2003-add-in-part-4

Step 5 – A new menu option is added to the toolbar
how-to-use-the-treemap-as-an-excel-2003-add-in-part-5

Usage

Use the ribbon using Excel 2007 (and later versions) and the menu toolbar in Excel 2003 (and earlier versions) and select from the two options: Simple Treemap or the Change Treemap

excel-2010-ribbon-view

Simple Treemap

This option allows you to create treemaps where similar nodes will be shaded using the same color for easy identification. You can also choose to color nodes at any level of the input data. Bear in mind that the last column should contain numeric and positive values.

Here is a sample treemap created using the simple treemap option.
simple-treemap-sample-small

Change Treemap

This option allows you to create treemaps where nodes are colored based on the criteria specified. The second last column specifies the size of the node while the last column specifies the hue to use. Bear in mind that the last two columns should contain numeric values and the second last column should only have positive values. The last column specifies the color and it goes from dark Red to white and then to dark Green indicating a range from the most positive to the most negative. The last column can be anything that you would like to use – rate of growth, per capita income or anything that can help you analyze the data better.

treemap-configuration-change

Here is a sample treemap created using the change treemap option.
change-treemap-sample-small

Select the data range, choose the options that you prefer and click ‘OK’. The finished treemap will be placed in a new worksheet. Using the options provided, you can choose to draw it square or use best-fit, add names, color, suffix parent node identifier, add or remove headers and modify the position & size of the treemap.

select-range-for-treemap

The last column of the data range has to contain numeric values. The area of each node in the resulting treemap is proportional to the value of the node in the data range.

Call Treemap Function From Other Functions in a VBA Project

You can also call the treemap function from other modules in the project. To access the treemap library, simply include a reference to the CHARTMAKER_TREEMAP library from the VBA IDE.

reference-vba-ide

Check the option box next to CHARTMAKER library. You can now freely use the objects in library.

reference-chartmaker_treemap

Syntax for Calling the Treemap from Other Functions

(If you want to use the shortest form, skip over to the section immediately below)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Dim return_val As Shape
Set return_val = TREEMAP
(
    treemap_range,
    treemap_type,
    squarify_treemap,
    treemap_range_has_headers,
    left_treemap,
    top_treemap,
    treemap_size,
    add_color_to_treemap,
    color_at_depth,
    use_excel_palette,
    add_names_to_treemap,
    name_at_depth,
    treemap_font_size,
    prefix_parent_name,
    use_zero_as_neutral,
    color_for_max_value,
    color_for_min_value
)

The TREEMAP function has the following return value and aguments:

return_val As Shape – The return value for the Treemap function. If the function executes successfully, the return value will be a reference to the newly created Treemap object.
treemap_range As Range – The data range to use
treemap_type As Long – 1 (Simple Treemap), 2 (Change Treemap)
squarify_treemap As Boolean – TRUE (Squarify) and FALSE (Best Fit)
treemap_range_has_headers As Boolean -Specify whether the data range has headers.
left_treemap As Double – Distance from the leftmost edge of screen
top_treemap As Double – Distance from the topmost edge of screen
treemap_size As Double – Relative size of the treemap
add_color_to_treemap As Boolean -Specify whether you want to add color to nodes in the treemap
color_at_depth As Long – Specifies the level of hierarchy at which you would like to add color to nodes
use_excel_palette As Boolean – Specifies whether you want to use Excel’s inbuilt color palette. If TRUE, the Excel’s default color palette will be used. In that case, you can assign your own colors to to the palette (Goto – ‘Tools’ -> ‘Options’ -> ‘Color’ and edit the color palette). If FALSE, a custom color palette will be used.
add_names_to_treemap As Boolean -Specify whether you want to add names to nodes in the treemap
name_at_depth As Long – Specifies the level of hierarchy at which you would like to add names to nodes
treemap_font_size As Long – Specifies the size of the font to use.
prefix_parent_name As Boolean – Specifies whether you want to prefix the parent names to nodes. In some cases, can aid information discovery.
use_zero_as_neutral As Boolean – In the case of a change treemap, specifies whether to use 0 (zero) as the neutral color (white). The two extremes shares are Red and Green.
color_for_max_value As Double – Specifies the color to use for representing the highest value in a change Treemap. (Default Value = 9592886, Max Value = 16777215, Min Value = 0)
color_for_min_value As Double – Specifies the color to use for representing the lowest value in a change Treemap. (Default Value = 192, Min Value = 0, Min Value = 0)

Easy Way to Use the TREEMAP Function

If the above looks daunting, don’t worry. Only the first argument is mandatory and the rest are optional. So you can create a treemap by writing something as simple as :

1
Set return_val = CHARTMAKER.TREEMAP(selection)

or

1
2
'SPECIFY A RANGE AND TREEMAP OF TYPE =2 (CHANGE TREEMAP)
Set return_val = CHARTMAKER.TREEMAP(Range(Cells(1, 1), Cells(50, 7)), 2)

If you want to customize it further, you can add more parameters

1
2
3
4
5
Sub my_own_custom_treemap()
Dim return_val As Shape
'SPECIFY ALL THE PARAMETERS
Set return_val = CHARTMAKER.TREEMAP(Range(Cells(1, 1), Cells(500, 4)), 2, True, True, 25, 25, 400, True, 2, False, True, 1, 8, True, False)
End Sub

You can even omit parameters that you don’t want. So the following is good enough.

1
2
3
4
5
Sub my_own_custom_treemap()
Dim return_val As Shape
'SPECIFY ALL THE PARAMETERS
Set return_val = CHARTMAKER.TREEMAP(Selection, 2, True, True, , , , True, , False, True, 1, , True, False)
End Sub

The treemap_type is either 1 (simple treemap) or 2 (change type treemap). If the function executes successfully, the return value will be a reference to the newly created Treemap object. This allows the developer to use the treemap object and further enhance the functionality of his/her VBA code (such as copy-pasting it to a new workbook, powerpoint slide, resizing the Treemap etc. etc.)

(Please note: In the previous versions, the Treemap returned a Boolean value indicating success (or failure) of the function call. In the latest version, the TREEMAP function returns a reference to the newly created treemap.)

Click here to buy the Treemap utility for Excel. It has been tested to work with Excel versions – 2000, 2003, 2007 and 2010 (windows versions).

Add to Cart

View Cart

Change Log

25-May-2011

Customized Color Options for Change Treemap
The earlier version allowed for variation only between dark Red and Green values while creating the Change Treemap. Changes made in this version allow the user to choose any color to represent the maximum and minimum values. The default colors have also been tweaked to represent deep Blue for maximum and Red for minimum.

TREEMAP Function returns a Shape Object
In the previous versions, the Treemap returned a Boolean value indicating success (or failure) of the function call. In the present version, the TREEMAP function returns a reference to the newly created treemap. This allows the developer to use the treemap object and further enhance the functionality of his/her VBA code (such as copy-pasting it to a new workbook, powerpoint slide etc.)

The function return value is now a Shape object. Hence the correct usage is:

1
Set my_treemap = treemap(selection, 1)

Important: Please note the use of the Set operator

Minor Improvements in Node Labels
Fixed a minor issue with node label placement left-offset in some version of Excel.

Sample Treemaps

Please refer to full size treemap examples

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel


ARCHIVES