May 20

Guest Post #1 – Dynamic Funnel Chart

0  comments

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

by
Bert van Zandbergen
The Netherlands

Based on this file: 
bert1
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/ 

7be1b bert2
Figure 2

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

c5fca bert3
Figure 3
Formula: Define the hotspot with the name: “ valSelOption” 
75b95 bert4
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. 
72e00 bert5
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  

ebf10 bert6
Figure 5 — The “hotspot”

Above the hotspots and the linked cells. Special formed to make an easy crossover.

***
A big thank you to Bert – hopefully there will be more contributions in the future!
Do you have something interesting to share? Send me an email or drop me a line on LinkedIn. 

Tags


You may also like

December 20, 2013

100+ Best Excel Resources And Websites

August 6, 2019

How to Create a Relative File Path in Power Query

April 9, 2011

How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell

September 17, 2012

The Excel Rollover Mini FAQ

February 17, 2014

It’s time to say “Goodbye,” to Hungarian Notation
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Free Power User Quick Guide

Discover The BEST Ways To Use Lookups And Conditional Calculations Quickly And Easily With This Reference Guide -- You Won’t Want This To Leave Your Side

With so many ways to use Excel, it can be difficult to memorize all of the key functions, calculations, and techniques you can employ to meet your goal: simply get the job done.

>