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

Installation – Excel 2007 and Later

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

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

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.

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

Installation – Excel 2003 and Earlier

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

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.)

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.

Step 4 – Select the add-in and click ‘OK’ twice

Step 5 – A new menu option is added to the toolbar

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

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.

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.

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

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.

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.

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

Syntax for Calling the Treemap from Other Functions

(If you want to use the shortest form, skip over to thesection 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 tobuy the Treemap utility for Excel. It has been tested to work with Excel versions – 2000, 2003, 2007 and 2010 (windows versions).

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 Exce