Calculating HTML image maps in Excel

If you create web pages which contain images of charts, HTML Image Maps can be created which will add a number of hyperlinks to defined areas of the image. Users can therefore click on different parts of the image and jump to different web pages or to alternative bookmarks within the current page. Clicking on a particular chart column or legend entry can enable you to drill down to further information about a particular data series.

The following diagram is an example of a chart containing an image map. Hover the mouse over different areas to view labels describing each series. Note that there is a link for each month / column, but not a separate link for each staff type / line within each column.

There are two elements which must be added to a web page to create the image map. The image must be tagged to link it to the map. The code for the map itself must be added to the page head section.

<img alt="An excel chart containing an ImageMap" height="361" src="Exp_Chart%201.gif" width="630" usemap="#data_chart_1">

<map name ='Data_Chart_1'>
<area shape='rect' coords='75, 65, 160, 312' href='#Jan_wte' title='Period 1 data' alt='Click to follow links'>
<area shape='rect' coords='166, 65, 250, 312' href='#Feb_wte' title='Period 2 data' alt='Click to follow links'>
<area shape='rect' coords='256, 65, 340, 312' href='#Mar_wte' title='Period 3 data' alt='Click to follow links'>
<area shape='rect' coords='346, 65, 431, 312' href='#Apr_wte' title='Period 4 data' alt='Click to follow links'>
<area shape='rect' coords='437, 65, 521, 312' href='#May_wte' title='Period 5 data' alt='Click to follow links'>
<area shape='rect' coords='527, 65, 612, 312' href='#Jun_wte' title='Period 6 data' alt='Click to follow links'>
<area shape='rect' coords='146, 320, 255, 350' href='#Ba_wte' title='Bank staff' alt='Click to follow links'>
<area shape='rect' coords='259, 320, 369, 350' href='#Ag_wte' title='Agency staff' alt='Click to follow links'>
<area shape='rect' coords='373, 320, 482, 350' href='#Pe_wte' title='Permanent staff' alt='Click to follow links'>
</map>

The following (Excel Web App) model creates the necessary formulas for generating a map - you can download this sheet in order to view the formulas.

The example above contains a string in cell L5 which is a comma separated list of properties of the Excel chart. This has been produced using a VBA function. The Excel web application does not allow macro enabled sheets and therefore the ChartProps(SheetName, ChartName) function is not active. The VB code for this user defined function is shown here.

Note that the formulas in this model make extensive use of a named range ('Cht_1a') and the INDEX function. To copy this model and apply it to another chart, copy the range L5:Y26. The last column of the first table (Q8:Q25) must then be defined as a new named range. Finally select the range S7:Y25 and 'Find and Replace' all references to the original range name (Cht_1a) with the new name.

A bar chart. Note that there is a link for each location / column, but not a separate link for each specialty / section within each column.

Excel routinely reverses the order of legend entries and so be careful that the formulas in your ImageMap calculations reflect the order of categories in the chart legend rather than their position on a table of source data.

A pie chart with links associated with the segments and the legend items.

The areas have been calculated using a series of triangles to approximate the shape of each pie segment. Using more than one triangle per segment produces a better fit and covers more of the area. The above example uses polygon shapes each based on two triangles.

If an image map area for the 'Nurse' category is based on a single triangle it will be a very flat shape which produces a small plotted area for the hyperlink. A polygon shape calculated using two triangles fills much more of the nursing segment and the triangle sides are  much closer to the edge of the circle. Having at least two triangles is essential if any categories could be greater than 50% or 180 degrees.

The information on this page is not included in the current Excel functions guide.

Click here for details about obtaining the guide.