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

Author's headshot

Val Karkauskas

Val Karkauskas has written this article. More details coming soon.

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:

Stay in touch

Bringing news to you

Subscribe to our newsletter

By clicking and subscribing, you agree to our Terms of Service and Privacy Policy

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

Related Content

thumbnail image

Working with an Automation Mindset

5 MINUTES READ | August 22, 2019

thumbnail image

3 Tips for Showing Value in the Tech You Build

5 MINUTES READ | April 24, 2019

thumbnail image

A Beginner’s Experience with Terraform

4 MINUTES READ | December 20, 2018

thumbnail image

Tips for Holiday Reporting Preparedness

3 MINUTES READ | November 5, 2018

thumbnail image

Navigating the Amazon Ecosystem

2 MINUTES READ | September 10, 2018

thumbnail image

Our Approach to Marketing Automation

7 MINUTES READ | November 16, 2017

thumbnail image

ICYMI: The Next Big Title in Media Agencies

1 MINUTE READ | July 11, 2017

thumbnail image

A Simple Look At Natural Language Processing

2 MINUTES READ | November 9, 2016