Directly link Excel form controls to backend data with dynamic references

Form controls are great for reporting information about groups of items, like a list of programs or accounts. They are often used on Excel dashboards and reports that demand interactive capabilities. One such type of capability provides the user with a list of items to choose from. When the user makes a selection, a macro is executed that populates a table holding referenced values. Those values are linked to a series of form controls on the frontend. This interactivity is displayed below:

Step 1

Step 2

Step 3

There is a final step, which I haven't included. The user would make changes to the project under the Options table. They would press a "Save" button and their changes would be copied from the Linked Values table back onto the backend data in the column corresponding to the selected project using VBA.

The No-VBA way

There's nothing wrong with this method in and of itself, but I want to propose a method that requires no VBA. The advantage of this new method is that it links directly to the data itself and bypasses the need for the Linked Values table. We can do this by allowing the form controls to take advantage of dynamic references.

Typically, form controls can only do direct, absolute references. You cannot, for example, use VLOOKUP or INDEX within the source field of a form control. However, you can use a named ranges.

Let's do it!

First, we give that 'index' field above a named. How about selection? Next, we create four named ranges to correspond to the form control checkboxes. Stage_1 to Stage_4 are those new named ranges.

As you can see from the picture, I use the fourth row to connect to checkbox Stage 4 and the selection value to inform Excel to pull from the fourth column in the backend data (which is Project 4, if you recall).

Finally, I can simply link these named ranges to their associated checkboxes:

Using this method, changes to the checkbox automatically change the backend data. There is no intermediate table required -- like the Linked Values table above -- to interface between the frontend and the backend.

That's all for now - have a happy and health holiday season!

Update 25 December:
Make sure to see the download file - Direct Links.xlsm.

CUBE: The Rollover Method Returns! Spin a cube in a 3d environment with your mouse!

Ok, so I know I promised no more updates, but this one is too cool. Basically, I combined Andy Pope's 3d rotation tutorial with the rollover method. The result is a free-floating cube which you can rotate by passing your mouse over it. Here's a choppy-ish video of what's going on. I promise however that it run smoothly on your computer. The choppiness is a result of the video's frame rate.

The mechanism is similar the the election map and the details-on-demand graphs: the chart is placed on top of the hot spots to get the mouse's coordinates. In this case, the rollover pulls in the mouse's location and initiates a timer. The timer adjusts the rotation angles based on the mouse's location. Additionally, some fancy calculations in the timer process procedure give the rotation a "slowing down" effect. I urge you to open on the Visual Basic Editor and do some dissection, if you desire. Remember though, the Timer API is a bit finicky. Make sure to save often in case your Excel crashes while in the middle of your investigations.

The download file: Rotate Cube Rollover.xlsm

Have fun.

Taking a small break

These last few months have been great to this humble blog - and to me, your humble blogger. We've received a lot of traffic, especially for posts concerning the rollover method (40k pageviews overall as of today!). Also, I got married in October!

In pains me to tell you this, but I'm taking a break until next year (you probably already noticed my absence). In August, I signed on with Apress publishing to write a book about developing Excel applications and dashboards. I'll be honest with you - I'm woefully behind schedule, and I really need to catch up. Writing this book has been a dream of mine. So I really need to focus on it.

In my last post, I said a tutorial on how I created the Election map would be forthcoming. At this rate, I'm not sure when that will be. Thankfully, Robert Mundigl of ClearlyAndSimply.com has written about the very same technique the Election map uses in his phenomenal article, Roll Over Tooltips and Web Actions on a Microsoft Excel Dashboard. I can't really thank Robert enough for this. His article is a pleasure to read (as are his other articles) with explanations that are thorough and thoughtful; certainly, they surpass anything I've ever written on the subject. So, if you haven't read Roll Over Tooltips and Web Actions on a Microsoft Excel Dashboard, please make your way there as soon as possible.

Another person I need to mention is Bert van Zandbergen who has been my silent partner-in-crime concerning investigations into the rollover method. He has toyed and tampered with the rollover method producing spreadsheets that are different but always interesting. I mean, just take a look at these:

You can contact Bert at: klvzndbrgn (at) gmail.

Finally, the people I really need to thank are you, my readers. This blog wouldn't be what it is without you.

I won't be gone forever. My facebook and twitter will be alive again soon. Make sure to check them out.

Oh yeah, and a happy holidays to everyone :)

Interactive United States 2012 Presidential Scoreboard in Excel

Alright, here's another map-based rollover. The instructions are pretty simple: click on a state to toggle through its party selection. Click on one of the three selections above to change scenarios.

In the next month or so, I'll be putting together a tutorial on this. But in the meantime, have some fun. Download. Ask questions if you need.

For more on these types of maps, checkout:
Choropleth Maps with Excel

Download here:
Election Map.xlsm

How about another screenshot?

Recommendation: Excel School + Excel Hero Academy Bundle

Chandoo (of Chandoo.org) and Daniel Ferry (of ExcelHero.com) are kicking-off their new Excel School and Excel Hero Academy. If you really want to learn Excel—and I mean learn Excel—I recommend their new courses.

It’s true; I’ve never taken a course from either of them. However, much of what you see on this website is a direct result of their work, their tutorials, their fearless trailblazing into Excel’s unknown. All of my work (not just the stuff on this blog, but my professional work too) owes a great debt to both Chandoo.org and ExcelHero.com. I can only imagine the great work they do online is expanded in their classes. In all honesty, I would sign up too if I wasn’t so pressed for time in the short term. But if you have an interest in learning Excel and taking it to the next level, I can’t think of two better people to teach you how. Believe me.

I’m not a paid spokesperson. I won’t receive anything for referring you. In fact, unless Chandoo or Daniel views this blog article (here’s hoping I’m important enough for them to follow!), they won’t even know I’ve made a recommendation. But I believe that strongly in their work.

So sign up for the Excel School & Excel Hero Academy Bundle. I think it’s well worth the money.

Handling Rollover Clicks Without Using the Worksheet_SelectionChange Event

You probably noticed a "bug" while playing around with my Interactive Periodic Table of Elements in Excel. When you click down into the Table of Elements (or really, anywhere on the sheet), one of the chemical classifications will become "selected," despite your not having clicked on it directly. This happens because I've used the ScrollArea property to set the clickable bounds of the spreadsheet to just the range containing the chemical classifications. When you click on one of those classifications, Excel handles the "click" through the Worksheet_SelectionChange event. So even if you don't click into any part of the clickable area defined by the Worksheet's ScrollArea property, Excel can still fire the Worksheet_SelectionChange event.

And then there's this other bug, too: If you try to click on one of those classifications more than once (without clicking onto something else first), you'll notice that nothing happens. This is because your "selection" hasn't changed (that black selector is still in the same spot).

Such are the problems when using the Worksheet_SelectionChange to handle rollover interactions. So the fix? Don't use the Worksheet_SelectionChange.

As Chandoo demonstrates in his One race, Every medalist ever – Interactive Excel Visualization article, the rollover UDF can take ranges as input parameters. Take a look at this sample UDF below:


Public Function RolloverSelection(Index As Integer, curRange As Range)

'....

  

End Function

Notice that the second parameter will take a range as its argument. That means on my Excel spreadsheet, in cell D5, I could write something like this cell for my rollover formula:

=IFERROR(HYPERLINK(RolloverSelection(2, D5),4),4)

See that highlighted portion? I'm just passing in a reference to the cell that holds the formula.

Ok, so where am I going with all of this? Well, remember that whenever we click on a "clickable" cell (that is, a cell allowed within the desired ScrollArea), the black selector changes. But how then do we find if the the selector has changed its location? We use the Selection object, of course! Thus, to test if the user has clicked onto one of our rollovers, we simply test if the address of the curRange (that's D5 above) is the same as the address of the selector. In other words:


Public Function RolloverSelection(Index As Integer, curRange As Range)



      'Do other stuff here

    

      If curRange.Address = Selection.Address Then



              'Handle clicks inside of here



      End If



End Function

Using the above function, we are able to fix both problems described that stem from using the Worksheet_SelectionChange event. In addition, we provide an even more robust mechanism for handling user clicks.

***

So here's the funny story surrounding this post. I was updating a dashboard I had worked on previously this year. As I was trying to become once again acclimated to my previous work, I couldn't understand how my code was handling user clicks. There was no code in my Worksheet_SelectionChange. So after some investigation, I saw the code above and it all came back to me. But holy crap, I must have been on something. I didn't even remember to use it for the Periodic Table of Elements. Sheesh.

Investigations in "Illegal Activities" with User Defined Functions

A group of fellow Excel experts who call themselves the Frankens Team were encouraged by the rollover method to investigate other known instances in which Excel has allowed for the impossible; namely, allowing UDFs to change the value of other cells.

I invite everyone to view their terrific analysis: Excel formula to change the value of another cell?

Also, take a look around the site (which is mostly in Italian - so get that Google translator ready!) for some great Excel stuff, like Creative and advanced chart design in Excel.

By the way, what would you call this type of chart?

Courtesy E90E50 fx

The Excel Rollover Mini FAQ

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

Video Tutorial on Interactive Dashboard using Hyperlinks by Chandoo

Use a mouse hovering technique to create an interactive chart by Oscar of Get-Digital-Help.com

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

Downloadable Demos
Interactive United States 2012 Presidential Scoreboard in Excel by Me

One race, Every medalist ever – Interactive Excel Visualization by Chandoo

Interactive Map in Excel using Rollovers by Me

Guest Post: Rollover for Months and Years by Bert van Zandbergen (klvzndbrgn (at) gmail )

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

Interactive Periodic Table of Elements in Excel by Me

Rollovers for Gantt Charts by Me

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

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

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

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:

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

About Me

A big thank you to everyone! This blog has achieved over 20,000 page views, which is quite an accomplishment for this humble blogger. Looking through my work, I realize that I've never formally introduced myself.

So, here goes.

My name is Jordan Goldmeier and I live in Dayton, Ohio. I work for a small start-up consulting company, The Perduco Group, developing analytical applications many of them in Microsoft Excel. Later this year, I am getting married to wonderful, incredibly smart theology student named Katherine. I'm also currently writing a book on Excel dashboards for Apress publishing.

Also, I have two cats:

Rufus

and

Theo

And then there's Katherine's dog, Katie:

***

As I went through the information Google Analytics collects about my blog, I saw people from all over the world have visited my blog. Wherever you're from - don't be a stranger - introduce yourself! And if you're in the Dayton area and haven't said "hi" yet - here's your chance!

jpo645 (at) gmail (.com)

Look forward to hearing from you. Comment, email - let's connect.

-Jordan

Guest Post: Rollover for Months and Years

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!

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.

Consulting Services

You may have noticed that I tend to update this blog less frequently than some of the more prominent Excel and VBA bloggers. The reason is that I blog on the side rather than full time. In fact, I work for a wonderful startup consulting company called The Perduco Group. Here’s what we’re about:

Perduco is Latin for "to lead through" or "leadership.” The Perduco Group leverages a number of key competencies in data structures, programming, operations research, and business intelligence to provide an overall analytical solution to the customer − delivering performance from data to decision. The fundamental goal is to provide a useable product founded in credible analytics to support organizations and change the way by which they do business. The Perduco Group is focused on providing organizations value by leveraging our technical skills and industrial and defense experience with our client-focused, results-oriented delivery approach.

If you’re interested in taking your spreadsheets to the next level, optimizing your efficiency with operations research, or implementing a new BI system drop me line at my work email, Jordan.Goldmeier (@) ThePerducoGroup (.com) and I can let you know what we’re all about.

As a follow-up to the above, I am gathering resumes on folks with experience in Excel, VBA, data analysis, and data visualization. If you’re interested in this type of work, feel free to send me your resume either at the email above or my personal one at the bottom of this page. The only condition is that you must be a US citizen.

Turn Off Cell Background Error Checking with VBA

When I'm all done with my Excel application, I'll usually want to flip off background error checking - that is, I'll tell Excel to stop showing those little green triangles that appear in cells. Don't get me wrong, those little green alerts can be useful - but they are rarely so in a finished product where I know my layout and formulas are correct. I just want to tell Excel, "thanks for the help, but stop annoying me already!" Indeed, these green alerts appeared on other computer screens when users opened my Periodic Table of elements file, which I found annoying.

The problem is that while I can tell those green triangles to go away on my instance Excel by going into Excel Options (or simply by clicking "ignore"), that won't fix the problem when my file is loaded onto other computers. The way around this is some VBA and the workbook open and close events.

So, in my ThisWorkbook object in the VBA window, I wrote this:

Option Explicit

Private Sub Workbook_Open()

Application.ErrorCheckingOptions.BackgroundChecking = False

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.ErrorCheckingOptions.BackgroundChecking = True

End Sub

There are actually several types of background error checking that go on in a workbook. Excel allows you to disable these separate error checks by themselves if don't want to disable everything. Specifically, you can modify background checking options for empty cell references, error calculations, inconsistent formulas, and omitted cells, among others. For example, you might simply write:

Application.ErrorCheckingOptions.OmittedCells = False

if you only want Excel to stop monitoring for formula patterns that appear to omit cells that Excel thinks should be included in the formula. This will work so long as BackgroundChecking is still True. If you set BackgroundChecking to False like in the example above, Excel will cease all attempts to second guess your work (which can make your life easier, sometimes).

To read more, see:

The ErrorCheckingOptions Object

Newspapers - Read Your Sources!

Update - Apologies for the bad proofreading job in the original blog post. Before writing this, I had spent the entire day writing a book proposal for a technical publisher. I was a bit burned-out.

It's an exciting time for the United States. In the forthcoming days (possibly tomorrow), the Supreme Court of the United States will issue a ruling on whether all or part of the Affordable Care Act is constitutional. Surrounding the hype is an attempt by the media to figure out--that is, predict--the court's ruling before it's released this week. One factoid passed around is that a group of former Supreme Court law clerks and attorneys surveyed after arguments a few months ago suggest that the law will be overturned.

In an article by Business Insider, author Brett LoGiurato writes:

Most former Supreme Court attorneys and clerks believe that the individual mandate to buy health insurance — the signature provision of President Barack Obama's Affordable Care Act — will soon be struck down as unconstitutional by the high court.

That's according to a joint poll by the American Action Forum, Center Forward and Purple Strategies. An astounding 57 percent of clerks and attorneys polled now think that the mandate will be ruled unconstitutional. That's up from just 35 percent in March, before the court held oral arguments on the case.

Read more: http://www.businessinsider.com/obamacare-unconstitutional-supreme-court-barack-obama-health-care-2012-6#ixzz1yko2eVxa

The pic below appears in the actual Business Insider article and is a snapshot from the actual survey.

I'm not sure how Business Insider misunderstood the study, especially because a picture of it (the very same picture above) appears in their article. Clearly the survey does not say that 57 percent of clerks and attorneys think the mandate will be ruled unconstitutional. Rather, it says the average probability given by all the respondents is 57 percent. But can we say that "most" of the survey respondents believe that the mandate will likely be struck down? No, that's wrong, too. We really don't have enough information to draw that conclusion. More on that point in a minute.

This article, by the Washington Post, gets it wrong too:

A new poll of 56 former Supreme Court clerks finds that 57 percent think the individual mandate will be overturned. That’s a 22-point jump from the last time the same group of clerks was surveyed, right before oral arguments. Back then, 35 percent thought the court would toss out the required purchase of health insurance.

Ezra Klien (who loves charts) gets it wrong.

Here, the Wall Street Journal's Joseph Rago, too.

Did anyone get it right? Yes. Laura Green, of the Palm Beach Post, writes:

A poll of former Supreme Court clerks and lawyers who have argued before the justices found that many switched their initial prediction that the court would uphold the sweeping law. The oral argument performance led them to now speculate that there is a 57 percent chance the court will strike down the heart of the law, which requires virtually every American to buy insurance.

Read more: http://www.wptv.com/dpp/news/national/health-care-act-studies-of-oral-arguments-predict-court-will-rule-against-reform-this-week#ixzz1yksclk3m

What can we say about the survey's results?

Not a ton. I question the usefulness of engaging folks on the probability that a dependent, nonrandom event will occur. When the poll's respondents came up with their "probabilities," they were really giving a score to how confident they are in their belief that the Supreme Court will strike down the mandate. Calling this a "probability" is misleading.

The average is above 50%--can't we say that most believe the mandate will be struck down?

Not really. Consider this scenario. Of the 56 respondents, nine said the probability of the Supreme Court overturning the mandate was 100%; that's a rather slim, if certain, margin. Now consider that the rest -- 47 respondents -- giving the probability of the court overturning the mandate a 49%. In other words, the 47 feel the likelihood is slightly less certain than flipping a coin. Do the math; you'll see that it averages to about 57%. Do those nine respondents in this example constitute a "most believe"?

Survey Source:
Affordable Care Act Survey, June 2012
Sponsored by: American Action Forum, Center Forward, and Purple Strategies

Interactive Periodic Table of Elements in Excel

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.

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

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

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

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!

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

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.

Miscellaneous Stuff

First, a big thank you to everyone following my blog. Here's some stuff I'd like to share in no particular order.

Stephen Colbert Come to My Wedding

We (my fiance and I) want Stephen Colbert to come to our wedding, which is in October of this year. My beautiful and wonderful fiance explains as follows:

During the tedious processes of inviting people to our wedding, my fiance and I began a running joke of inviting Stephen Colbert. Well, he may still be joking, but I'm not.

We don't have terminal illnesses or a terribly romantic love story--unless you consider our burning desire to have Stephen Colbert at our wedding an illness and online dating terribly romantic.

Want to help? Of course you do! Do this:

(1) Like us on Facebook.
(2) Follow us on twitter
(3) Get your friends to do the same!

Thanks in advance for the help, internet - you're so awesome!

* * *

Excel Custom Formats

Lately, I've really been into Excel custom formatting. This is my favorite page to reference when I have questions about custom formatting: http://www.ozgrid.com/Excel/CustomFormats.htm

Make to checkout all the other info ozgrid has to offer!

Looking for a cool Excel forum?

I am a contributor to the Excel forum, Excel Heros on LinkedIn. This forum is associated with the terrific Excel Hero website.

Visualization and You

I have a lot of strong feelings about visualizations as you may have noticed in a previous blog post. I believe that many organizations, including the media, software companies, and even universities are encouraging meaningless and distorted data representations.

The visualization guru, Stephen Few, has this excellent quiz to test your Graph Design IQ. Check it out!

Right now, I'm reading Few's book, Now you see it, which I highly recommend. A link for it will be up later on an "excel resources" page when it's complete.

Want to hire an Excel consultant, but don't know where to begin?

This page from Daily Dose of Excel is a good place to start.

Let's join forces!

As you may have noticed, sometimes my blog updates are sparse. I do this in my spare time and sometimes I'm just too busy to update. Part of the problem is I start writing tutorials that always become too big. For example, I wanted my sensitivity analysis tutorial to be complete in one post -- already, it might grow to three or four! I have a real problem.

Do you like writing? More specifically, do you like writing on Excel? Want to join forces? I would really love to have some more regular posts. Drop me a line if you're interested!

Have just one post you think you might want to share? Again, drop me a line!

I'd love to hear from you

Comment below, email, facebook, twitter, LinkedIn... whatever you want. Have something interesting? Share it!

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

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

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)

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.

Everyone! We've moved to WordPress.