Handy Tool for Dynamic Data Consolidation in Excel

Growing up as a young basketball player I wanted to rebound like Charles Barkley, pass the ball like Magic Johnson, and score like Michael Jordan. I also dreamed about combining my favorite attributes from various women into the one perfect girlfriend. Having a perfect all-in-one scenario seems completely unattainable; luckily we have more options in the world of analytics.   Excel can provide the ultimate dream in consolidating and presenting data when you know how to use it.

Less seems to be more when it comes to data utilization and comprehension. Research shows that customers start losing interest whenever our spreadsheets contain more than 3 tabs of information. In fact, reports with one tab have about a 70% higher chance to be read and analyzed than reports containing 4 tabs or more. Somehow we need to trick our readers’ minds that they are consuming a reasonable amount of information in our spreadsheets or dashboards within compacted viewing areas.

This brings me to the topic at hand. Drum roll please…. Inserting a List Box using Form Controls is my favorite tool to compact and organize data in Excel.  It is simply a visual filter that allows you to create dynamic reports that can merge multiple tabs into one easy-to-read dashboard.

One recent research study conducted at a major US corporation confirmed that form controls and drop downs in Excel were preferred options to view consolidated data. More specifically, readers favored the look of a List Box. In fact, users increased their engagement of the material by 45% when selecting choices within a List Box versus using a drop down. The image below shows the comparison of these two options.

It seems that our brains prefer to see options presented visually instead of taking an extra step of opening a drop down that may contain some scary hidden cobwebs.

Where do we find this amazing option in Excel? Just add a Developer tab if don’t have it already (here is a link on how to do it) and under Control section select an Insert button which will allow to pick a desired form control option.

Here are a couple of links with more detailed information on how to setup form controls:

https://www.youtube.com/watch?v=5MZLq7poTzI

http://support.microsoft.com/KB/291073

And regarding my two unattainable dreams as a young man, one of them did come true, so dream big my friends…