Is your data validation list is so long that the endless scrolling makes you want to close Excel and never use it again? This post will review several solutions to this problem.

 

What’s the Big Deal?

Why can’t we simply use Data Validation? The list has 2551 items! Scrolling back and forth is PAINFUL !

 

 

What Can We Do?

Let’s create a shorter list based on a keyword!

 

 

Ask Questions!

Before we jump into solving this ask a few questions to determine the exact requirements!

 

 

Solutions

#1 PIVOT TABLE

A few easy steps to create this solution. But…it takes longer to change the keyword.

  • super quick to create
  • dynamic named range could be eliminated if required
  • built in options include: wildcards, ‘starts with…’, ‘ends with…”, etc.

 

#2 ‘List Search’ VBA Add-in

Free vba add-in from Jon Acampora! But…challenging to modify / troubleshoot for non vba users.

  • ready to use solution!
  • free!

 

#3 Array Formula

Allows multiple keywords! But…arrays can be very tricky for most Excel users.

  • less steps than non array formula solution
  • multiple keywords are allowed

 

#4 Simple Formulas

Only simple formulas used! But…only one search word allowed. More formulas required.

  • much easier to audit this solution

 

#5 Advanced Filter

Advanced filter! But…not fully dynamic (you’d have to re-run this a lot).

  • quick to create and update
  • no advanced vba or formula knowledge required

Check out Debra’s video and post!

 

 

Download My Excel File

Download it here or from my OneDrive (00158 Keyword Data Validation List.xlsx)

 

 

Watch My Video

I review the solutions above YouTube video.

 

 

About Me

My name is Kevin Lehrbass. I live in Markham, Ontario, Canada.

About 20 years ago I remember seeing a friend working on an Excel spreadsheet. It looked interesting. I thought to myself “What if I could learn more about Microsoft Excel than I know about Chess & Spanish?”

At the time it seemed like a distant possibility but I started learning and I’ve never looked back 🙂

I’m a Data Analyst at McKinsey & Company.

Check out my YouTube videos and my blog posts.

Post a comment