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!
Before we jump into solving this ask a few questions to determine the exact requirements!
#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!
#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
Download My Excel File
Watch My Video
I review the solutions above YouTube video.
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.