This website uses cookies to ensure you get the best possible experience. See our Cookies Policy.

PMG Digital Made for Humans

Handy Tool for Dynamic Data Consolidation in Excel

2 MINUTE READ | December 9, 2014

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:

Insights meet inbox

Sign up for weekly articles & resources.

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

Posted by Val Karkauskas

Related Content

Get Inspired

Working with an Automation Mindset

5 MINUTES READ | August 22, 2019

Get Informed

Parsing Redshift Logs to Understand Data Usage

7 MINUTES READ | May 6, 2019

thumbnail image

Get Inspired

3 Tips for Showing Value in the Tech You Build

5 MINUTES READ | April 24, 2019

Get Informed

A Beginner’s Experience with Terraform

4 MINUTES READ | December 20, 2018

Get Informed

Tips for Holiday Reporting Preparedness

3 MINUTES READ | November 5, 2018

thumbnail image

Get Insights

Navigating the Amazon Ecosystem

2 MINUTES READ | September 10, 2018

Get Insights

Our Approach to Marketing Automation

7 MINUTES READ | November 16, 2017

Get Informed

ICYMI: The Next Big Title in Media Agencies

1 MINUTE READ | July 11, 2017

Get Informed

A Simple Look At Natural Language Processing

2 MINUTES READ | November 9, 2016