How to Save Time and Reduce Errors with Auto-Complete

Overview

You’re probably familiar with the concept of auto-complete.  After all, it’s been around for nearly 19 years, the invention largely attributed to Google with the launch of Google Suggest.  You probably use auto-complete within several apps every day in various forms, without even realizing it.  It’s seamless and hugely helpful…when it’s not an epic fail of an auto-correct!

In many apps, you can build in auto-complete search suggestions (even setting up your own searchable list), and Excel is no exception.  In this article, we’ll look at three Excel features that help you take this approach to save a lot of time and reduce the risk of errors.

Data Validation Lists

If you capture any inputs in your Excel workbooks and don’t use data validation, you’re missing out on a great feature and adding unnecessary risk to your process!  Excel offers many data validation settings, such as number/date ranges, text length, formula-based validation, and pre-defined lists.  For the sake of this article, we’re going to focus on pre-defined lists.  Below are three reasons worth considering using them.

They restrict user inputs to valid values

You’ve likely used hundreds or thousands of drop-down lists on the web and in apps, and a data validation list is the easiest way to offer the same functionality in Excel.  The list can be set in two ways:

• A manually-typed list (i.e. “Yes,No”) – best for few values very unlikely to change
• Linked to a range of valid values – best for a larger list that is likely to expand or change over time

This can greatly reduce errors and misspellings, and guide users as well.  Key tip: use the keyboard shortcut Alt+⬇️ to show the drop-down list.

They can save quite a bit of time

The more inputs your workbook captures, the more time you’ll save offering data validation where appropriate – both the time entering valid values, and the time saved from searching for and correcting errors.  At the time of this writing, Microsoft recently enhanced Excel such that suggestions are offered as you type when the cell has a data validation list applied – a minor but super-helpful feature!

They can be dynamic

When linked to a dynamic list (via a named range), data validation lists can change based on the list contained in the named range.  I use a named range with a formula like the one below to make it dynamic (based on the last used row).  That list is generally held in a lookups sheet out of the way.  See the screenshot below of a named range linked to a data validation list.

=OFFSET(Lookup!$A$2,0,0,MAX(COUNTA(Lookup!$A$2:$A$511),1),1)

Helpful macros

To easily apply dynamic data validation lists, there are two macros available in the XLEV8 Excel add-in that I use to apply it within a few seconds.  Here are the steps to use them:

1. Enter the initial list of valid values.  I like to start in row 2 of a lookups sheet for this.
2. Determine a range name for the values (i.e. Color_List).  Enter the range name above the list as a header and copy it to the clipboard.
3. Select the top value in the list.  Run the Add Dynamic Named Range macro.  The range name should be defaulted at the prompt, so click OK to proceed.
4. Select the cells where you want to restrict values to this list and run the Add Data Validation From Named Range macro.  The range name should still be defaulted at the prompt, so click OK to proceed.

Viola!  In just a few seconds, you’ve created a dynamic named range and applied it to a data validation list!

AutoCorrect

Ever since touchscreen keyboards became prominent (mostly thanks to the original iPhone back in 2007), auto-correct has helped our hurried, misspelled words get fixed…except when it hilariously guesses wrong.  You can use this feature within Excel too!  Below are three reasons worth using it.

It saves you time and reduces errors

Unlike data validation lists above, AutoCorrect is always on, and it works within the editor, not just whole cell values.  By correcting values for you, it’s saving you time and increasing your accuracy.  If you don’t like a correction, just undo it.

You can build your own list

While AutoCorrect comes with about 1,000 built-in corrections, you can change, delete, or add to these if you’d like.  To do so, go to File >> Options >> Proofing >> AutoCorrect Options >> Enter the “Replace:” value and “With:” value, then press the Add button (see the quick video below).  Repeat for as many corrections as you’d like.  Note that it orders them alphabetically.  Key tip: use this for shortcuts, such as person/company initials, acronyms, etc.

It works with other apps too

The best part about using AutoCorrect is that it doesn’t just work with Excel – the AutoCorrect list on your computer works across most MS Office apps!  There is often a slight delay in syncing the list, so if you’re not seeing some changes reflected, wait a little bit and they’ll show up.

Helpful macros

Editing the AutoCorrect list can be a bit of a chore.  I use a macro in the XLEV8 Excel add-in called Bulk Edit Autocorrect.  It does just what it says – it allows you to make multiple changes to the list in one bulk step.  Just run the Bulk Edit Autocorrect macro, and it exports the existing AutoCorrect list.  Edit, delete, or add items, then run the Bulk Edit Autocorrect macro again to save them!  Save a copy of the file with the updated list and share with friends or use when you need to switch to a new computer.

Custom Lists

Custom lists are another great way to enter data in an auto-complete-like way. Rather than just one value though, you can enter many of them in one step. If you repetitively enter the same list of names, locations, labels, etc., this can be quite helpful. Below are three reasons worth using custom lists.

They save time and reduce errors

Seeing a theme here?  Most helpful software features should save time and reduce errors!  In this case, you’re saving the time of entering all the values of a list repetitively.  You’re reducing errors not just by entering the right values, but all of them, as a completeness check.

They work with auto-fill

After entering a couple of the list values, just drag the fill handle to fill in as many items from the list as you want (it doesn’t have to be all of them).

Fill in your own lists

Excel has four default custom lists (days and months) that cannot be edited or deleted.  Aside from those, you can add, edit, and delete as many custom lists as you want.  Custom lists can have up to 254 list items.  To add/edit them, they can be manually entered, or you can import them from a range of existing values.  To do so, go to File >> Options >> Advanced >> Edit Custom Lists (at the bottom bottom) >> Add to a new list or import from a range (see the quick video below).

Helpful macros

There are three macros I use in the XLEV8 Excel add-in that help with custom lists:
 

1. Import selected cells to a new custom list using the Set Custom Fill List macro.
2. Edit the custom lists in one bulk step using the Bulk Edit Custom Lists macro.
3. Use the Search Custom List macro to search for a custom list in an auto-complete search box and enter list items with a matching result.

Summary

Auto-complete has undoubtedly changed our lives for the better by saving time, reducing errors, and making apps less frustrating to use.  Using data validation, AutoCorrect, and custom lists is a great way to leverage the auto-complete approach.  The XLEV8 Excel add-in macros mentioned above make it super-easy to apply and use these helpful features.  

Which of these features do you use in your daily routine?  Which ones are you going to use even more?  Please share your thoughts in the comments below!