Category Archives: Spreadsheet Tricks

Guest Post #1 – Dynamic Funnel Chart

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: 
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  

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

Change the Font Size, Color, and Style of an Excel Form Control Label

Anyone who has used a Form Control Label likely knows its limitations: you can’t increase the font-size, -color, or style.  Below, you can see that these formatting items have been “grayed out” in the Font group on the Excel Ribbon.

To be sure, the Label control has received a lot of flack for these deficiencies.  A look through some Excel forums shows suggestions to use a TextBox shape or an ActiveX Label instead of the hapless Label control.It’s a tragedy since the other form controls are lightweight and easy to use. Some forum posters even said Labels are best used to cover cells you don’t want the user to click. So sad.   

But things are looking up.  I’ve since discovered you can take a boring Label Control from this…
…to this!
How?
As it turns out, Labels take on the text-font and -size features of a referenced cell.  So, to make my label look this this, I simply linked it to a pre-formatted cell, like G2, below.
In fact, this is the same mechanism to link a Textbox shape to a cell.  The difference here is that a Textbox can take an unformatted cell and apply new formatting on the front-end, when it’s displayed to the user.  Above, we see that the cell must first be formatted, then linked.  In fact, whatever formatting exists in the cell when you first make the link, the label will maintain this format until a new link is created.  For example, if I were to change G2 to a black color and a smaller font, the label would not show these new changes (however, it would change its text if I changed the value in G2 to something else).  So to change the Label’s formatting — even when it’s linked to the same cell — you’ll need to click the label, click the formula bar, and retype the cell link.  
Admittedly, everyone else might have already figured this one out.  However, I’m still very excited.  Don’t get me wrong, Textbox Shapes are great, but having too many could become expensive on your spreadsheet, especially if you are constantly updating the screen (how many redraws can your computer handle before things start to slow?).  Labels, as Form Controls, inherently carry less bloat and overhead.  They’re perfect for dynamic dashboards.  

Truncating Long Text to Fit into Cells

Have you had a list of names but found your cell width too small to show every name?  Take a look at this:

I had my nonexistent secretary print out this fake itinerary for me.  Nothing lines up neatly.  Look how some of my events overlap the cell boundary between columns B and C.  My 2012 End-of-world Steering Committee meeting on February 1st, 2013 goes long and is then cut-off by the date!

To fix this, you could have Excel automatically size Column B, but that’s a bit unpredictable to have on a dashboard (what if other content is sized off the screen?).  We’ll just have to accept that we can’t show everything, but we can make it look neater.  We’ll employ some type of text truncation method similar to what Microsoft uses to display the names of files in Windows Explorer: if the text to be displayed is greater than a certain character length, simply truncate and add a “…”.

The “Original Recipe” Method

First thing we’ll need to do is figure out how many characters we’ll want to display before truncation.  There’s really no way to do this without trial and error. But, to make life easier, we can use =LEN(“Your Text Here”) to return the length of your text; or, we can use something like  =LEFT(B3, 4) to return the first four characters of text in cell B3.  Keep trying until you find a good character length.  

Hopefully, you got a good handle on using LEN and LEFT above because we’ll be using them from here on out.  Also, I’m going to use 25 characters as my desired character length before truncation.  Below I use  LEFT to pull the first 25 characters from each event name.

Now we’ll need to know which names to add the “…” to.  Following ExcelHero’s discussion on using IFs, we’ll avoid using one here; not to worry, we don’t really need it.

As it turns out, LEFT can take a zero value character length — it just returns nothing.  So to have Excel automatically return what we want, we’ll do this:

=LEFT(“…”,LEN(B3)>25)

Take a look at that second argument on the right.  It takes the length of our event name and tests if it’s greater than our cutoff length of 25.  If it’s less than 25, Excel will return a zero and display nothing.  If it’s greater than the cutoff, Excel will return a 1 and display the first character in our text.  But there’s something I found out while writing this tutorial, I expected Excel to only return one period (“.”), but instead it returns all three (“…”) even if you only put a 1 in on the right.  I’m guessing that the ellipses are treated as one character; however, I’m dubious that this works in every case on every computer, so to have your formula return three characters or zero, you’ll simply change it to:

=LEFT(“…”,(LEN(B3)>25) * 3)

Now you’ll need to concatenate both strings together using the concatenation operator, &.

Finally, as you can see in cell, H7, there is a space between the event name and the ellipses.  This is because the last character before the cutoff was a space.  To fix this, surround the first string formula with the TRIM function; this will automatically delete any leading or ending spaces.  
Putting it all Together

To finish, we’ll combine all our steps into one formula, like so:

=TRIM(LEFT(B3,25)) & LEFT(“…”,LEN(B3)>25)

To get:
…mmmmmmuch better!

Download my example: Spillover Design Example.xlsx

A Dynamic Dashboard for Project Durations and Costs in Excel

Make sure to checkout A Dynamic Dashboard for Project Durations and Costs in Excel (part 2) for the example file!

Let’s say you are the manager of a portfolio of projects at your company.  While each project under your aegis is different, they have a few things in common; specifically – and for the sake of our example – they all go through (or, are currently in) three different phases, and each phase has a different cost per month, but the cost doesn’t differ between each project.  In our example, the phases are:

Phase Name Cost of each stage per month
1. Planning $250/month
2. Execution $500/month
3. Maturity $125/month

The idea is to capture both the cost in any given month and the overall cost over the life of the project.  For example, you have an IT Upgrades project in your portfolio.  You think that you will spend two months in Planning, nine months in Execution, and one month in Maturity after which the project will be closed.  Thus, the overall cost of your project is expected to be,

Planning @ two months: 2 months * $250 $/month  = $500
Execution @ nine months: 9 months * $500 $/month = $4500
Maturity @ one month: 1 month * $125 $/month = $125
Planning + Execution + Maturity =  $500 + $4500 + $125 = $5125

The problem is that each project is different.  One project might spend more time in the first phase than the other.  Moreover, for some projects you have a good idea of how long they will stay in each phase, and for some you are a bit less sure.  Boy, you say, wouldn’t it be nice to change how long a project stays in each phase to compare costs.  Wouldn’t it be nice to see it on a chart?  Let’s see what we can do.

Using a VLOOKUP might seem like an obvious choice for our solution.  Here’s how to do it.   

The Dynamic VLOOKUP Method

Step 1: Create the VLOOKUP table
Since you know there are three projects, you could encode ids into a lookup table numbers as follows.



Step 2: Create a Reference Table
The Reference Table is a time-series table that holds the numerical references to your VLOOKUP table.  If IT Services has two months of Planning, then the Reference Table will show two 1’s in the first two months, respectively.

Step 3: Create a Values Table to map each reference to its monthly cost using VLOOKUP
Now, you’ll make another table that is essentially a mirror image of your Reference Table. This table, however, maps each reference to the correct cost.  So, for Month 1 in the Values Table, VLOOKUP will use the corresponding reference (1) in the Reference Table to indicate the project is in the Planning phase and the associated cost is $250.

Step 4: Sum the Values Table, then graph

Summary
Our dynamic VLOOKUP() method works: the manager (that’s us) can replace the values in the Reference Table to update how long a project stays in each phase.  We would simply repeat numbers in the Reference Table in the amount of months a project is in a particular phase.  Our Values Table automatically updates based on each change.



So here’s the thing.  I don’t like this method at all.  For one, we need a VLOOKUP in for every month, for every program.  Our example only displayed a few projects in a small time frame, but the real world might have many projects over many years!  That’s a real problem because VLOOKUPs can become computationally expensive as our spreadsheet grows.

And there’s another problem, too.  The manager – that’s us, remember – must hand-jam the references in for every month. What if there are many years to account for?  We could Copy/Paste to make life easier, but this method is so very, very error-prone.  There should be a way for us to simply enter the months a project is in a given phase and have Excel automatically generate all the required reference information.

Let’s talk about how to do that in the next post.

If you’re having trouble with the work above, take some time to see if you can recreate your own version in Excel.  Even if you have followed everything so far, consider the VLOOKUP method and the importance of using numeric references.  They will play a very key role in part 2. 


—–


Now Available: A Dynamic Dashboard for Project Durations and Costs in Excel (part 2)