Guest Post #1 – Dynamic Funnel Chart

May 20, 2012
/ Guest Posts Rollovers Spreadsheet Tricks VBA
/ By Jordan Goldmeier

Reader, Bert van Zandbergen, from Beekbergen, The Netherlands (Holland) sent me his own cool creation based on the Rollover technique I created. I asked if I could post his work to this blog to which he graciously agreed.

Below is his write up. Some of the Excel functions below are in Dutch (which I think is really cool). If you’re confused, download the file first – your version of Excel will show the functions in your language of choice. And, of course, if this technique is new to you, read the the tutorial on how to do Excel rollovers.

***

Dynamic Funnel Chart

Bert van Zandbergen

The Netherlands

Based on this file:

presentatietechniek-25-pipeline-2012.xlsm

Figure 1

Start with the 4 columns. The dummy had to be 100 or more for space to unfilled dummy bars. Delete the lines. Now you can change the value from “100” to “1”. Further information is visible in Figure 1.

Hyperlink:
Read also the information and explanation on the website of Chandoo.org
http://chandoo.org/wp/2011/07/20/interactive-dashboard-using-hyperlinks/

Figure 2

Place a hyperlinks in the cells of the hotspot – see above.

Figure 3
Formula: Define the hotspot with the name: “ valSelOption”

Figure 4

Go to VBA and insert a module. For more information about the instruction – see on this website, the Chandoo website and the module above.

Figure 5 — Chart with hotspot

The hotspot is based on 10 columns combined with 42 rows. – see figure 5/6. The hotspots are linked by hyperlinks with corresponding cells in columns AI:AR. For a special effect and an easy crossover the values are placed in a diagonal figure

See: Figure 5/6