Everyone! We've moved to WordPress.

Loading...

The Excel Rollover Mini FAQ

26
As more people are use the rollover method (or “Interactive Hyperlinks” as Chandoo calls them), I thought it might be helpful to answer common questions I've seen popping up on forums. So, in no particular order, I present the Excel Rollover Mini FAQ!

1. Are there example articles and spreadsheets on how to use Excel rollovers?


Here’s a list: 
Articles and Tutorials
How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell by Me
How to: highlighting cells using the rollover technique in Excel by Me
Interactive Dashboard in Excel using Hyperlinks by Chandoo
Needs More Rollover: Quick Tip! by Me
Une macro sensible à la souris by Monsieur Excel (this one is in French!)
Handling Rollover Clicks Without Using the Worksheet_SelectionChange Event by Me
Roll Over Tooltips and Web Actions on a Microsoft Excel Dashboard by Robert Mundigl of ClearlyAndSimply.com
Guest Post: Rollover for Months and Years by Bert van Zandbergen (klvzndbrgn (at) gmail )
Dynamic Funnel Chart by Bert van Zandbergen


Related
Investigations in "Illegal Activities" with User Defined Functions by The Frankens Team

If you know of others or would like me to add an informative article you've written, go ahead and email me a link. 

2. Do rollovers work in Excel 2003 and in versions previous?

No, I don't think so. I haven't tested this for myself, but comments on other blogs suggest that the method only works for versions 2007 and 2010. Excel 2013 is not yet out, so while I think it will work in that version, we will have to wait and see for ourselves.

[updated 9/21/2012]

Maybe. I might have been wrong about what I said earlier. You see, I've always used IFERROR to surround the HYPERLINK formula, but I forgot IFERROR didn't exist for Excel until version 2007. This formula by Zoran Stanojević might conceivably work in Excel 2003:

=REPT("",ISERROR(HYPERLINK(getCoordinates(....),"")))
Additionally, Zoran writes:

I always avoid version specific functions when they are unnecessary because they potentially weakening the universal solution.

That's an incredibly good point. Still, I'm probably not going to give up IFERROR anytime soon :).

But, if you are you are employing the rollover method on your own spreadsheet--and you're concerned with backward compatibility issues--definitely consider using his formula above. Then come back and share the results with your good buddy, Jordan.

Also, I had an email conversation with someone recently who attempted to run my introduction to the rollover method file, "Snakey", in Excel 2013 preview. He said the file eventually crashed Excel 2013. However, it was the preview version, so whether the rollover method works in the final version still remains to be seen.  For this blog's sake, I hope the rollover method works correctly in Excel 2013.

3. Why/how do rollovers work? I thought User Defined Functions could not change other values on a spreadsheet. I've also heard this functionality doesn't exist in Excel.

As far as I can tell the rollover method shouldn't exist. I think it works by taking advantage the Hyperlink formula in a way unforeseen by the original developers. Intentional or not, the functionality I believe has opened a door to some cool new possibilities with Excel. 

I can only speculate why rollovers work the way they do. My best guess is that when your mouse floats over a hyperlink, Excel goes out behind the scenes to check if the hyperlink exists and loads the address in memory in anticipation of a click. This piece of memory probably goes into a generic callback procedure which knows how to handle the different actions that fill the callback queue. Because a User Defined Function is supplied instead of a URL or reference on the spreadsheet (as is normally the case when use Hyperlink) the callback function treats the UDF like any other and executes it immediately without knowing that it came from the Hyperlink formula. 

I could be wrong about all of this, but that's my (rather uneducated) guess. If any of you know better, I welcome your feedback. If I'm wrong, let me know. My feelings won't be hurt. 

4. Is there a way to handle multiple rollovers on the same sheet?

Yes. Simply create another rollover function in your module and point it to the new rollover function in your module. As far as I can tell, there is no limit to how many rollovers your spreadsheet can employ at any given time.  

5. When my mouse is over the hyperlink, Excel continuously fires the macro which is slowing everything down. Is there anything I can do about this? 

Yep, try this.

6. I want the entire cell to become a rollover hotspot, but the user defined function only fires when my mouse is on the text of the hyperlink - how can I make the entire cell a hotspot?

Enable wordwrap in each cell containing the rollover method.

7. My rollovers used to work perfectly, but now they don't work at all! Recently I added some formulas like INDEX, VLOOKUP, and SUM to the hyperlink rollover formula. Now everything appears without error, but the macro no longer fires. What happened? I need to make my formulas dynamic!

Rollovers appear like regular formulas but they are not. When you add other formulas to them, they tend to not work as planned (they may work if you select the cell and hit Enter - but that's not what you want). However, you can make your rollover functions dynamic, but you cannot do it by adding formulas directly to the rollover formula. 

Here's what's I've found. Apparently, rollover formulas work exactly like references used for dynamic charts, dynamic labels, and form controls. Let's take the chart example. When you want to make a dynamic chart, you must have your chart series refer to a range containing the dynamic data. You can't place an INDEX function directly into your chart's SERIES function, but you can have your chart point to a set of data that uses the INDEX function. You'll have to create the same mechanism when using rollovers. So, if you want to make your hyperlink formula change its "friendly text" based something the user does, place a reference to the range with your dynamic content in the friendly text parameter of the HYPERLINK formula and the return error parameter of your IFERROR formula. 

It's really no different than what you would do when working with a dynamic chart. But unlike in charts and form controls - Excel won't give you a warning when you've placed stuff into the rollover formula that shouldn't be there - instead, your rollovers just won't work. 


8. How do I handle clicks on my Rollover cells?

You can use the Worksheet_SelectionChange event, but I prefer this method.

9. Do rollovers work on non-Windows machines, like Macs?

I don't think so. Rollovers require Visual Basic for Applications and the latest versions of Office for Macs don't include the ability to create or run macros, from what I'm told. 

If you're a mac user and have success using the rollover method, I'd certainly love to hear from you about it.

Can I add to this list?

Sure! Email me or post thoughts, examples, advice, etc. 

Interactive Map in Excel using Rollovers

10
Alright, so this seemed like the next logical step for the rollover method:

Unable to display content. Adobe Flash is required.

This one is kinda complicated, I admit. Unfortunately, I didn't really take the time to clean up the spreadsheet file for others to follow (I don't really have the time these days). Sorry. But try to take it apart - and ask me questions if you have them.

I've canvassed some other folks from the Excel community to see if they would want to do a video tutorial of this - and I think that's what it would take.

Have fun!
Mapper.xlsm

Details on Demand: Bring up details on a graph with Excel rollovers

4
So, as usual, I've been experimenting more with the rollover technique. Last night, I started experimenting with adding the capability to rollover a point on a chart (instead of just a worksheet cell) to see if I could have a label appear - or just some event fire - upon the mouse entering a chart's datapoint's "hotspot." The goal was to improve upon previous attempts to provide details-on-demand for charts. Such attempts usually required that you create a reference to a chart object and use chart sheets. Personally, I don't like chart sheets.

In the end, I moved beyond just firing an event when your mouse hovers over a data point; instead, I created the functionality to select a rectangle of data points to display information about them. See:

Unable to display content. Adobe Flash is required.

Because I'm now writing a book, I don't really have time to go through what I did at length. But here's a short summary.

1. I first made every cell into a square of the same size, which you can learn how to do by reading my "Most Squares Method."

2. With each cell the same size, I now had a grid that I could turn into"hot spots" for the mouse rollover. I laid out my chart to use 36 (0 - 35) squares horizontally and 17 (0 - 16) vertically. There was really no good reason for why I picked these numbers; my choice was pretty arbitrary. However, for this method, the more squares you use, the more hotspots you create; thus, more squares means more precision.

3. I then created a mouse rollover technique to capture the row and column numbers set above. Using these numbers, I mapped them on to the charts grid (so for row two, I would do 2/17 * y-axis value to get an approximate mapping). With these mapping I could approximate where the hotspots would light up certain points that were within them

4. I didn't want the user to be able to click on the graph and change its values. So I took a shape and placed it on top of the graph and assigned it to fire a macro on click. You can't see the shape because the fill color is fully transparent. Reread that last sentence and note that I did not say I used "no fill color." When you assign a macro to a shape, if you select "no fill," Excel lets you select anything that is contained by the shape as if it's not there. That would mean the user would be able to select the graph, which is exactly what I didn't want. For the shape above, I simply set its transparency to 100%.

 5. Finally, to make a long story short, the shape when clicked fires the macro that allows the user to draw the rectangle.

I know right now my work isn't perfect (some data points aren't selectable for example depending upon where you start the rectangle), but I like what I have now and don't have much more time to work on it. I'll leave it to you to put it to good use.

Let me know if you make something cool.

Download File
Details on Demand Rollover.xlsm


Guest Post: Rollover for Months and Years

0
Reader Bert van Zandbergen sent me a cool modification to the instruction file in How to: highlighting cells using the rollover technique in Excel to include years and months - not just rows and columns. He writes:

I am an enthousiast reader of the blog 'Option Explicit'. I changed the formula (see below) and made a version for showing Years and Months. Now you can use this tool for Management Information. This fantastic tool, combined with Named Dynamic Ranges gives the ultimate solution for making awesome interactive dynamic Excel charts. As soon as possible I will show you an example.           

Bert van Zandbergen, Beekbergen  - The Netherlands 

You can download the file, here: Rollover_MI model version 1.xlsm

Thanks Bert!

Needs More Rollover: Quick Tip!

2
More people are interested in Excel mouse rollovers, which I think is great. I'm especially enthusiastic about Chandoo's latest dashboard contest where I found out that several contestants used the technique! By the way, there are many great dashboards showcased in the contest. Take a look and make sure to vote!

So here's the tip. If you're familiar with the rollover method - and you should be by now, it's like all I ever blog about these days - you may have noticed that while your mouse is over a cell, Excel is continuously firing the rollover method. For example, let's take a look at this snippet from Chandoo's blog:

Public Function highlightSeries(seriesName As Range)
Range("valSelOption") = seriesName.Value
End Function

If you place your mouse over the cell with the HYPERLINK formula that calls this function, the range valSelOption will be continuously written to. This becomes a problem when there's a lot of complex interaction on your spreadsheet. If, for example, you've used a lot of volatile functions (like OFFSET or VLOOKUP), continuously writing to the sheet will mean a recalculation for each cell with the volatile function. Nobody likes a slow spreadsheet. 

The incredibly simple fix to our problem comes from the wonderful book, Professional Excel Development, by  Bullen, Bovey, and Green in their chapter on spreadsheet optimization and speed tricks. Simply test if you're rewriting the same value over and over again:

Public Function highlightSeries(seriesName As Range)
If Range("valSelOption") <> seriesName.Value Then Range("valSelOption") = seriesName.Value
End Function

In the above code, I test if valSelOption already equals seriesName.Value. If it does, then we do nothing; if not, we write to it so that the next time the function is called with the same value in its argument (which is unpreventable if your mouse is over a cell for even a brief moment second), we can again, relax and do nothing.

Thats it! You'll likely see an immediate speed improvement, especially if you're doing lots of complex stuff with your rollover, like a this.

Interactive Periodic Table of Elements in Excel

46
I've been on a real rollover kick lately. I'm really trying to figure out if it can be useful. Earlier today I started making a Periodic Table of Elements using Excel. I employed the rollover technique to allow the user to gain information about an element simply by rolling over a cell. Well, for some reason, I couldn't stop there. So what was meant to be a small project ballooned into something larger. Unfortunately, my sticking to good coding practice didn't keep up with craving to do more. So what I present to you below isn't really a polished product. If you poke through the named ranges and the rollover indexes, you'll probably see that I add and subtract one to them somewhat randomly (a cheap trick - this is  due to my trying to reconcile the table copied from Wikipedia with my indices).

As you can see below, you can not only gain information about an element but you can also toggle on and off different element groupings.

Unable to display content. Adobe Flash is required.

If you want to "crack" the file, the first thing you'll need to do is reset the ScrollArea (Click on a cell. Go to the Developer tab, click Properties. Delete the reference in the ScrollArea box.). Then just unhide everything.

Good luck.
Periodic Table.xlsm

Update -
Reader Dario found an error in the spreadsheet (see the comments) - this is the result of some carelessness and cheap tricks on my part. An updated version will be released tonight. In the meantime however, you can still poke around the file :).

Another Update -
I've since fixed the bug described in the reader comments. If you find anything else, let me know!

How to: highlighting cells using the rollover technique in Excel

8
In my last post I used my patented rollover technique to create an effect similar to the one shown below:

Unable to display content. Adobe Flash is required.

Neat, huh? When you place your mouse over a cell, it changes color to show that you are selecting it. It's a true "rollover": no mouse-clicks required. In my last post, I show how that might be useful.

Wondering how I did it? Checkout this file:
Rollover Surprise.xlsm

Rollovers for Gantt Charts

2
Before we start, you might have noticed some renovations on this blog. Yeah, it was time for a change. I'm working this template out, so sorry if some of the formats seem off. Also, I have started a new facebook page. I'll be keeping it updated with stories from my favorite blogs. Also, you can post questions and stuff for me (or others) to take a crack at. Plus, you'll see that I'm just a normal average guy. So if you have a facebook, you should join. You really don't have a good excuse why not! :)

DOOOOOOOOOOOOO IT!
Option Explicit VBA | Promote Your Page Too


***

In early last year, this humble blog had very little traffic. All of that changed in April of last year when I posted an exciting discovery: the Rollover technique. This technique was written about by the wonderful Purna of Chandoo.org. At the time, I was super excited about all of the possibilities of this technique. Now, just about a year later, I'm unsure whether anyone (besides me) has really ever used it. I even tried to incorporate it into my last project, but I found my clients were all too used to clicking on a cell rather than rolling over one. In the end I removed the rollover and replaced it with a click. Perhaps the rollover technique is destined to be a cool gimmick and not much more.

But I'm not giving up. Reader Bert van Zandbergen has been sending me many fine spreadsheets featuring this technique. His enthusiasm is encouraging. So I give you a rollover you can use for your Gantt Charts!
Unable to display content. Adobe Flash is required.

Download the fun, here:
Gantt Chart Surprise.xlsm

To see my intermediate table, scroll to the right of the dashboard. Sometime in the short future, I'll post a how to make the highlighted cell stand out with a border. In the mean time, remember to go to Facebook and like this page. Seriously, what are you waiting for? 

Spreadsheet Guidelines and Best Practices

0
Today, I found this journal article, The role of OR [Operations Research] specialists in ‘do it yourself’ spreadsheet development, from the European Journal of Operational Research, by John S Edwards, Paul N Finlay, John M Wilson. The article outlines 20 Guidelines for Spreadsheet Development and 21 Best Practices Points, which I excerpt below.

Guidelines for Spreadsheet Development

Fig. 2. Guidelines for spreadsheet development.

Best Practices for Spreadsheet Development

Fig. 3. Best Practices Points. 
My favorite point is #14 from the list of Best Practices: 

Never input the same piece of data more than once.

This rule reminds me of the 17th rule of composition from the Elements of Style: "Omit needless words." Perhaps Best Practice-point #14 can be rewritten as "Omit needless data." 

_____
Figure 2 and Figure 3, from John S Edwards, Paul N Finlay, John M Wilson, The role of OR specialists in ‘do it yourself’ spreadsheet development, European Journal of Operational Research, Volume 127, Issue 1, 16 November 2000, Pages 14-27, ISSN 0377-2217, 10.1016/S0377-2217(99)00331-8.

Guest Post #1 - Dynamic Funnel Chart

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

Easy One-Way Sensitivity Analysis on Weighted Sum Models in Excel (Part 2)

0
Wow, it's been a while since my last article. I've actually been pretty busy the last few weeks putting the finishing touches on an Excel deliverable for one of my company's clients. We delivered the product about a week ago and things have just now started to slow. So now it's back to blogging.

If you've been following so far, we left off with Easy One-Way Sensitivity Analysis on Weighted Sum Models in Excel (Part 1). In that article, I describe a method for automating one-way sensitivity analysis, and, in Part 1, we construct the mechanism that drives the automation. However, where we left off, our sheet was a bit ugly. Sure, the mechanism worked, but it wasn’t a dashboard. We’ll talk about making a dashboard in this article, Easy One-Way Sensitivity Analysis on Weighted Sum Models in Excel (Part 2). If you’re scratching your head at this point (“what the heck is sensitivity analysis?”), go ahead and start with Part 1. There, I give a brief introduction to the mathematics and rational behind sensitivity analysis (and the weighted-sum model) before going into the Excel stuff. Also, these tutorials are a bit on the long side, so you may just want to do the analog thing and print them out instead of following along on here. 

I’ve been scratching my head as to how to segue from my last article to this one. I’ve decided to do what good chefs do and start with something already prepared. So go ahead and download this file: Healthcare Sensitivity Analysis Example.xlsx. You’re welcome, of course, to recreate the layout of that file, but I’ve done the heavy lifting for you. The core difference between this file and the one you created in the last blog post is that I’ve added many more countries (with fake data). Let’s get the lay of the land of this new file.

If you start with the Data tab you’ll something similar to our last blog post.


The table on the left isn’t all that different from the last file. If you play with the scrollbars, you’ll see that they work exactly as they did before. In the table to the right, each metric has more information broken out than in our last spreadsheet, but the mechanism we created before is exactly the same here (notwithstanding the extra information). Note, too, that the final weighted scores have been moved the front (that is, to the first column on the left) of the final table. You’ll see why at the end of this tutorial.

(The reason more info is broken out is because that info is used in the Healthcare Analysis dashboard in the Fun Downloads section. We won’t use those extra columns here, but if you get the hang of this stuff, consider creating a dropdown to individual weighted scores using that extra information. I might write about how to do that in a Part 3.)

Now click the Example tab. You should see the barebones of our dashboard. If you scroll all the way down (or zoom out) you should see another table below the dashboard called VLookup Table.

Dashboard 

Intermediate Table

This table is an important, if unnecessary, intermediate step. I say unnecessary because you could go without out, but I wouldn’t recommend it; the table makes life much easier. So, this is my advice: whenever you make a dashboard, you should have an intermediate table off-screen that summarizes most of the selection and number crunching for you. For the computer science and java geeks out there, think of this table as part of the model-view-controller framework. The dashboard is our view, or “reporting” layer – it reports data to the users, but it doesn’t allow them to make specific underlying changes to it (they can only change how the data is shown to them). This intermediate table acts kind of like a controller. It handles input stuff like scrolling, and it’s usually only for the Excel developer and not for the client or front-end user (that’s why it’s off-screen). Finally, the underlying data on the backend is our model. I admit that’s a pretty rough explanation, but it serves this very fundamental point to dashboard creation: you should separate the reporting, event handling, and underlying data areas on your spreadsheet. This separation mitigates damage caused by underlying and user-created errors and separates the logic of your work into distinct modules. In other words: it makes life easier.

Let’s go.

Step 1: Link the percentages from your data to your dashboard. 


Start by selecting cell I3 on the Example tab and link it to cell E4 on the Data tab, which holds its corresponding weight. Then go across the boxes on your Example tab and ensure that each cell is linked to its correct proportion on the data tab. Your numbers might be different from mine above, but if you do everything correctly, your mapping should look like this:
Health Level                → Data!E4
Responsiveness              → Data!E5
Financial Fairness          → Data!E6
Health Distribution         → Data!E7
Response Distribution       → Data!E8
It’s always good to double-check to make sure your references are correct. Check twice, reference once.

Step 2: Copy the scrollbars to your dashboard. Resize accordingly.

Remember those scrollbars from the last blog post? They’ll come in handy here. Go to the Data tab. While holding down Ctrl, select all five scroll bars by clicking each one individually. Press Ctrl+C to copy them. Next, go to your dashboard on the Example tab. Press Ctrl+V to paste to your dashboard.

You’ll now need to adjust the size of each scroll bar – then position it next to each number as shown below.
 

Pro Tip: You can make your life easier by selecting the first scrollbar on the left (select it with a Ctrl+Left-Click) on your dashboard. Move the control on top of, or near, the Health Level box and adjust it to the desired height. Use the arrow keys on your keyboard to fine tune its placement (you may need to adjust the height one more time). When you like the scrollbar’s size and position, right-click and select Format Control. Select the Size tab. Take note of its current Height and Width (write it down if you need). Click OK.

Now select all of the remaining scrollbars using the Ctrl+Left-Click as you did in Step 1. Right-click any one of the selected scrollbars, then select Format Control. In the Height and Width boxes enter the information you just noted. Click OK. Each scroll bar is now the right size. Move each scrollbar next to its associated number. Use the arrow keys to fine tune if you’re neurotic (like me!).



Lookin’ good….

Step 3: Create the scrolling indices to show each country.

On the Example tab, scroll all the way down to the VLookup table. In cell F32 of the Example tab you should see only the value “1” alone, by itself. In the cell below the 1 (F33), type =F32 + 1.

Hit Enter. Now drag cell F33 down until you reach the value of 15. Next, go to the Developer tab and insert another From Control scroll bar to the left of the column of numbers we just created. Just like in Step 1, we’ll link this scroll bar to a specific cell on the sheet; in this case, we’ll link it to the cell that held the 1, cell F32. So right-click the scroll bar, then select Format Control. Click the Control tab. In the cell-link box, select (or type) F32. Click OK.

Press the up and down arrows on the scroll bar to see the indices change.

Step 4: Use the indices to pull the final weight values from the Data tab.

Here, we’ll use the scrolling indices to pull information from the Data tab. We can use the Large() formula to ensure the data we pull is sorted. Use the scrollbar to scroll up so that there is only a 1 in F32. (If you see a zero in F32, you’ve scrolled too far. Scroll up one.) Now, in the cell to the right of it, type "=Large(". Now select the entire column of Final Weights from your data tab, Data!G4:G53.


Hit F4 to make it an absolute reference. Type a comma to go to the next parameter. Now, go back on your Example tab, select the 1 (cell F32) to the left. Your formula for F3 should look like this:
=LARGE(Data!$G$4:$G$53,Example!F32)
What we’ve told Excel to do is pull the greatest value from the set of Final Weights. Hit Enter.

If we drag that formula all the way down, we are then telling Excel to pull the second largest value, third largest value, and so forth. Thus, the resulting values in the Largest Values column are always nth largest value, where n is the number in the cell to the left. That’s how we make an automated sorted list. 

Step 5: Fix the Scrollbar minimum and maximum values.

If you play with the scroll bar, you’ll see that scrolling all the way up or scrolling up the way down results in #NUM errors. This happens because we only have 50 countries to choose from. Indices less than one or greater than fifty are outside the bounds of our set. So right-click the scrollbar, select Format Control. Select the Control tab.

Right away, we know that the Minimum value should be 1. But if we have 50 countries in our set, what do you think the maximum value should be? Hint: it’s not 50. Remember, we’re only changing the value in cell F32, the rest of the values are calculated for us on the spreadsheet. So the maximum value should actually be 36. Why? Because while we have 15 different indices showing, one of them is the cell link and the other 14 are calculated on the spreadsheet. 50 – 14 = 36.

In Sum: 
Minimum Value: 1
Maximum Value: 36 
Click OK.

If you play with the scrollbar, you’ll see that it keeps the indices within the correct bounds.

Step 6: Use VLookup to take largest values to its corresponding information.

In the first cell to the right of the Largest Values column (cell H32), we’ll use the Weight Value to the left as a lookup value. Now, do you see why I moved the final weighted scores all the way to the left on the ata tab? It’s a lookup column now! So, in Cell H32, you’ll have something like the formula shown below. But rather than typing what I have, try to recreate the formula yourself. Then double-check to ensure that our work agrees. 
=VLOOKUP(G32,Data!$G$4:$R$53,2,FALSE)
(1) G32 is the weighted score;
(2) Data!$G$4:$R$53 is the entire table from the Data tab;
(3) 2 tells us to pull from the second column, that’s the name of the country we’re interested in; and,
(4) FALSE ensures an exact match.
But wait, the VLookup Table on the example tab has a column for each metric, and we've only filled in info for one column. We’re not just interested in the second column from the table on the data tab. We’re actually interested in columns 2, 4, 6, 8, 10, and 12 as demarcated in red below.



We can actually pull all that info out with only one VLookup. On your data tab, select H32. Now, rewrite the VLookup formula as follows:

=VLOOKUP(G32,Data!$G$4:$R$53,{2,4,6,8,10,12},FALSE) 

Note we have changed the formula from looking up only column 2 to looking up a set of columns. Ensure that you use the curly braces to surround your set of columns as shown above. Now drag the selected cell to the right until you reach column M, the end of the Vlookup table. With H32:M32 still selected, click into the formula bar, then press Ctrl+Shift+Enter. Viola! We use the Ctrl+Shift+Enter here because the VLookup is returning an array of numbers and not just one number. Now drag the selected row all the way down to fill the table. If you’ve done everything correct so far, your table should look like mine.


Use the scrollbar to see the values change.

Step 7: Map the values from the intermediate table to the dashboard.

First, select the scrollbar to the left of your VLookup Table. Press Ctrl+C to copy. Scroll all the way up to the dashboard. Now paste. Move this new scrollbar to a location on your screen that makes sense. You could put it between columns E and G. Or, you can put on the right, as I have, to the right of column O.


Looking at your dashboard, select cell G6. Set G6 to reference the nation at the top of the list in the VLookup table. So, G6 should have “=H32” as its formula. Now drag G6 down 14 rows. If you see one of your rows start showing a value of “0,” you’ve dragged too far. Now, while looking at your dashboard, select cell O6. Map this cell reference the first weighted score on your VLookup table. O6, then, should have the value “=G32.” Drag down. The preset bar charts should show up automatically.

Now play with every scrollbar on the dashboard.

***

This is a good stopping point for Part 2. I might be working on a Part 3 as time allows. If I end up writing a Part 3, we’ll talk about how to make those bar charts. And, we’ll talk about how to build another graph that shows how each country performed in just one metric. Lastly, we’ll talk about how to populate the top and bottom rankings.

If you're really curious about the incell bar charts, you can see a good discussion on Chandoo's site,
How to Visualize Survey Results using Incell Panel Charts.

Questions? Feel free to ask.

Easy One-Way Sensitivity Analysis on Weighted Sum Models in Excel (Part 1)

3
This article describes what I like to call "Easy One Way Sensitivity Analysis" in Excel.  I've used it before in several dashboards.  If you want to try it out for yourself before reading ahead, both Healthcare dashboards on the Fun Downloads page implement my Easy One Way Sensitivity Analysis method.  Here's a pic of one of those dashboards:


Above, you can use the scroll bars to change the weights for each given metric.  As you change one weight, the others change proportionally, just as you would expect with one-way sensitivity analysis. In Part 1, I discuss how to build the structure that drives this functionality.  In Part 2, I'll discuss how to put it on your dashboard.

Below I begin with a review of sensitivity analysis and weighted-sum models.  If you'd like, you can skip the review and go right to the instructions, here.

Review
The title of this article may seem like a mouthful, but don’t let the technical words fool you.  It’s likely you’ve seen this stuff before.

Imagine, for a moment, you are evaluating the healthcare systems of different nations.  After considerable research and many hours with healthcare experts, you decide there are five key criteria you will use to evaluate each nation’s system.  They are:

Health level - a measure on the overall health of a country
Health distribution - a measure of how equally healthcare resources (above) are distributed across the country
Responsiveness - a measure of the speed of health services, doctor’s choice, amenities, etc.
Responsiveness distribution - a measure of how equally healthcare responsiveness (above) is allocated across the country
Financial fairness - a measure of how citizens in each country “fairly” distribute the financial burden of the healthcare system

Not every metric is equal, however.  And, after some careful thought, you decide that each criterion should be weighted by a proportion of its importance to the overall performance of each system.  The weights are as follows:


This model may not look familiar, but, in fact, it’s the very same criteria and weights used in the landmark World Health Organization study, World Health Report 2000: Health Systems, Improving Performance, which assigned a rank to 191 different countries based on the performance of their healthcare systems.   Indeed, this model is used in many published rankings. It works like this: you define the key metrics about the subject you want to investigate and assign each a weight.  You then collect data about each metric resulting in an overall score for that metric. For example, you could collect surveys from healthcare professionals ranking their healthcare networks on a scale from 1 to 10 and then average the results (I'm simplifying a bit). Finally, you would multiply each metric by its associated weight then sum those products together to come up with an overall score. If you understand me so far, then you get the weighted-sum model.

In the published rankings above, the weighted sum model is used to evaluate many different countries. Broadly, you’re simply investigating a resultant list of alternatives whose scores follow directly from the importance of each input defined in the first step. As such, you may want to investigate how changing the importance of inputs impacts the overall score. This is called sensitivity analysis.

One-Way Sensitivity Analysis

One simple, if powerful, sensitivity analysis method is to vary only one weight at a time while maintaining the proportional importance of the other weights. This is called one-way sensitivity analysis and it works like this:

Say I want to test what happens if I increase Health Level by 4%.  First, lets divide the weights into two theoretical groups,


The rule here is that each group must always sum to 100%. So, if we add 4% to Health Level, we have to subtract it from the other group.


Now that the overall sum of the "other group" has changed, the weights that make up that group are adjusted while maintaining the same proportion to the group's sum as they did before.  First we find the proportion...
Then multiply by the new group weight...


Finally,
...our weights again add up to 100.0%

Easy One-Way Sensitivity Analysis in Excel
The above method may appear daunting, but we can make Excel do most of the work for us. I call my "easy" because it's both straight-forward and requires no VBA to implement.  However, it has some limitations that will become clear by the end.


Step 1 - Set up your calculations table

This is how I set up my calculations tab tab:


Note that I have my weights listed on the left side.  I'm given some space between my weights and my score table.  Note, too, that I have all the multiplication set up.  You'll likely want a similar layout. Of course, to save some time, you can download the workbook above with the layout already set up (use the Example tab).

Step 2 - Insert Form Control Scroll Bars, One for Each Weight 

Go to the Developer tab.  Select Insert > Form Control Scroll bar.  (If you don't see the Developer tab on your Excel screen, you'll need to enable it.)


Insert five different scroll bars below the weights table.  Or, better yet, insert one, then copy and paste.

Step 3 - Link each Scroll Bar 

Insert a new column between columns B and C.  Put a new heading in the new column titled "Linked Weight."  We want to link each scroll bar to a metric so that we can use the scroll bars to change the weights.  To link a scroll bar to a value on a sheet, right click on the left-most scroll bar and select Format Control.  In the Cell link field, select the cell that will hold the current value of the scroll bar. You will also need to set Minimum Value to 1.


From the left-most scroll bar to the right, you'll want to link them to cells starting C3 all the way down to C7. Once the cells have been linked, go ahead and click the down arrows on each scroll bar. You should see the values change as you scroll down. This will also allow you to double-check that each scroll bar is associated with the correct cell.

Step 4 - Adjust Each Value

We have a small problem: the scroll bars increase their cell's values when scrolling down. While there's nothing wrong with that per se, it's counter intuitive to how we think. For our purposes, we'd like the action of scrolling up to actually increase the resulting value and scrolling down to decrease. So we need to adjust the values on the spreadsheet to reflect this preference.
Insert another column between C and D.  Name it "Adjusted Weights."  In each cell next to the Linked Weights, you will take the cell to its left and subtract 100 (the max value of the scroll bar).  You can do this with a formula like the one shown below.


Play around with the scroll bars. Note that the adjusted weights now increase and decrease intuitively.

Step 5 - Sum and Define Proportions

At the bottom of the Adjusted Weight column, add a SUM cell.

Now you will overwrite the values in your Final Weights column.  For each cell in Final Weight, you will divide the cell to its left (in the Adjusted Weight column) by the total sum you've just calculated.  Use the formula below as a guide.


At this point you're finished with Part 1. If you play around with the scroll bars, you will see that you can change the weight of one criteria at a time, while maintaining the proportionality of the others. The final scores update automatically! The scroll bars also adjust the location of the selector (that little gray bar) accordingly.

Limitations

As you may have already noticed, the scroll bars appear to have a limit. (Scroll one of them to its maximum value to see what I mean.) For some applications, this limitation isn't a real problem; but you may create a model where the "easy" method is not useful. Additionally, because we use formulas to derive the final weights, you can't type a desired weight into the Final Weights column without breaking part of the scroll bar mechanism. (But you can fix this problem with some VBA.)
In Part 2, we'll talk about putting this structure into a dashboard layout.

Download the file
One Way Sensitivity Analysis.xlsx

Turn an Excel Spreadsheet into a Bitmap Image

3
So I've created a spreadsheet program that will import a selected bitmap and use Excel as a canvas to display it.  Each cell is a pixel.  Here's a picture of president Obama in 8-bit glory:


And here's my current March Madness bracket in stunning 24-bit:

You can download the file below, and instructions are included on the Instructions tab.  Don't worry, it's not hard to use.  If you can use MSPAINT, you'll be making your very own Excel art in no time!

For now, the code is locked, but I might make it available later after I'm sure it's error free. Speaking of which, there are a few problems you may (but hopefully won't) run into:

1.  The picture shows up discolored and slanted.  While I'm fairly certain that I've fixed this issue, I admit that I am by no means perfect.  When bitmaps are saved, they are "padded" in the file to make their height evenly divisible by four.  The program should compensate for this padding, but if it doesn't -- again, I'm fairly certain this issue is resolved -- just resize the bitmap it to the nearest number evenly divisible by four.  (That number is at most three digits away.)

2.  Too many Different Cell Formats error.  Sometimes you'll get this annoying popup.  If that happens, try zooming in on the canvas screen first and then reuploading your file.  If that doesn't work, either crop the original image or resize to a smaller one.  Or, if you are uploading a 24-bit bitmap, consider resaving as an 8-bit and then reuploading.

If you find other issues, please, let me know.  It would be great help.

Also, this is best used with Excel 2007 and above.  It will work in 2003, but remember that 2003 has a column size limitation, which may cause in an error for files that are too large.  If you are concerned, smaller is better: resize or choose to save in the 8-bit format.  When you are done, remember to save as in the Excel 2007 file type (check to see if you are in compatibility mode) so that you get all the columns required to display your most productive spreadsheet, ever.

I'm releasing this now because I've been sitting on it for way, way too long.  I was hoping I'd come up with some real, professional use for it, but I haven't thought of anything yet.  For a moment, I was trying to apply a Hough Transform to the bitmaps to see if I could take images and turn them into cool free form shape, but this proved too taxing for Excel.  I also made an attempt at Eigenfaces which ended ridiculously.  So if anyone out there has some good ideas, please email me and we'll work together.

Lastly, if you like what you see and choose to host a spreadsheet made with this program (or screen capture of one) on your blog or website, I'd be so grateful if you mentioned this blog!  Have fun.

File
Bitmap to Excel.xlsm